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

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

作戦

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

投稿日: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

-作戦

執筆者:

関連記事

パワポは全部表で作れ。

■PowerPointでのプレゼンテーション作成は、 何となくストレスのかかる作業のひとつだ。 「ExcelやWordは苦にならない」 と言う人にも、 「どうにもPowerPointだけは疲れてしまう …

第二外国語初等クラスの春。

■大学生だったある春、「またも」留年が決まったぼくはいい加減、大学生である自分に嫌気がさしていた。 一念発起して勉強し直す気概もないくせに、 退学する勇気もなく、将来への不安だけで春を迎えていた。 ■ …

ガスパゴス携帯はじめました。

■三か月も前の話になるが、 二年間使っていたスマートフォウンをやめ、 フィーチャフォウン(ガラパゴス携帯)に戻した。 (厳密にはOSはアンドロイド系列で、 「4G」と表示されているので ガラパゴス携帯 …

no image

人生で十本の指に入る後悔「TV録画しとけばよかった」。

■この、「明瀬祐介日記」は、意識してはいないものの、「若いころの自分が読んだら役に立っただろう」という記事がある。 下記などは、自分が一〇歳代のときに気づいたら、もっと人生が楽しかっただろうと思う内容 …

no image

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

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