関数のネスト | ねこ先生がプログラミングをガリ勉してみた(=^x^=)♪

【ARRAYFORMULA関数とIF関数のネスト】「ドラえもん」キャラの点数を分岐する

この記事を読むことで、

関数を入れるセルが１つで済むのでスプレッドシートを軽く出来る

比較的分かりやすい【ARRAYFORMULA関数】を紹介

IF関数とのネストで「ドラえもん」キャラの点数を分岐

についての概要を掴めます。

スプレッドシートにはExcelと共通して使える関数もありますが、Excelにはないスプレッドシートのオリジナル（独自）の関数もございます。

今日はその中でもIF関数と合わせて、比較的分かりやすいARRAYFORMULA関数を使って、国民的アニメ「ドラえもん」のキャラを使って、点数を分岐する方法を掲載致します。

Excelでは数式を使いたい範囲全てに、数式を入れていましたね。
その為数式を入れるとシートが重くなってしまいました。

しかし、ARRAYFORMULA関数では、適用したい範囲の最初の1つのセルにだけ関数を入れると、範囲の全てに適用出来るので、その分シートが軽く出来ます。
具体的に見て行きましょう！

具体例として、のび太やジャイアンといった、国民的アニメ「ドラえもん」のキャラの点数を分岐します。

分岐する条件として、50点未満のキャラクターには補習を受けてもらいます。

Excelと同じように関数を入れると、まずC2のセルに、

=IF(B2<50,”補習”,””)

と入れます。

はい、のび太くん、補習確定。
ドラえも〜〜〜ん（涙）

この状態で、[Shift]+[↓（下矢印）]を押しながら出来杉くんの所までドラッグして[Ctrl]+[D]を押します。

のび太とジャイアンが補習を受けることになりました。

ここまではExcelと一緒ですね。

スプレッドシートでは、のび太くんの所だけに式を入れるだけで出来杉くんの所まで式を扱うことのできる、ARRAYFORMULA関数とIF関数を組み合わせると、スプレッドシートを軽く出来ます。

一旦、ジャイアンから出来杉くんまでの判定を消します。

まずはセルC3以降の数式を全て削除して、C2のみに式を入れます。
今、数式バーはこの様になっています。

=if(B2<50,”補習”,””)

この状態で下記のキーを同時に押します。
（Macの場合は[Command]キーに読み替えて下さい。）

[Ctrl] + [Shift] + [Enter]

すると、数式がこの様になりました。

=ArrayFormula(if(B2<50,”補習”,””))

このB2の後に、「:B6」を加えます。

=ArrayFormula(if(B2:B6<50,”補習”,””))

この関数のスペルを覚える必要までは無いかもしれませんが、ポイントは、

・ARRAYはR

・FORはフォーユーとかのFOR

・MULAはL

・範囲はのび太から出来杉くんまでのB2:B6を指定す

になります。

ARRAYFORMULA関数についてもっと知りたい方へ

Googleの神様でG神と呼ばれている牛乳屋さんcooker8 by 明治クッカーの西原亮社長、にっしー氏のチャンネルがオススメです！

（私は、ただの1視聴者であり、にっしー社長とは面識がございません。）

この動画では、ARRAYFORMULA関数の他に、氏名や年月日を分けるのに便利なSPLIT関数や、年・月・日を指定する関数、転置するのに便利なTRANSPOSE関数、ミニグラフを作るのに便利なSPARKLINE関数の使い方も勉強出来ます。

ご精読、誠に有難うございました。

スプレッドシートのオススメ本を紹介いたします。

■ できるYoutuber式 Googleスプレッドシート現場の教科書できるYouTuber式シリーズ ■

単行本（ソフトカバー）
Kindle版

■ なんの才能もない発達障害者の声: 〜届かない声を届けたい〜 (障害者雇用) ■

殆ど障害者雇用や発達障害の悩みについての内容ですが、厳選してスプレッドシートやExcel等の「障害者雇用のお仕事で特に役に立ったショートカットキー」についても掲載しております。

Kindle版

お疲れ様でした。ブレイクタイムPhotoは、

江ノ電、鎌倉高校前の海です。

【OFFSET関数、INDEX関数・MATCH関数のネスト】で「ドラゴンボール」と「迷子のトランクス」を探せ！！

「か～～～め～～～は～～～め～～～は～～～～！！！」

