Excel。VLOOKUP。複数のシートから1枚のシートに転記させたい時はVLOOKUPが使えます。

Excel。複数のシートから1枚のシートに転記させたい時はVLOOKUPが使えます。

VLOOKUP関数+INDIRECT関数

複数のシートから１枚の集計シートに転記したいけど、セル参照では、とってもたいへんなので、
何か効率的な方法はありませんかね？

というご質問がありまして、何かいい方法はと考えてみたのですが、
ここは、VLOOKUP関数をつかってみても面白いかと思い、ちょっとそれをご紹介。

では、どのような表なのかと言いますと…

シート名は、山田で、A4:E6に　山田という名前の定義をしております。

シート名は、鈴木で、A4:E6に　鈴木という名前の定義をしております。

シート名は、佐藤で、A4:E6に　佐藤という名前の定義をしております。
そして、集計表。

やりたいことは、
それぞれの科目の平均値をこの集計表シートに転記していきたいという訳なのですが、

例えば、セル参照を使ってもいいのですが、
それぞれのセルごとにセル参照を一つずつやらないといけない訳でして、それが、面倒ということ。

具体的に考えてみると、例えば、集計表のB4には山田さんの国語の平均値が入るわけです。

=山田!E4

という数式になるわけですが、
これを左側、あるいは、下側にオートフィルハンドルを使って数式をコピーをしても、
うまくいきません。

C4の数式は、=山田!F4　となってしまいます。本当は、=山田!E5にしたいわけです。

さらに、下側ですと、=鈴木!E4　という数式。セル参照をしないといけません。

そこで、何かいい方法はないかというご質問の訳ですね。
確かに3名ぐらいなら、根性でどうにか出来ますが、30人だったら、ちょっと根性では…。

で、今回はVLOOKUP関数とINDIRECT関数を使うと、
意外とこのご希望を叶えることが出来るんですよ。

では、早速作っていきましょう。

集計表のシートのB4をクリックして、VLOOKUP関数ダイアログボックスを表示しましょう。

まずは、検索値ですが、B$3。常に3行目を参照させたいので、複合参照にしておきます。

範囲には、INDIRECT関数をネストしますので、INDIRECT関数ダイアログボックスを表示しましょう。

参照文字列には、$A4　と入力します。

INDIRECT関数は、その物自体を使えることが出来ます。

A4には、山田と入力されていますので、その文字を引数として使いたいわけです。

で、なぜ、山田なのかというと、各シートの範囲に名前の定義で、
設定してあるものを使うことが出来るわけです。

実は、名前の定義も適当につけてあるわけではないのです。


では、VLOOKUP関数に戻りましょう。
列番号は、5　と入力します。
検索方法は、完全一致なので、FALSE　と入力するか、0（ゼロ）と入力します。

あとは、OKボタンをクリックしましょう。
数式は、

=VLOOKUP(B$3,INDIRECT($A4),5,FALSE)

となります。
では、あとは、オートフィルハンドルを使って数式をコピーしていきましょう。

7行目の平均を算出して、それぞれのデータを小数点第2位までにしましょう。

これで、完成しましたね。

このように、VLOOKUP関数はアイディア次第で、こんなところにも使うことが出来るんですよ。

Excel。OFFSET関数を使って関数の範囲を自動的に調整してくれる方法

Excel。OFFSET関数を使って関数の範囲を自動的に調整してくれる方法

OFFSET関数

概ね計算結果は、データの範囲の下に求めたりすることが多いですが、
場合によっては、違う所に算出することもあったります。

その時に、SUM関数などの範囲が、一定ならいいのですが、前回はA5:A8だけど、
今回はA5:A88とか範囲がバラバラの時、
イチイチ数式の範囲を変更しているのは面倒ですよね。

関数の範囲がおおむねこの範囲とかでしたら、名前の定義を使うと楽かもしれませんが、
OFFSET関数を使うというテクニックもオススメなんですよ。

ということで、AVERAGE関数を使って、
その範囲を自動的に調整してくれるようにする方法をご紹介していきます。

