Excel関数に関するお悩み等ありましたらお問合せフォームからメッセージください(^^)

Excel関数活用術!VLOOKUPで行間を詰める方法

どうもasです。

本日は私が仕事でよく使う関数の活用術を紹介したいと思います。

それではいってみましょう~。

 

VLOOKUP活用術

皆さん、仕事で何かを集計した時に、別シートに特定の条件のものを移して別の表を作成したい時ありませんか?

例えば下の画像のように・・・

 

 

そんな時、フィルタリングやピボットテーブルでも可能ですが、VLOOKUPでも同じことができるって知ってましたか?

今回紹介するテクニックを覚えれば、色々な場面で役立つので是非覚えてみてください!

 

今回使う関数一覧

○     VLOOKUP
○     COUTIF
     MAX
     ROW
     COLUMN
     IFNA

 

上記関数の使い方がわからないって方もサンプルの関数を貼っておきますので、何となく理解してもらえれば大丈夫です!

 

手順

今回のテクニックの手順は下記の通りです。

 

  1. 集計したデータを反映させる表の枠を作成
  2. VLOOKUPの検索値となる関数を入力
  3. 集計先の表に②を検索値にしてVLOOKUPを入力

 

以上です。簡単ですね。

①集計したデータを反映させる表の枠を作成

 

まずは集計したデータを反映させる表の枠を作成していきましょう。

 

こんな感じです。後で都道府県の所(F2からG2のセルの所)に都道府県名を入力して集計できるようにしたいので、都道府県名を入力するセルも作成しておきましょう。

 

次はA列に1列挿入します。

はい、これで下準備は完了です。
それではこれから関数を入力していきます。

②VLOOKUPの検索値となる関数を入力

まずはA3のセルに下記関数を入力してください。

=IF(COUNTIF(C3,$H$2)>=1,MAX($A$1:A2)+1,””)
この関数を簡単に説明すると、「もしH2に入力した都道府県名がC3に含まれていれば、A1から自セル(この場合A3)の一つ上までで一番大きな数字に1を足した数字を表示して、含まれていなければ空白にする。」っていう関数です。
はい、上記画像の選択しているセルの部分ですね。
この関数を入力したら、まずはこの関数が正常に動作するか確認してみましょう。
試しに、都道府県名入力欄(H2)に”東京都”と入力してみてください。
はい、上記の画像のようにA3セルに「1」と表示されればOKです。
そしたらA3セルの関数をA12までコピーしてください。
すると東京都の行にナンバリングがされたと思います。

③集計先の表に②を検索値にしてVLOOKUPを入力

続いて、VLOOKUPを使って集計先の表に東京都だけを集計してみましょう。

G5のセルに下記関数を入力してください。

=IFNA(VLOOKUP(ROW(A1),$A:$E,COLUMN(B1),FALSE),””)

この関数を簡単に説明する前に、ROWとCOLUMN簡単な説明を。

ROW関数
この関数は括弧で囲んだセルの行数を返す関数です。
例えば、ROW(A1)と入力したら「1」、ROW(G5)と入力したら「5」というように行数を数字で返します。
つまり、指定したセルの列は完全無視で行数のみを返す関数です。ちなみに、何も入力せずROW()と入力すると、自セル(その関数を入力したセル)の行数を返します。
COLUMN関数

”カラム”と読みます。この関数は括弧で囲んだセルの列数を返す関数です。

例えば、COLUMN(A1)と入力したら、A列は左から数えて1列目なので「1」、COLUMN(G5)と入力したら、G列は左から数えて7列目なので「7」というように列数を数字で返します。
ROWと一緒で、指定したセルの行は完全無視で列数のみを返す関数です。
こちらも、何も入力せずCOLUMN()と入力すると、自セル(その関数を入力したセル)の列数を返します。

 

上記をふまえて、簡単に関数を説明すると、「A列の1(ROW(A1)の部分)が入力されている行の2列目(COLUMN(B1)の部分)の文字を表示して、もしno answer(検索値が範囲内に無い)の場合は空白を表示する」という意味になります。

 

 

上記関数をG5のセルに入力して「A店」の文字が表示されればOKです。

そしたらG5の関数をコピーしてG5からJ12までに数式のみを貼り付けましょう。

数式のみを貼り付ける場合は右クリックで下記アイコンをクリックすれば出来ます。

 

すると下記画像のように無事、東京都のみが行間を詰めて集計できたと思います。

 

 

 

さいごに

VLOOKUPで行間を詰める為には?の紹介は以上になります。

 

 

関数って単体で覚えただけだと大した事できないですけど、こうやって色々な関数を組み合わせて使うことによって、一気に幅が広がりますよね!

他にもExcelには便利な機能がいっぱいあって、例えばデータの入力規則を使って下記のように都道府県の入力部分を選択式にしたり、

 

 

条件付き書式を使って自動で最適な罫線と塗りつぶしを行ったり、

 

といった具合に。
今回は長くなるので紹介できませんが、気になるという方がいらっしゃればいずれ紹介させていただきます。
是非コメント欄にリクエストをお待ちしております^_^
今日はこの辺で。

業務代行始めました!

ココナラにてExcel業務の代行サービスを始めました。
以下のような事でお悩みの方、お気軽にご相談ください。

 エクセルでの業務を効率化するツールを作りたいけど、イメージ通りに作れない・・・

 業務で使っている既存のファイルが使いにくいけど、どう改修すればいいかわからない・・・

 エクセルを使った業務は苦手なので、そもそも誰かに代行してほしい・・・

 後から自分でもメンテナンスしたいから、マクロとか高度な機能は使わないで欲しい・・・