小学生の頃、ドラゴンボールのキャラの悟空になりきっていました（笑）

この記事ではドラゴンボールを題材に、

・OFFSET関数でドラゴンボールを見つける

・INDEX関数とMATCH関数で、ベジータとブルマの子どもで「迷子になったトランクス」を見つける

・関数のネストに自然に慣れる

についての概要を掴めます。

ご存知かと思いますが、「ドラゴンボール」について簡単に書きますと、ボールを7個集めると願いが叶うという格闘少年漫画です。

オレンジ色のボールに、★が1つから7つまで入ったボールがあり、それらをコンプリートすると、願いが叶います。

それではまず、OFFSET関数から見て行きます。

【 OFFSET関数】

下の様な簡単なシートを用意します。

表の作り方の基本的なことですが、A列縦の1〜2まで入力して、そこを選択して下にフィルをすると1ずつの連番が振れます。

また、横の数字はA列縦の1〜4まで選択してコピーをして、貼り付け先のセルB1を選択して、[Ctrl]+[Alt]+[V]を押して、下部の「行/列の入れ替え」にチェックを入れてOKを押します。

表が整いましたら、まず基準は、表の左上隅にある、赤い色で塗りつぶしたセルA1の「おうち」に致します。

その「おうち」を基準にして、黄色いセルG1の所に、オレンジのセルE4の「ドラゴンボール★」を呼び出します。

では早速、セルG1にOFFSET関数を入力します。
最初に数式の意味をお伝えしますと、

=OFFSET(おうち , 3行下 , 4列右)

という意味の式を入力します。

おうちのあるセルA1を基準にして、下へ3歩、右へ4歩移動しました。
では、実際に式を入れていきます。
（引数の指定は、下に下がる方を先に書きます。）

=OFFSET(A1,3,4)

すると、セルG1にドラゴンボール★が呼び出されました。

次に、同じセルG1に「ドラゴンボール★★★」を呼び出しますので、式を書き換えます。[F2]を押します。（お使いのノートPCによっては、[Fn]+[F2]キー）
今度は、「おうち」から10歩下に行って1歩右に行きますので、

=OFFSET(A1,10,1)

と指定します。

OFFSET関数はここまでです。
基準のセルA1のお家は「0（ゼロ）」としています。

次に掲載するINDEX関数と混同しない為に、「OFFSET」の「O（オー）」は「0（ゼロっぽい）」と抑えると分かりやすくなると思います。

【 INDEX関数】

では次に、INDEX関数の基本的な使い方を見てみます。
この様な表を用意します。

先ほどのシートをコピーしてA列と1行目を挿入します。
B列のセルの1〜7までを選択してコピーし、セルA2の所で、[Ctrl]+[Alt]+[V]→[V]で書式なしの番号のみが貼り付けられます。

同様にして、1行目も番号を振ります。
A列の1〜5までをコピーして、セルB1の所で[Ctrl]+[Alt]+[V]→ 「（下の方にある）行列の入れ替えのチェックボックスを入れてOK」で番号が貼り付けられました。

内容を下記の様にキャラクターを入れて書き換えます。

ベジータとブルマの間には、トランクスという子どもがいます。
迷子になったトランクスを探します。

上の表から、「ベジータがいる行」と、「ブルマがいる列」を目視すると、「ベジータは5行目」、「ブルマは4列目」になり、そこで迷子のトランクスが泣いています。

また、INDEX関数や、この後にお伝えするMATCH関数を理解する時にイメージとして

「縦の糸はあなた」、「横の糸は私」

・・・みたいな関数だと解釈すると親しみやすいと思いました（笑）
フィーリングはどんな勉強においても結構大事かもしれないです。

ここでオレンジのセルB17にINDEX関数を使って、ベジータとブルマの子どもで、迷子になってしまった「トランクス」を見つけます。

セルB17に

=INDEX(「おうち」を含めた表の範囲,
ベジータの行の5, ブルマの列の4)

である、

=INDEX(B2:F8,5,4)

と入力すると、無事に迷子になったトランクスが無事に見つかりました！！

めでたし、めでたし！！

さて、上記の方法でも迷子のトランクスを見つけ出せましたが、実務では表の増減などの影響もあり、ベジータの行を5、ブルマの列を4といったベタ打ちは殆ど使わないのです。