下記のような表があります。

A列のテスト結果の平均を求める場合は、A3:A9という範囲ですが、
仮にこのデータが、C列のように件数が増えたとしたら、
当然範囲は、A3:A17と範囲が変わるわけですね。

これをデータの都度修正していくのが面倒なので、
範囲を自動的にコントロールしてくれないかな？というのが今回のやりたい事なのです。

今回登場する関数は、AVERAGE関数　OFFSET関数そして、COUNT関数です。

では、F3をクリックして、AVERAGE関数ダイアログボックスを表示しましょう。

なかなか、AVERAGE関数ダイアログボックスってみませんよね。

さて、範囲1には、OFFSET関数をネストしていきますので、
OFFSET関数ダイアログボックスを表示しましょう。

引数が多くて、ちょっとビックリですが慌てずに作っていきましょう。
参照ですが、これは、範囲のスタート地点ということになりますので、A3

行数は、その行から移動する行数ですが、移動しませんので、0（ゼロ）
列数も、その列から移動する列数も、移動しませんので、0（ゼロ）

高さは、このデータの範囲になりますから、このデータを数えてあげますから、
このボックスには、数値を数える関数であるCOUNT関数をネストしていきます。

COUNT関数ダイアログボックスを表示していきます。

値1は、データの範囲がわかりませんので、A:Aと入力します。
これは、A列という意味になります。
では、OFFSET関数に戻りましょう。
幅は、省略できますので、空欄のままで大丈夫です。

それでは、OKボタンをクリックしましょう。
F3の数式は、

=AVERAGE(OFFSET(A3,0,0,COUNT(A:A)))

となっています。

では、A10:A17に、C列のデータをコピーして、F6と同じ結果になるか確認してみます。

貼り付けてみましょう。

F6の値とF3の値が同じになりましたよね。つまり、データが増えましたが、
自動的に範囲選択が拡張されたわけですね。

OFFSET関数を使うとこのようなことができますので、結構役に立つスキルのひとつですね。

Excel。Display_format。不要な通し番号を削除したり、作業で使った番号を見えなくさせる方法

Excel。不要な通し番号を削除したり、作業で使った番号を見えなくさせる方法

表示形式のユーザー定義

前回は、リストから該当する誕生月の方を抽出する方法をご紹介しましたが、
通し番号や作業で使った数値などが見えていて、イマイチ綺麗とはいえない表でした。

このような表でしたね。
今回＋αしたいのは、F列とH列。

F列は、6～18が不要ですよね。ただ、抽出件数によって表示する範囲が変わります。

H列は、G列を表示させるために使ったものなので、
削除するわけにはいきませんが、見えなくていいわけです。

こんな風にしたいわけです。

それでは、まずはF列からどのようにしたらいいのか？を考えてみましょう。
考え方ですが、D列に抽出された件数分だけのNoを表示していきたい訳ですね。

ですので、
まずは、D列の件数を数えるために、COUNT関数を使います。

そして、その件数と行番号を比べて、
件数が多いところまでは表示させるようにしてあげるわけです。

最初は、IF関数からスタートしますので、
F4をクリックして、IF関数ダイアログボックスを表示しましょう。

論理式にD列を数えるCOUNT関数をネストしていきます。

ここで、IF関数に戻ります。

論理式には、COUNT($D$4:$D$21)と入力されていますので、>=ROW()-3と入力します。

このROW関数はそのセルの行番号を算出します。
今回は、1件目なので1を表示したいので、今算出しているこのF4は、4行目にありますので、
-3します。

真の場合は、ROW()-3
偽の場合は、””
と入力してOKボタンをクリックします。
あとは、オートフィルハンドルを使って数式をコピーしましょう。

数式は、

=IF(COUNT($D$4:$D$21)>=ROW()-3,ROW()-3,"")

ですね。
まずは、D列が完成しましたね。

つづいてH列ですね。

実務では、よく計算させるための作業用のデータを作る場合がありますが、
今回もそのケースに近いですね。

