アカセニッキ(明瀬祐介日記)

少しだけ役に立つ、または面白い内容を提供できれば。

作戦

簡単な関数で不要部分や重複を削除しないで”詰める”方法。

投稿日:2018年10月26日 更新日:

■珍しく実用的な記事である。
Excelの関数について。

ひとつめは、ある表のうち、何かに該当するものだけを、
“詰めて”、別の表に表示させる方法。
ふたつめは、ある表のうち、重複を除いたものを
別の表に”詰めて”表示させる方法だ。

それぞれフィルタ機能や、[重複の削除]に近い。

IF、COUNTIF、VLOOKUPくらいで作ってみたい。

■もしかするとこれをご覧の中には、こう思われる方がいるかもしれない。
「それ、フィルタ機能や、[重複の削除]使えばいいじゃん」

が、実務では、次のようなパターンがあるのだ。

「同じ入力用シートをみんなに配り、
それにみんなに入力してもらい、提出してもらったあとで
さまざまな処理を行なう。
(同じ入力用シートを何度も使ってもらう)
その中の後処理の一つとして、”詰める”作業が必要になる
(“詰める”以外の作業もあり、そのままのデータを残す必要がある)」

このような場合、フィルタ機能や重複の削除を使うことにすると、
そのたびにマウスまたはキーボードでの操作が必要となり、面倒だ。
“何もしなくても”、”自動的に””詰めて”表示されてほしい。

■検索すると、いろいろな高度な方法が出てくる。
それぞれいい方法だと思うのだが、
私はIF、COUNTIF、VLOOKUPくらいしか使えない。
(また、Excelに詳しくない人にも修正してもらう必要がある場合がある)

(また、たとえばROW関数を用いると、別の場所ではそのまま使えず、
足し引きが必要になる、という弱点もある)

■ということで、下記のSheet1の表のうち、出身地が東京都の人だけを、
“関数だけで”、Sheet2のように表示されるようにしてみよう。
(多くの使用者には、Sheet1のA列とB列に入力してもらう、とする)

というわけで次のようにやってみた。

Sheet1のC列
該当のものだけに”〇”
IF($B2=”東京都”,”〇”,”×”)

Sheet1のD列
C列が〇の行だけに番号
IF(C2=”〇”,COUNTIF($C$2:$C2,”〇”),”-“)

Sheet1のE列
C列が〇の行だけ、名前を再表示
IF($C2=”〇”,$A2,”-“)

Sheet2のB列
Sheet1のD列に番号があれば、
そのひとつ右のSheet1のE列
IFERROR(VLOOKUP($A2,Sheet1!$D:$E,2,FALSE),”-“)

それほど難しい関数ではないと思う。

要点は
・”該当の物だけに”番号を表示させ、
・VLOOUKP関数で、その番号のものを順番に表示させていく
ということだ。

(*むろん、これだけなら、C列とD列を分ける必要はなく、
いきなり数を数えればよいのだが、
説明のためC列でいったん”〇””×”を表示させている)

■つづいて、[表1]のうち、
重複を関数だけで除き、”詰めて”、[表2]で表示させる方法。

最初の”該当だけ詰めて表示”が分かる方であれば、
もう簡単にできよう。

Sheet1のB列
“登場回数”をカウント
COUNTIF($A$2:$A2,$A2)

Sheet1のD列
初登場のものだけに〇
IF($B2=1,”〇”,”-“)

Sheet1のE列
D列が〇の行だけに番号
IF($C2=”〇”,COUNTIF($C$2:$C2,”〇”),”-“)

Sheet1のE列
C列が〇の行だけ、名前を再表示
IF($C2=”〇”,$A2,”-“)

Sheet2のB列
Sheet1のE列に番号があれば、そのひとつ右のSheet1のF列
IFERROR(VLOOKUP($A1,Sheet1!$D:$E,2,FALSE),”-“)

要点は上記と同じである。
・”初登場”のものだけに番号を表示させる
・VLOOKUP関数でその番号のものを順番に出していく

(*これも、作業列を設けず、もっと短くする方法はある。
が、どんどん作業列を入れていった方が分かりやすい)

明瀬祐介
acsusk@gmail.com

-作戦

執筆者:

関連記事

no image

関西の貧乏学生はフェリーで上海行っとけ。

■大学生活の六年目、 二〇一〇(平成二十二)年の夏、船でひとり、上海に行った。 神戸から出ている「新鑑真号」に乗り、三泊四日だったかで、帰りは大阪への「蘇州号」に乗ったのだ。(これだと往復割引がなくて …

no image

短期バイトが教えてくれたこと。

■前回、大学時代にやった、短期の「被験者」仕事について書いたけれど、やった仕事は「被験者」だけではない。 ■「時代祭」の祭礼行列で、何時代だったかの武士の服装をした。別の、もっと小規模なお祭りで勤皇の …

no image

ヤンキー理系なら白衣に熱い言葉を刺繍しろよ。

■自分にはヤンキー趣味への憧れがある、ということで思い出したのだが、大学の時、化学系の知人に記念品として贈るため「刺繍白衣」を作ったことがある。 確かこの刺繍屋だった。http://www.u-na. …

高認試験こそ攻守兼用の最強効率ライフプラン。

■以前にも書いたことがあるのだが、日本の十歳代の最高の過ごし方は「高校行かずに高認合格。そして好きなこと極める」だと思う。 勉強の知識自体は役に立つし、学歴だって社会で重要な意味を持つ。しかし高校に行 …

左利き活動はじめました

■生まれてこの方、 ずっと右利きだった私だが、 一か月ほど前、突如左利きになろうと思い立ち、 「左利き活動」を始めてみた。 ■実は、十年くらい前、 二十歳くらいの頃に、 腕や手や脚を組むときに、 それ …