GPSを使わないでヤマカンで見つけた様なものですから。

そこでGPS的な役割として、MATCH関数を用いて、それぞれベジータの行とブルマの列を求めます。

【 MATCH関数の使い方】

最初にベジータの行を求めます。「ベジータ」と書かれているすぐ下の黄色いセルB11に、

=MATCH
(ベジータ,「おうち」を含めた「行」のキャラ名の範囲,完全一致)

という意味の、

=MATCH(B10,B2:B8,0)

と入力します。

この式の意味は、B10の「ベジータ」を検索値として、
「おうち」を含めた行の部分の、B2からB8のキャラクター名の範囲を調べ、
5番目であることの結果が返って来ました。
最後の0は、ピッタリ一致する、完全一致という意味です。

この式の最後の「0」の他に、近似一致と言ってこの部分を「1」にすると最も近い値を求めることも出来ますが、今回は「ベジータ」がいる行を求めたいので、ピッタリ一致する「0」を指定しました。

次にブルマの列を調べます。
ブルマの下のセルB14に式を入れます。

=MATCH
(ブルマ,「おうち」を含めた「列」のキャラ名の範囲,完全一致)

という意味である、

=MATCH(B13,B2:F2,0)

と式を入れました。

【 INDEX関数とMATCH関数の合わせ技 】

この表から一旦セルB17に入った式を削除して、現在下の様にして、セルB17に迷子になったトランクスを探します。

オレンジのセルに、

=INDEX
( おうちを含む表の全て , ベジータ行 , ブルマ列 )

を意味する、

=INDEX(B2:F8,B11,B14)

を入れると、おうちから数えて5行目下、4列目右に行った所のセルの「トランクス」が求まります。

また、ベジータとブルマのセルを参照しない方法で、関数のネストを使って、INDEX関数の中にMATCH関数を用いることでも同様に求まります。

一度、オレンジのセルのB17を消して、

=INDEX(おうちを含む表の全て,
MATCH(ベジータ,行範囲,0),
MATCH(ブルマ,列範囲,0)
)

と、関数のネストを使用した数式に入れると、おうちを含む表の全ての所に、B2〜F8が入り、
「ベジータ行」の所にMATCH関数で求めた5行目、
「ブルマ列」の所にMATCH関数で求めた4列目が入りました。

=INDEX(B2:F8,MATCH(B10,B2:B8,0),MATCH(B13,B2:F2,0))

トランクスも見つかって、一安心。

この記事で紹介した、OFFSET関数、INDEX関数とMATCH関数の合わせ技を使いこなせる様になると、VLOOKUP関数の限界を突破して、Excelでの表現力が増しますね(=^x^=)！

お疲れ様でした。ブレイクタイムPhotoは、

スカイツリーと河津桜です。
河津桜はソメイヨシノよりも少々早咲きです。

ご精読誠にありがとうございました。

■ Excelの学習にオススメ本の紹介 ■

「できるYouTuber式 Excel現場の教科書（長内孝平氏）」

単行本（ソフトカバー）
Kindle版

「なんの才能もない発達障害者の声: 〜届かない声を届けたい〜 (障害者雇用向け)」

私、猫★シエスタの著作です。殆どが「発達障害者として働くことや障害とSNSについて」ですが、障害者雇用の事務作業で特に使用頻度の高いキーに絞ったExcelなどのショートカットキーの紹介と、演習問題も掲載しております。
Kindle版

【ExcelのVLOOKUP関数とXLOOKUP関数】IF関数、IFERROR関数のネストでエラー対応。イタリア語の数字に触れてみよう!!

この記事では、Excel関数の中でも特に実務での使用頻度が高い、

・VLOOKUP関数の使い方

・エラーの際のIF関数やIFERROR関数のネスト

・検索値のドロップダウンリストの作り方

・XLOOKUP関数の便利さ

の概要が掴めます。

この使い方に慣れておくと、「Excel関数だったら、是非あなたにお任せしたい！！」と、安心してお仕事を任せて貰える可能性が高まりますので、宜しかったらご覧下さい。

まず、この様な簡単な表を用意します。
（ヘタリアというアニメが好きなので、イタリア語を例に致しました。）

※ 数字は半角に統一しておくと、学習がスムーズになります。 ※

イタリア語の数字のサイトを参照しました。

では、早速VLOOKUP関数を使ってみます。
検索値はセルA1に致します。