このように計算結果は、データがなければ空白とはいきませんよね。

データはあるわけですから、そこでここは、数式でどうのこうのではなく、
表示形式のユーザー定義を使って、H列を非表示にしていきます。

では、H4:H21まで範囲選択をして、セルの書式ダイアログボックスを表示しましょう。

ctrl + 1　のショートカットを使うと、セルの書式ダイアログボックスが簡単に表示されますので、
覚えておくと便利ですね。

表示形式タブの分類をユーザー定義にします。
そして、種類に、;;;（セミコロン×3）を入力しましょう。

あとは、OKボタンをクリックします。

これで、H列に算出されていたデータが見えなくなりましたね。
しかし、数式はちゃんと残っていますよね。この;;;（セミコロン×3）は覚えておくと便利ですよ。

ということで、このような関数やユーザー定義などを使って、
綺麗にするテクニックも覚えていくといいですよね。

Excel。MONTH。誕生月の方を抽出したリストを作りたいけどどうしたらいい？というご質問

Excel。誕生月の方を抽出したリストを作りたいけどどうしたらいい？というご質問

MONTH・ROW・COUNT・SMALL・INDEX関数

登録されている一覧から誕生月ごとに何人いるのか？という算出方法をご紹介しましたが、
今回は、その該当月の方を抽出したいのだけど、どうしたらいい？と、ご質問がありましたので、
今回は、それを紹介していきます。

難しそうには感じませんが、結構ややこしくて、面倒なんですね。
例えば10月の方を抽出したい場合、

考え方として、IF関数を使ってみたら、

E4には、

=IF(MONTH(C4)=$E$1,B4,"")

という数式が設定されています。
この数式では、E列に10月生まれの方の氏名が表示されるだけですね。

これでは、イマイチですね。

次にテーブルにしてみたら、どうでしょうか？

テーブルに変換して、月を10月で抽出してみます。

なお、このD4には、

=MONTH(C4)

という月を算出する関数が設定されています。

これで、10月の方が抽出されましたので、OKではありますが、
元の一覧が消えてしまっていまして、リクエストとして、元の表は見えるようにしてほしいという、
欲張りなものでした。

ということで、今回は、関数を使って抽出していくことにしましょう。

完成はこんな感じです。

では、まずは、抽出から作成していきましょう。

この抽出は、最終的にこの列を使用して、INDEX関数を使って、該当の方を抽出させていきます。

それでは、D4をクリックして、IF関数ダイアログボックスを表示しましょう。

論理式には、MONTH(C4)=$G$1　と入力します。

これは、誕生月が10月と合致するかどうか？とします。

真の場合には、ROW()-3　と入力します。
このROW関数は、その行番号を算出してくれます。
今回の一覧表は、3行目に見出しがあるので、
篠崎さんは1行目（1レコード）にならないといけませんので、-3（マイナス3）する必要があります。

偽の場合には、”” 空白　と入力します。

OKボタンをクリックして、オートフィルハンドルで数式をコピーしましょう。

続いてこの抽出で表示された数値だけを別表に集めていきます。

ここで登場するのはSMALL関数です。

SMALL関数は、データの中から指定された順に小さい数値をもってきます。

それでは、H4をクリックして、IF関数ダイアログボックスを表示します。

論理式には、抽出された件数よりも大きければ空白を入力するための数式が入りますので、COUNT($D$4:$D$21)<F4　と入力します。

真の場合は、””　空白を入力します。

偽の場合は、SMALL関数を挿入しますので、
SMALL関数ダイアログボックスをネストしていきます。

配列には、抽出のD列。$D$4:$D$21　と入力します。

順位ですが、これは、丁度NoのF列に1～連番ではいっていますので、
これを使いますので、F4　と入力します。

OKボタンをクリックして、オートフィルハンドルで数式をコピーします。

H列に数値が集められましたね。SMALL関数って結構使える関数なんですよ。
そして、いよいよ、氏名を抽出させます。
IF関数ダイアログボックスを表示します。

