「コピぺ de 関数」FILE_15
今回は、ところどころ空白のセルが混ざってるの取り除いてデータを詰めたい、というときに役立つテクニックをお伝えいたします。
今回は「もらったデータにちょいちょい空白のセルが混ざってて、歯抜けなデータを整えたい！」って時に使えます。
実際に、自分が仕事をしていた中でこういうデータをもらったことがあって、データの個数が何個あるのかも数えにくし、一個一個セルを削除するのが面倒なほどデータの数自体は多かったので、一発でデータを整理したい時がありました。
列のパターンと行のパターンで使う関数が異なるので、それぞれご紹介いたします。
まずはご自身のExcelを開き、下記のデータを、A1に貼り付けてください。
| 名前 | 好きな物 | 嫌いな物 | 趣味 | 職業 |
|---|---|---|---|---|
| 安室 | みかん | |||
| 赤井 | ||||
| 白鳥 | ||||
| 目暮 | ||||
| 毛利 | ||||
| 江戸川 |
スッカスカのデータですが、A列で名前が抜けているのを詰める方法と、2行目を見て横に並んだデータを詰める方法、2通りの方法を書いていきます！
縦に並んだデータの中で、空白セルがあったら取り除いて詰める
理屈はあとから！まずは、G2セルに下記の数式を入れてください。
=IFERROR(INDEX(A:A,SMALL(IF(A:A<>””,ROW(A:A)),ROW())),””)
結果は、「安室」になるかと思います。
そして、G2セルの数式をG9セルまでズズズイーっとコピーしてください。
すると、空白セルであるA4セルとA8セルを除いた名前リストが出来上がりました！
ただ、A11セルに入っているタイトルの文章まで引っ張ってきてしまいました汗
頭の良い方は、「じゃあG2セルの関数をいじって、対象範囲をA2:A9みたいにすればいいじゃね？」って思うかもしれません。
試しにやってみましょう。
そうすると「0」という訳のわからん答えが返ってきます。（安室さんにゼロってなんか・・・w）
数式の中で、3回、A:Aと記述してますが、一部をA2:A9とかしても機能しないので、この数式を成立させるにはA:Aと記述する必要があるみたいです。（A2:A9と書いても構文エラーにはならないですが）
ですので、データが整って、こっから下はいらない！ってセルを見つけて、そのセル以下を削除してしまいましょう。
そうすれば、きれいにデータが整った状態になります！
ちなみに、その後さらにデータを利用するのであれば、一旦、G列の値（正確には数式の結果）を値に変えておく方が良いと思います。
G2セルからG9セルの範囲を選択して、コピー、そのまま右クリックで貼り付け（値のみ）すれば、数式の値が「安室」、「赤井」といった中身になります。
横に並んだデータの中で、空白セルがあったら取り除いて詰める
次に、横一列にデータが並んでいた場合のやり方です。
A13セルに、下記の数式をコピペしてください。
この時、さっき入力したG2～G7の数式は削除しておいてください。（厳密に言えば、G2セルだけ消せば大丈夫です）
=IFERROR(INDEX(2:2,SMALL(IF(2:2<>””,COLUMN(2:2)),COLUMN())),””)
そうすると、A13セルは「安室」となります。
A13セルを選択して、C13セルまで右にズズズイーっといくと、安室、みかん、公安になります。
数式の挙動ですが、正直説明しづらいですw
ざっくり言えば、rowまたはcolumn関数を使って、空白じゃないセルの行または列番号を数の小さい順に返して、その中身を表示、という感じだと思います。
なっかなか使う場面が無いかもしれませんが、ITの仕事をしていれば、歯抜けデータがに会う日が来るかもしれません。
そのとき、このサイトを思い出してつかってやってください(笑)