セルA1の検索値を、まずは「2」にしてみます。

では、ここで隣の黄色いセルB1にVLOOKUP関数を入力致します。

「 =vl 」まで入力したら、「Tabキー」を押すと、VLOOKUP関数が選択出来ます。
この様な式を入れます。

=VLOOKUP(A1,A4:B6,2,FALSE)

関数の中身が沢山ありますので、1つ1つ見ていきます。

・検索値の所をA1セルをクリック
・範囲の所をA4:B6で表を囲う様にドラック
・列番号を2
・完全一致にするので「0」または「FALSE」

ここまで入力したらEnterを押します。
この関数の中身のことを「引数」と呼びます。

セルB1にこの様な式が入りました。

この様に、B1セルにイタリア語の２に当たる「due（ドゥエ）」が表示されたらOKです！

ところで、検索値のA1セルを消すと、黄色いセルにこの様にエラーが出てしまいました。

このエラーをまずはIF関数を使って修正します。
黄色いセルB1に

=IF(A1=””,””,VLOOKUP(A1,A4:B6,2,FALSE))

と入力します。

式の意味は、もしも検索値のセルA1に何も入っていなかったら空白を返し、数字が入っていたら対応するイタリア語を表示する、と言う意味です。

これで、無事にエラーが消えました。
この様に関数の中に関数を入れることを「ネスト」と呼びます。

試しにここで、セルA1の検索値に1〜3の数字を入れると、この様に表示されます。
例としてセルA1に入力する検索値を「3」にしました。

では、1~3以外の数、例えば検索値に「7」が入っていたとします。

この場合はIFERROR関数を使います。

=IFERROR(値,”エラーメッセージ”)

となる様に、黄色いセルのB1に下記の式を入力します。

VLOOKUP関数の部分が値に該当して、なるべく分かりやすいエラーメッセージを入れます。

=IFERROR(VLOOKUP(A1,A4:B6,2,FALSE),”1から3までの数字を入力して下さい”)

この式では、もしも値が正しかったら該当するイタリア語を表示して、エラーだったらエラーメッセージを表示すると言う意味です。

現在この様になっていればOKです。

検索値の1,2,3を手打ちで都度入力するのは面倒ですよね。
それに、7などの数を誤入力されてしまうこともあります。

そこで誤入力を防ぐ為にも、検索値のセルA1にリストを設定します。
一旦、セルA1の7を削除してからドロップダウンリストを作成します。

まず、セルA1を選択して、
「データ」タブ → データの入力規則 で、下記のダイアログボックスが出ます。
一番左の「設定」タブを選択して、入力値の種類を「リスト」にします。

次に、リストにする値を指定します。

「元の値」の右の横のボタンをクリックして、ピンク色のセルのA4〜A6をドラッグしてOKを押します。

元の値にA4-A6の絶対参照が入っていればOKです。

これで、Windowsの方は、「Alt」+「↓」、Macの方は「option」 + 「↓」で、リストで「1,2,3」と選択出来るようになりました。

これで実際にリストから数字「1」を選んでみます。

無事にリストから選択出来、黄色いセルに表示されました。

お待たせしました!!
ではここからXLOOKUP関数について掲載いたします。

XLOOKUP関数はVLOOKUP関数のパワーアップ版の関数ですので、検索値の左の値を取得することも可能になりました。

先ほどの表のシートをコピーして、検索値とイタリア語を逆にしてこの様に作りました。

黄色いセルには、

=XLOOKUP(A1,B4:B6,A4:A6)

と式が入っています。

この中身をなるべく分かりやすく緩い感じで書きますと、

=XLOOKUP(検索値,検索する範囲,取ってくる範囲のある戻り範囲)

になっております。

更に、先ほどのVLOOKUP関数で半角の1-3以外の値を入れた場合は、IFERROR関数や空白の場合はIF関数を使いましたが、XLOOKUP関数はエラーの場合も引数で指定出来ます。

上の表は検索値に6を入れて、XLOOKUP関数はこの様に引数の指定をしています。
見つからなかった場合に「半角数字の1-3まで入れてね♪」の文字列を指定しました。

=XLOOKUP(A1,B4:B6,A4:A6,”半角数字の1-3まで入れてね♪”)