論理式には、H4=””　先程のH列が空白なら空白そうでなければINDEX関数をネストします。

真の場合は、””　空白を入力します。
偽の場合には、INDEX関数をネストしますので、INDEX関数ダイアログボックスを表しましょう。

その際に、引数の選択ダイアログボックスが表示されてきますので、
【配列,行番号,列番号】を選択しましょう。

配列には、$B$4:$B$21　と入力します。氏名のデータですね。
行番号は、H4　と入力します。この数値のデータをもってきます。
列番号は、0　と入力します。省略もできます。
あとは、OKボタンをクリックして、数式をコピーしましょう。

これで完成ですね。

しかし、H列など作業だけで算出させたのが見えていると見栄えが良くないので、
次回は、見栄えを良くする方法アレコレをご紹介していきます。

Excel。VLOOKUP。判定数が多い時は、IF関数よりもVLOOKUP関数のTRUEを使うほうが楽

Excel。判定数が多い時は、IF関数よりもVLOOKUP関数のTRUEを使うほうが楽

VLOOKUP関数

以前、HLOOKUP関数を使ってご紹介したことがあるテクニックを、
VLOOKUP関数に置き換えただけではあるのですが、
ご相談があって、得点によってS・A～Eと、６段階に判定したいけど、
これをIF+IF関数のネストでやろうとすると、何重にもネストをしないといけなくて、大変だし、
わかりにくいし、もっと効率的な方法ってないのかな？とのことでした。

○か×とか、ABCぐらいでしたら、IF+IF関数のネストで十分に対応できるのですが、
判定数、判断数が多くなればなるほど、煩雑になってしまいます。

そこで、登場するのが、VLOOKUP関数なのです。

VLOOKUP関数で出来るのですか？と聞かれますが、出来るんですよ。

ただ、判定表のようなテーブルを作っておく必要があります。

では、下記の表を使って紹介していきましょう。

3科目の合計点によって、右側のH3:I9にある判定表にあるように、
6段階で判定していきたいわけです。

では、F4をクリックしてVLOOKUP関数ダイアログボックスを表示しましょう。

検索値は、内藤さんの合計点ですので、E4

範囲は、判定表ですから、H3:I9。

そして、この数式を下方向にオートフィルハンドルを使って数式をコピーしますので、
範囲がずれない様に、絶対参照をこの範囲に設定しますので、
見出し行は除いて、データ部だけの、$H$4:$I$9。と入力します。

列番号。これは、毎度のことながらわかりにくいですよね。

列番号に入力する数値は2ですが、なぜ、2なのか？というと、
先程選択した範囲の左から何列目のデータを抽出したらいいのか教えて？ということなので、
ランクを抽出したいので、ランクは左から2列目ですから、2　と入力するわけですね。

そして、今回のポイントになるのが、検索方法。

だいたい、VLOOKUP関数ですと、完全一致が多いので、
FALSEか、0　（ゼロ）と入力することになりますが、
今回は、完全一致ではなくて、【近似値】になりますので、
検索方法は、TRUE　か、1　を入力します。

こうすることで、ここからここまではというような判定で使えるようになります。

あとは、OKボタンをクリックしましょう。

判定はBと表示されましたね。Bは198なので、180～239までのBが該当しますので、
合致しておりますので、オートフィルハンドルを使用して数式をコピーしましょう。

これで完成しましたね。

大崎さんの119は60～119のDで合致していますし、
田町さんの300は、Sで合致しています。

このようにVLOOKUP関数の検索方法TUREの近似値という方法を使うことによって、
IF+IF関数のネストを使わなくても、算出することができますので、
アイディアとして知っておくといいかもしれませんね。

なお、判定表は、昇順のデータでないとうまくいきませんので、ご注意ください。

Excel。phonetic。氏名のフリガナを苗字と名前に分けて表示をしたい

Excel。氏名のフリガナを苗字と名前に分けて表示をしたい

PHONETIC関数