空白の場合もこれで何をすれば良いのか伝わると思いますので、XLOOKUP関数は複数の関数のネストせずに単体でエラー値の場合も指定出来るという利便性があります。

そろそろOffice365の環境になって来た方も増えて来たと思いますので、XLOOKUP関数での場合も追記致しました。

お疲れ様でした。ブレイクタイムPhotoは、

遊歩道の桜並木です。

ご精読誠にありがとうございました。

■ Excelの学習にオススメ本の紹介 ■

■ 関連記事 ■

【IF関数】天気予報と雨具の準備の例で習得

この記事では、関数の王様的な存在の、IF関数について掲載致します。

・Excelは使ったことがあるけれど、関数は初めて

・学生の方の授業や講義、就活の準備

・普段の業務や転職活動に向けて関数に慣れておきたい

・関数の復習をしたい
（最近プログラミングばかりでExcelはお久しぶり等）

に当てはまる方を主な対象にしておりますが、それ以外の方もご興味がありましたら是非ご覧になって下さると嬉しいです。

例として、北海道と東京と大阪の降水確率を用いております。

IF関数を使って、「YESだったら丈夫な傘を持っていく」、「NOだったら「-」と入力する」という、「降水確率ごとの条件分岐」が理解出来ることを目指しております。

まず、天気予報でB列の降水確率が「60以上」でしたらC列の「雨具の用意」の所に「丈夫な傘」と表示されるようにします。

お手数ですが、可能な方は下記のような簡単な表をご用意頂けますと、実際に手を動かしながらIF関数に慣れていくと思います。
配色はお任せ致します。

（地域と降水確率は適当であり、傘を持って行くかどうかも私の私的な判断です。）

IF関数は、黄色いセルがあるC2の所に入力します。

=IF(条件,”当てはまる場合”)

セルC2をのところで、「　=if　」と入力してTabキーを押すとIF関数が選択出来ます。

=IF(B2>=60,”丈夫な傘”)

という様に式を入れます。

北海道の降水確率は10なので、「違うよ！丈夫な傘は持っていかなくても大丈夫だよ」という意味のFALSEが求まりました。

この数式をC2セルの右下の小さい四角いハンドルを下に引っ張って、「書式なしコピー（フィル）」を選ぶと、

大阪の降水確率が70なので、そこのみに「丈夫な傘」と入りました。

FALSEって英語ですし、ちょっと分かりにくいので、条件に当てはまらない場合は「-」などを表示したい場合は、セルC2に

=IF(条件,”当てはまる場合”,”当てはまらない場合”)

という処理になる、

=IF(B2>=60,”丈夫な傘”,”-“)

この様に式を入れます。

無事に表示されました。
ただ、右下のオートフィルオプションが邪魔ですよね。

これを消すには、何もない任意のセルの所で「スペースキー」を押してから、空いたスペースを消してみて下さい。
（環境によって異なる場合もございます。その際お手数ですが検索をお願い致します。）

セルの入力には[F2]キーが便利です。
（ノートPCの環境によっては、[Fn]も[F2]と一緒に押して下さい。）

はい、無事にオートフィルオプションが消えました。

今度は、降水確率が30以上の場合は、「折り畳み傘」と表示させる様にします。
先ほどの式、

=IF(条件,”当てはまる場合”,”当てはまらない場合”)

の、当てはまらない場合の所に、更にIF関数を入れます。
これを、関数のネストと言います。
セルC2の所に、

=IF(条件,”当てはまる場合”,
IF(次の条件,”当てはまる場合”,”当てはまらない場合”)
)

となるように、

=IF(B2>=60,”丈夫な傘”,IF(B2>=30,”折り畳み傘”,”-“))

と式を入れて数式を下まで引っ張っていくと、

一旦、ここまででの流れをまとめます。

降水確率が、

60以上 …..…………….. 丈夫な傘
60未満で30以上 .……. 折り畳み傘
30未満 …..…….….……..「-」

となります。
最後の30未満は、上記のどれにも当てはまりません。

IF関数の根幹の部分は以上でございます。

お疲れ様でした。ブレイクタイムPhotoは、

恵比寿ガーデンプレイスのクリスマスイルミネーション、
フランスのクリスタルメーカー、バカラのシャンデリアです。

ご精読誠にありがとうございました。

■ Excelの学習にオススメ本の紹介 ■

■ 関連記事 ■

月	火	水	木	金	土	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31