ご質問がありまして、以前紹介した、氏名を苗字と名前に分割する方法で、
氏名を分割する方法はわかったんだけど、
氏名のデータを元にフリガナを苗字と名前に分けて表示することって出来ますか？というもの。

なるほど、確かに現場レベルでないと出てこないご質問ですね。

ただ、先に話しておきますが、Excelというか、テーブルのフィールド管理のポイントは、細かく。
が大切で、分割よりも結合の方が簡単に出来ますので、その点も含めて、
最初の段階でしっかり、フィールドを考えるほうがいいと思います。

では、下記の表があります。

このように、氏名から直接、苗字と名前のフリガナを抽出したいという訳ですね。

フリガナを算出する関数である、PHONETIC関数がいっぱい出てきますので、
煩雑でわかりにくいという方は、B列に一度、PHONETIC関数を使って、
フルネームのフリガナを算出して、それを利用したほうがわかりやすいかと思います。

では、早速作っていくことにします。

B3をクリックしましょう。

まずは、苗字ですが、左側から何文字というように算出させますので、
LEFT関数を使いますので、LEFT関数ダイアログボックスを表示しましょう。

文字列には、A3のフリガナ情報を抽出させる必要がありますので、
PHONETIC関数ダイアログボックスを表示しましょう。

OKボタンをクリックしてはいけません。
まだLEFT関数を作り始めたばかりです。

では、LEFT関数ダイアログボックスに戻り、
今度は、LEFT関数ダイアログボックスの文字数を入力していきます。

この文字数は、空白までの文字数になります。

今回A列の苗字と氏名は【全角空白】で分けておりますので、その全角空白を見つけるために、
まずは、FIND関数ダイアログボックスを表示しましょう。

検索文字列は、”　“　全角空白ですね。

対象は、A3のフリガナですから、PHONETIC(A3)。入力の仕方は先程と同じですね。

これで、全角空白のある場所がわかりましたが、その前までですから、
マイナス1する必要がありますので、LEFT関数ダイアログボックスに戻りましょう。

文字数の数式。FIND("　",PHONETIC(A3))の最後に-1を入力して、
FIND("　",PHONETIC(A3))-1
とします。

そしてOKボタンをクリックして、オートフィルで苗字のフリガナを算出しましょう。

まずは、苗字が完成しましたので、続いて名前を抽出していきましょう。

名前は、全角空白のあとの文字数ということになりますので、
C3をクリックして、MID関数ダイアログボックスを表示しましょう。

文字列には、PHONETIC(A3)　を入力します。今回はこればっかりですね。

開始位置は、全角空白の次の文字ですから、全角空白を見つけないといけませんので、
FIND関数ダイアログボックスを表示しましょう。

検索文字列には、”　“ 全角空白　を入力します。
対称は、おなじみ、PHONETIC(A3)
そうしたら、MID関数ダイアログボックスに戻りましょう。

開始位置の数式が、FIND("　",PHONETIC(A3))だと、3になっていて、
この次の文字からになりますので、プラス1をする必要がありますので、

開始位置は、FIND("　",PHONETIC(A3))+1

文字数は、開始位置以降の文字数が欲しいわけですが、A3の文字数でも問題はありません。

ここには、文字数を数えるLEN関数が登場しますので、
LEN関数ダイアログボックスを表示しましょう。

文字列には、PHONETIC(A3)　を入力して、OKボタンをクリックしましょう。
そして、オートフィルハンドルを使って数式をコピーしましょう。

ちなみに、C3は、
=MID(PHONETIC(A3),FIND("　",PHONETIC(A3))+1,LEN(PHONETIC(A3)))
という数式です。

このようにすると、
氏名から直接、フリガナを苗字と名前で分割して抽出することができますので、
色々挑戦してみましょう。

なお、名前をRIGHT関数で挑戦してみると、

=RIGHT(PHONETIC(A3),LEN(PHONETIC(A3))-FIND("　",PHONETIC(A3)))

という数式になりますが、長さがあまり変わりませんね。