Excel基礎 | ねこ先生がプログラミングをガリ勉してみた(=^x^=)♪

ピボットテーブル(Excel)

この記事ではフリーのサンプルデータを使って、Excelで簡単にピボットテーブルを作成する方法を掲載しております。

サンプルデータについては、前の記事を参照下さい。

現在、Excelでこの様な画面になっていることを前提として進めさせて頂きます。

[挿入]タブを選択して、ピボットテーブルを押します。

範囲の確認と新規シートを選択して、

OKを押します。

画面右側の「ピボットテーブルのフィールド」をドラッグして左に移動して、ピボットテーブルの近くに置くと作業しやすいです。

取引先名を行にドラッグ＆ドロップするとこの様になります。

同じように、商品名を列、売上金額を値にドラッグ＆ドロップするとこんな感じになります。

ピボットテーブルは自由にカスタマイズできるので、行の取引先名を「ピボットテーブルフィールド」を、行の上のフィルタにドラッグ＆ドロップします。

次に、入荷日を行にドラッグ＆ドロップするをこの様になりました。

フィルタに取引先名が入っているので、取引先名ごとのレポートも、すぐに作れます。
下の図のオプションをクリックして、

レポートフィルターページの表示をクリックして、

取引先名が選択されていることを確認して、OKを押します。

すると、

このように、取引先別にシートが作られました。

次に、最初の全員分のピボットテーブルのあるシートに戻り、行の所の入荷日をピボットテーブルのフィールド外にドラッグ＆ドロップをして、列の商品名を行に移動すると、

このように、見たいカットで表現できます。
また、行ラベルの▼を押して、昇順や降順に並べ替えが可能です。

以上、ざっくりですが、ピボットテーブルをサクッと使うことが出来たかと思います。

お疲れ様でした。
勉強のリフレッシュに、写真で息抜きを致します。

写真は、お台場海浜公園の冬の花火大会、レインボー花火大会からの1枚です。

この記事だけでは、入門的なことしか学べませんでしたので、より深くピボットテーブルについて学びたい方は、スプレッドシートのピボットテーブルの詳細の使い方について詳しいブログをリサーチしましたので、そちらをご覧ください。
尚、このブログの2018年度の割合を調べる所は、おそらく「P5セルの絶対参照」だと思います。

仕事や勉強のリフレッシュに、趣味で写真を撮っておりますので、宜しかったら フォトストック写真ACさん の投稿もご覧頂けますと、大変嬉しい限りでございます！！

こちら、無料の「ダウンロードユーザー」に登録して頂けると、無料で写真のダウンロードが可能になります。

※ 先にGoogleアカウントを作成して頂くと、登録がラクです♪

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

最後までご精読、誠にありがとうございました！！

ピボットテーブルをサンプルデータから作成する（Excel / スプレッドシート共通）

この記事では、可能な限りラクしてピボットテーブルを作成する方法をExcelとスプレッドシートの両方の共通する箇所を掲載しております。

ピボットテーブルを独学で勉強する時に一番躓くのって、実はピボットテーブルそのもの自体ではなく、ピボットテーブルの学習用のサンプルを用意する所だったりします。

なので、完全フリーのサンプルデータの中から私が最もピボットテーブル向きのサンプルを選んで、それを元にExcelとスプレッドシートの両方でピボットテーブルを作る方法を解説させて頂きます。

このブログでは、日本アプリケーション開発様 のテンプレートを使用します。

テンプレートはこちらです。

上記のリンクを開いて、少し下にスクロールした所にある、化粧品店向けのサンプルのダウンロードボタンを押して、任意の場所に保存します。

マクロは「無効で」開いて下さい！！

実際に使うのは一番左のシート「データ表」のC-J列までですので、C-Jを選択してお使いのExcelまたはスプレッドシートにコピペして使います。

この赤い枠の所のA1などを削除して、

C3:J18

と入力して、Enterを押すと範囲が選択できるので、[Ctrl]+[C]でコピー、自分が使いやすいExcelファイルまたはスプレッドシートで、[Ctrl]+[V]で貼り付けします。

Excelに貼り付けた場合も、スプレッドシートに貼り付けた場合でも、[Ctrl]+[A]で全選択して、列の境界線の好きな所（例えばA列とB列の間の線）をダブルクリックすると、列の幅が文字に合わせて自動で調整されます。

ここから先は、

Excel

スプレッドシート

に分けて、ピボットテーブルの基本的な使い方を解説致します。
入門レベルのなので、楽な気持ちでご覧頂けますと幸いです。

COUNTIFSでのび太とジャイアンの忘れ物を集計する

この記事では、国民的アニメドラえもんの主要キャラの、のび太とジャイアンの忘れ物を集計しながら、COUNTIFS関数について学んでいきます。

こんな感じの表を作ります。

セルのE5の所に、のび太の忘れ物の数を数えるために、この様な関数を入力します。

最初に、式の意味をお伝えします。

=COUNTIFS(名前の範囲 , のび太の名前)

具体的な設定は、下記の様になります。

=COUNTIFS($A$2:$A$15,D1)

＄マークは絶対参照なので、F4キーを押すと出てきます。
（Macの方は、Fn＋F4になります。）

のび太の忘れ物は8件でしたね。

このセルをそのままジャイアンの所に引っ張っていくと、

ジャイアンの忘れ物が6件となりました。
名前のA列を絶対参照で固定しているので、そのまま引っ張るだけで求められました。

これ、ちなみに、COUNTIFSではなく、COUNTIF関数でも求めらます。
今回COUNTIFSを使ったのは、次の表の集計でも使う為です。

オートフィルでは、書式なしコピーもすることが可能です。

このオートフィルオプション、用が終わったら結構邪魔ですよね。

そこで、範囲外の空白セルの所で、[BackSpace]を押して表示させないように致します。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、逗子海岸です。

次は、先ほどの例よりも、ちょっとだけややこしいのですが、のび太やジャイアンは、何をどれだけ忘れたのかを集計します。

セルE6にこのように入力します。

まずは数式の意味は、

=COUNTIFS
(名前の範囲 , のび太 , 忘れ物の範囲 , 教科書）

で、絶対参照などが出てくるので、これについては、

・名前の範囲は[絶対参照]
・のび太は [列]固定の複合参照
・忘れ物の範囲は[絶対参照]
・教科書は [行]固定の複合参照

となり、具体的な数式は、

=COUNTIFS
($A$2:$A$15,$D6,$B$2:$B$15,E$5)

と指定します。

ちょっとややこしいですね。
面倒ですが、最初だけ絶対参照・複合参照を設定すると、後で式を引っ張っていく時にラクなんです！！

これを下に引っ張って、

右に引っ張って

書式なしコピーのフィルにします。

このフィルタオプションをまた、範囲外の空白セルの所で、[BackSpace]で非表示にします。

詳しくは外部サイトの、エクセルでオートフィルのオプションボタンを消すには をご覧下さいませ。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、立川市の多摩モノレールです。
（立川市と日野市の境目の立日橋付近で撮影。立日橋って結構そのまんまな名前ですね。）

このブログの読者様はご存知のことですが、私には発達障害があり、忘れ物が多すぎて悩んでいました。

実は、のび太とジャイアンにも発達障害があるのでは、という記事を見つけましたので興味のある方はご覧下さい。

ADHD(注意欠如・多動性障害) | のび太・ジャイアン症候群

のび太の方が、忘れ物は多めな感じですね。
ジャイアンの場合は忘れ物をしても、「お前のものは俺のもの！！」という武器もあったり・・・（笑）

・
・
・
Excelもスプレッドシートも、学ぶ範囲が広すぎたり、自分が頑張って勉強してもあまり実務に役に立たなかったりということに悩んでおりました。

その悩みを解決する本を見つけましたので、早速紹介させて頂きます。
最近の学びでは、本と動画が連動していて、本にQRコードが付いていて、YouTube動画で講師の方が分かりやすく説明して下さる、「本×動画」という新しい学び方に目から鱗でした。

動画登録者20万越えの教育系YouTuberでお馴染み、Yousefulの長内孝平氏、神川陽太氏のExcelとスプレッドシートの本では、「現場の教科書」というタイトルが付いてある通り、実務に直結した内容をまずは本で要点を掴んで、動画で詳しく解説して下さっています。
個人的にはお二人の動画最後の「See you next time! Bye Bye!!」が好きで勉強のモチベーションが上がる教材です。

講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。

若い方から教わることに抵抗のある方もいらっしゃるかも知れませんが、Yousefulの講師の方は親しみやすいので大丈夫です！

ロスジェネ世代の方達は、いずれは若い方から学び直しなどで教わることも沢山出て来ると思います。
ですので、本や動画を通して若い方から教わることに慣れて置くというのもこれからより良く生きる為に役立てると私は考えております。

できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏

ベストセラー1位に輝いたExcelの本です。
長内氏はMicrosoft本社が公式で認めるテクノロジーの専門家であり、徹底的な分かりやすさで語りかけるように動画でExcelを教えて下さっています。
学習用のサンプルも付いています。

できるYouTuber式 Googleスプレッドシート現場の教科書
神川陽太氏＆長内孝平氏

この本には、スプレッドシートだけでなく、Excelではどう解くのかも書かれているので、スプレッドシートとExcelの復習を同時に行いたい方には、かなりオススメです！
教材のサンプルのスプレッドシートは閲覧用ですが、コピーをして自分のスプレッドシートに貼り付けて演習を行うことが可能です。

お疲れ様でした。
ブレイクタイムPhotoは、東京都足立区の桜です。

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

東京都の新型コロナ感染者数をExcel関数で集計する（平均、四捨五入、最大値、最小値、件数カウント、グラフ）

東京都の新型コロナ感染者数から、平均（AVERAGE）・最大（MAX）・最小（MIN）・感染者数が1000人越えの件数(COUNTIF)といった、基本的な集計で使うExcel関数を勉強していきます。

平均を求めると、小数点以下の桁数がたくさん出て来てしまうので、ROUND関数やINT関数についても掲載致します。

また、関数の王様とも言われる、IF関数を使って、感染者数が平均以上かどうかで処理を分岐します。

感染者数は、ちょっと古いデータなのですが、令和3年1月30日に調べたデータで関数を使ってみます。

関数に加えて、条件付き書式や日付の表示形式の説明もございます。

まず、平均を求めてみます。
セルE2のところに、「=AVERAGE(B4:B10)」と入力します。
( 「=ave」と入力後、Tabキーで補完すると楽に関数名が入力出来ます。 )

このように求まりました。
ただ、小数点以下の桁数は、こんなに要らないです。
そこで、小数点第1位まで求めます。
スムーズに関数を編集できる様、「F2」キーを押して数式をこのように編集します。

=ROUND(AVERAGE(B4:B10),1)

切上げの場合は、ROUNDUP、切り下げの場合はROUNDDOWN、単に整数のみの場合は、INT関数を使います。

=INT(AVERAGE(B4:B10))

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、足立区の荒川河川敷から見たスカイツリーです。

では、次に、再びこのシートをご覧下さい。

C4の所で、平均値以上だったら「平均以上」と表示して、違ったら「—」と表示させるようにします。
式をこのように入れます。

ここで気をつけるポイントは、平均値が入っているセルE2は、固定する必要がありますので、絶対参照にします。
1月23日の数式を下に引っ張っていくとズレてしまうので、[F4]キーを押して＄（ドル）マークを付けています。

=IF(B4>=$E$2,”平均以上”,”—“)

セルC4の右下の四角をダブルクリックして、オプションを「書式なしコピー」にすると
こうなります。

もしも、オートフィルが消えなくて困る場合は、何もないセルを選択して、[BackSpace]を押してみて下さい。

次に最大値です。セルG2に「=MAX(B4:B10)」と入力します。
最小値はセルI2に「=MIN(B4:B10)」と入力します。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は浅草駅から見たスカイツリーです。

さて、ちょっと関数から離れて、条件付き書式の設定をします。
最大値と最小値に書式を設定します。

まずは、人数のデータの所を選択します。

ホームタブから条件付き書式を選択して、

条件付き書式を設定します。

セルの強調表示ルール → 指定の値に等しい

を選びます。

最大値を参照する様にして、書式は好きなのを選びます。

OKを押します。

最大値の人数に書式が設定されました。

同様にして最小値にも書式を設定しました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

真っ赤な薔薇が結構綺麗に撮れたかな。

では、関数に戻って1000人以上の回数を数えます。

K2に

=COUNTIF(B4:B10,”>=1000″)

と入力するとこの様に「3件」と求まります。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、江ノ電に乗って鎌倉高校前駅の海で撮影しました。

では、日付を「1/23(土)」のような形式にします。
これはA4からA10の日付が入っている所を選択して[Ctrl]+[1!ぬ]を押します。

分類を「ユーザー定義」にして、種類を「m/d(aaa)」と入力します。
OKを押します。

この「m」はmonth（月）で、「d」はday（日）で、「aaa」は曜日を表しています。

このように書式が変わりました。

また、人数をカンマ区切りにするには、人数の範囲を選択して[Ctrl]+[Shift]+[1!ぬ]
と押すとカンマ区切りになります。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

同じ海でも、こっちは房総半島の外房の御宿の海です。
雰囲気が変わりますね。

では、最後にグラフを作ります。
A3からB10までを選択します。

挿入 → グラフの右横の□→

すべてのグラフ　折れ線グラフマーカー付き折れ線グラフ
を選択してOKを押します。

このようにシンプルなグラフが出来ました。

このグラフを見て気になる所は、400より下の数値が不要だということです。
なので、左側の1,200から0の範囲ならどこでも良いので、好きな所で右クリックをします。
右側に「軸の書式設定」が出るので、最小値を400にします。

これで、だいぶ見やすく成りました。

あとは、グラフが選択された状態で、

グラフツール → デザインの右下から、好きなデザインを選べます。

色を変えたい時は、その左の「色の変更」から好きな色を選べます。

線のカクカクが気になる方は、折れ線部分を右クリックして、データ系列の書式設定を選んで、
バケツマーク →　マーカーまで行って、下にあるスムージングを「レ」チェック入れます。

すると、カクカクが滑らかになりました。

このままでも良い場合もありますが、グラフシートを作りたい場合は、グラフを選択した状態で、デザインタブ → グラフの移動 を選択します。

新しいシートを選んで、好きなタイトルを入れます。

そうすると、このようにグラフシートになりました。

お好みでこのように装飾することも可能です。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、2022年にお台場レインボーブリッジの冬の花火大会を、お台場海浜公園から撮影した写真です。

講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。

できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏

できるYouTuber式 Googleスプレッドシート現場の教科書
神川陽太氏＆長内孝平氏

皆さま、大変お疲れ様でした。
この記事最後のブレイクタイムPhotoは・・・

令和元年（コロナ前最後の夏）に撮った、いたばし花火大会です。

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

国民的アニメのキャラクターをVLOOKUP、IFERROR、COUNTA、OFFSET、COLUMN関数で、表の範囲を可変に操作

Excelで関数を使うとファイルが重くなりますよね。
また、エラーが表示されると見映えも悪くなります。
そこで、実務でよく使う関数である、

・VLOOKUP関数
・IFERROR関数
・OFFSET関数
・COUNTA関数
・COLUMN関数

の合わせ技を使ってみたいと思います。

OFFSET関数については、先に下記の記事を読んでおくと、
チョット分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ！！

では、国民的アニメを題材としたVLOOKUP関数の説明に入ります。

このシートの右側の表には、サザエさんに出て来る、
ワカメちゃんの名前があります。
作品名である「サザエさん」を黄色いセルに表示させます。

黄色いセルにVLOOKUP関数を入れます。
先に意味からお伝えします。

=VLOOKUP
(ワカメ,表の範囲,列,完全一致)

という意味です。
列の2は、表の範囲が名前をキーにして、名前が１列目、作品名が2列目なので、この場合は「2」になります。

完全一致には「FALSE」を入れます。
ここでは、完全一致にはFALSEを入れるんだなぁ〜〜〜って感じで、
あまり深く考えないで、取り敢えず先に進みましょう。

具体的なセルを入れた値はこの様になります。

=VLOOKUP(E2,A2:B5,2,FALSE)

と式を入れます。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

立川市の国営昭和記念公園の向日葵です。

では、別のキャラクターの名前を入れるので、一旦ワカメちゃんワカメちゃんを消します。

あ、エラーが出ちゃった（汗）
そんな時は、エラーの時には何も表示させないように、IFERROR関数を使って式を修正します。

式の意味は、

=IFERROR(OKな値 , エラーの値)

具体的には、

=IFERROR
(VLOOKUP
(E2,A2:B5,2,FALSE),””)

で、OKの値のところに、先ほどのVLOOKUP関数が入ります。

これでエラーが消えました。

では、キャラクターの所に、
「丸尾くん」と入れます。

ちゃんと作品名である「ちびまる子ちゃん」と
表示されました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

目白庭園の紅葉の写真です。

これで、めでたしめでたし・・・。
なのですが、この表に追加をする場合もありますよね。
灰色の所が追加分です。

こういう時は、表の範囲を1つ1ついじったり、
または、面倒だから100行ぐらい！！
=VLOOKUP(E2,A2:B100,2,FALSE)
なんてする方法もありますが、
そうなると、関数でExcelファイルが重くなるんです。

なので、加わった行数だけ自動で範囲を動かせるようにします。
まず、その為には、この表は何行分のデータがあるのかを数えるCOUNTA関数を使います。

上の表の右側のH列のオレンジのセルに式を入れます。
見ずらいので、ちょっと拡大しますね。

オレンジのセルの所に、

=COUNTA(A:A)

と式を入れました。
これで、A列の件数が数えられます。
表のタイトルの分があるので、
1を引いておきます。

=COUNTA(A:A)-1

これにより、表の範囲を7行分に拡張すれば良いです。これで、VLOOKUP関数の表の範囲の所にOFFSET関数を使うことが出来ます。
OFFSET関数の中で、COUNTAで求めた行数を使います。

OFFSET関数と組み合わせます。
=IFERROR(VLOOKUP(E2,A2:B5,2,FALSE),””)
の、表の範囲である、A2:B5を修正します。

=IFERROR(
VLOOKUP(
E2,
OFFSET(A2,0,0,COUNTA(A:A)-1,2)
,2,FALSE),””)

となります。

ちょっとややこしいな・・・と思われた方は、
OFFSET関数については、先に下記の記事を読んでおくと、
チョット分かりやすくなるかもしれません。
OFFSETとMATCH関数でドラゴンボールを探せ！！

この例でのOFFSET関数の中身、
OFFSET(A2 , 0 , 0 , COUNTA(A:A)-1 , 2)は、

OFFSET(基準のセル , 下に移動分 , 横に移動分 , 高さ , 幅)

です。

基準のセルから下に0、横に0移動します。
つまり、移動先はA2自身です。
高さがA2から7つ分の「COUNTA(A:A)-1」で、幅は2つ分が、
表の範囲になり、これを自動で可変できるようにしました。

では、追加した部分が表示されることを確認します。
E2の名前に「キルア」と入力してみます。

キルアの作品名がハンターハンターと表示されました。

では、更に表を追加して見ます。

これで、表の範囲の可変が出来ました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、多摩モノレールです。
多摩モノレールを立川南駅から柴崎体育館駅まで乗って、日野方面に南下して、立日橋から撮影しました。

ここまでご覧の読者様の中には、
「OFFSETとかCOUNTAとか、めんどくせぇ、もっと手軽な方法は無いの？」
と思われた方もいらっしゃるかも知れません。

もっと手っ取り早い方法は、表をテーブルに変換して、テーブル名をVLOOKUPの引数に設定してしまうという方法もございます。

まず、表内のどこでも良いので、[Ctrl] + [T]を押します。

先頭行を見出しにして、OKを押します。

テーブルに名前を付けられるので、今回は「anime_Table」としてみました。

それで式を

=IFERROR
(VLOOKUP
(E2,anime_Table,2,FALSE),””)

と修正します。

その状態で、テーブルにした範囲に新しいアニメキャラを追加します。
SPY×FAMILYのアーニャにしました。

テーブルでは隣接した一番下に入力すると、自動で範囲にされるので、

無事にアーニャの作品名に「SPY×FAMILY」が表示されました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

同じく、多摩モノレールです。

次に、この表では「名前」と「作品名」の２列だけですが、もっと列がある表の場合は、COLUMN(コラム)関数を指定すると便利です。

現在は、

=IFERROR
(VLOOKUP
(E2,anime_Table,2,FALSE),””)

となっておりますが、この２列目を指定している２にCOLUMN関数を使います。
実際にVLOOKUP関数の中に組み込む前に、COLUMN関数を単独で練習してみます。

F列の任意のセルに、

=COLUMN(F1)

と入力すると、このように「６」と言う結果になりました。

次に、D列に任意のセルに、

=COLUMN(D1)

は、「４」となりました。

この表は、E列から始まっていますよね。
それで、E列が1列目、F列が2列目にしたいので、その前のD列の「4」を引くと、

=COLUMN(F1)-COLUMN(D1)

で、「２」と求まりました。

この式を右に持っていくと、F列が２列目、G列が3列目、H列が4列目になるのですが・・・

このように、２、２、２・・・となってしまいました。

原因は、F列を行固定の複合参照にしていない、D列を行列固定の絶対参照にしていないからです。

そこで、

=COLUMN(F$1)-COLUMN($D$1)

と数式を変えました。
F1は[F4]キーを2回で、行固定の複合参照に、D1は[F4]キー１回の絶対参照にします。

それで、横に引っ張ると・・・

このように、２、３、４・・・となりました。

因みに、オートフィルオプションは、表以外の空白セルのところで、[Back Space] または、 [Space]で消えます。

これを、表の中で使うと、

=IFERROR
(VLOOKUP
(E2,anime_Table,
COLUMN(F$1)-COLUMN($D$1),
FALSE),””)

となりました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

代々木公園で撮影した薔薇です。

講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。

できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏

できるYouTuber式 Googleスプレッドシート現場の教科書
神川陽太氏＆長内孝平氏

皆さま、大変お疲れ様でした。
この記事最後のブレイクタイムPhotoは・・・

房総半島の外房、御宿の海です。

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

OFFSET関数とMATCH関数で、ドラゴンボールを探せ！！

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

良く小学生の頃、ドラゴンボールのキャラになりきっていました。
お察しの通り、私は大体これぐらいの年代です。

今日は、OFFSET関数とMATCH関数を使って、ドラゴンボールの「★」と「★★★」のボールを探して見ようと思います。

ドラゴンボールと言うのは、ボールを7個集めると願いが叶うという格闘少年漫画です。
オレンジ色のボールに、★が1つから7つまで入ったボールがあり、それらをコンプリートすると、願いが叶うという作品です。

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

●OFFSET関数
下の様な簡単なシートを用意します。
まず、基準は、表の左上隅にある、赤い色で塗りつぶしたセルA1の「おうち」です。
黄色いセルのある「天下一武道会会場」のセルの下で、
オレンジのセルE4の「ドラゴンボール★」を呼び出します。

最初に数式の意味をお伝えすると、

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

という意味です。
おうちのあるセルA1を基準にして、下へ3歩、右へ4歩移動しました。
では、実際に式を入れていきます。

=OFFSET(A1,3,4)

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

では次は、「ドラゴンボール★★★」を呼び出します。
今度は、10歩下に行って1歩右に行きますので、

=OFFSET(A1,10,1)

と指定します。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、代々木公園で撮影した紫陽花です。

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

上の表から、「ベジータがいる行」と、「ブルマがいる列」を表示します。

これは、MATCH関数を理解する時に、ぶっちゃけ、

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

・・・みたいな関数って言っちゃって良いのでは、なんて思いました（笑）
フィーリングはどんな勉強においても結構大事だったり。

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

=MATCH
(ベジータ,行の名前の範囲,完全一致)

という意味の、

=MATCH(H3,B3:B8,0)

と入力します。

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

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

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

=MATCH
(ブルマ,列の名前の範囲,完全一致)

という意味である、

=MATCH(I2,C2:F2,0)

と式を入れました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、代々木公園の薔薇です。

●OFFSETとMATCHの合わせ技
ベジータとブルマの間には、トランクスという子どもがいます。
迷子になったトランクスを探します。
表を下記の様に準備します。

オレンジのセルに、

=OFFSET
( おうち , ベジータ行 , ブルマ列 )

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

ベジータ行とブルマ列は、先ほどのMATCH関数を用います。

=OFFSET(おうち,MATCH(ベジータ,行範囲,0),MATCH(ブルマ,列範囲,0))

具体的なセルを数式に入れると、
おうちの所に、B2が入り、
「ベジータ行」の所にMATCH関数で求めた4行下、
「ブルマ列」の所にMATCH関数で求めた3列右が入りました。

=OFFSET(B2,MATCH(H3,B3:B8,0),MATCH(I2,C2:F2,0))

このような式を入れます。

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

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

ExcelはMOSの2016Expertまで取得していますが、
その範囲外の割には仕事で使うので、私も頑張って覚えます。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

近くで撮影した、ガーデンローズです。

講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。

できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏

できるYouTuber式 Googleスプレッドシート現場の教科書
神川陽太氏＆長内孝平氏

皆さま、大変お疲れ様でした。
この記事最後のブレイクタイムPhotoは・・・

スカイツリーと河津桜です。
葉桜って時々、撮り方によってはとても綺麗だと思います。

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

Excelの入力候補を表示させない（オートコンプリート機能をOFF）

Excelでデータの入力をしていて、
「こらーーーーー！！　うざいんじゃ！！！」
と思う機能はございませんか？

例えば、「新大久保」と入力した後に、
「新宿」と入力したいとします。
しかし・・・

この機能、オートコンプリートって言うんですけど、
うざくないですか。
私は「新宿」って入力したかったんですよ。
はい、都庁がある所です。

このオートコンプリート機能を使わないように
設定します。

WindowsとMacで操作方法が異なるので、両方書かせて頂きます。

●【Windows】の場合
「ファイル」タブから「オプション」を選んでください。
すると、こういうのが出てきたので、

「詳細設定」を選びます。

その後、「編集オプション」から、「オートコンプリート」の
チェックを外します。

これで、煩わしい機能が解除されました。

●【Mac】の場合
macだと、手順が少し異なります。

りんごマークと、ファイルの間の「Excel」をクリックします。

更に、「環境設定」をクリック

次に、オートコンプリートをクリック

ここのチェックを外します。

あとは赤い所で閉じます。

私もWindowsからMacに買い換えて、2年が経とうとするのに、いまだに不慣れな所があったりします・・・。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

新宿中央公園の噴水と花です。

講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。

できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏

できるYouTuber式 Googleスプレッドシート現場の教科書
神川陽太氏＆長内孝平氏

皆さま、大変お疲れ様でした。
この記事最後のブレイクタイムPhotoは・・・

2019年4月撮影の西新宿です。

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

スライサーとテーブルで東京の観光地巡り

この記事では、Excelで東京都のオススメ観光スポットを例に、テーブルの作り方やスライサーの使い方が勉強出来ます。

こんな感じの表を用意します。

「地味です( ；∀；)」って感じの表ですね。

これを管理しやすくするために、
テーブルに変換します。

表内のデータがある所でしたらどこでも良いので、任意のセルを選択肢て、

[Ctrl] + [T]

を押します。

先頭行をタイトルにするので、チェックを入れて、
[Enter]を押します。

こんなふうになりました。
デザインは、「テーブルデザイン」の「デザイン」の、
テーブルスタイルから好きなモノを選びます。

ここ、クリックすると、色々出て来ます。
勿論、デザインを適用しないで、左上の「なし」にして、
好きな色を付けることも可能です。

テーブルを使うことのメリットは、フィルター機能が付いてることと、データをドンドン追加するにつれ、テーブルの範囲が自動で拡張出来ることです。
また、データを入力する時に表の範囲の中のみを移動するので、入力が容易になります。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、立川市の国営昭和記念公園で撮影したひまわりです。

次に行きます。

テーブルには、データを絞り込む時に視覚的に分かりやすい、スライサーを組み合わせることも可能です。
デザインタブをクリックして、スライサーの挿入を選びます。

チェックを入れて、Enterを押すと、
フィールド（列ごと）のスライサーが挿入されます。

好きな場所に移動します。
これもデザインを選べます。

エリアから、例えば「下町」を選びます。

行が絞られましたね。

下町のスポットが強調されます。

ただ、薄くですが、それ以外のスポットも
表示されてしまっているので、
それを非表示にさせたいと思います。

おススメスポットのスライサーを選択して、
スライサーツールの「オプション」を選びます。

スライサーの表示設定を選びます。

ここの手順、Macの場合は、こちらをご参照下さい。

Windowsに話を戻します。

ここの、チェックを入れて、Enterを押します。

すると、ここの所が非表示になって、
下町エリアだけが表示されました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、スカイツリーとすみだ水族館です。

講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。

できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏

できるYouTuber式 Googleスプレッドシート現場の教科書
神川陽太氏＆長内孝平氏

皆さま、大変お疲れ様でした。
この記事最後のブレイクタイムPhotoは・・・

よみうりランドのクリスマスイルミネーション、ジュエルミネーションの噴水です。

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

IF関数とVLOOKUP関数でソーシャルディスタンス！

本日はExcel関数の中でも特に使用頻度の高い、IF関数とVLOOKUP関数の合わせ技を掲載します。

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

また、VLOOKUP関数で対応するメッセージを図形描画した吹き出し図形の中にも表示させる方法も掲載致しました。

まず、E列、F列にこの様に入力します。
E4の所に、「前の人との距離」と入れて、他も用意しました。

E７に「メートル」、F7に「セリフ」と入力します。
肌色のセルのE8〜E10には、それぞれ「1 , 2 , 3」と数字を入力します。（中央揃えをしています。）
その隣のF8〜F10にそれぞれのセリフを入れます。

ここまで準備が出来たら、E5にリストを設定します。

まず、E5にプルダウンメニューを作ります。
データタブ → データの入力規則 → 設定
で、入力値の種類を「リスト」にします。

このリストの範囲として、
メートルの下の肌色のセルのある、E8〜E10をドラッグしてOKを押します。

これで、Windowsの方は、「Alt」+「↓」、Macの方は「option」 + 「↓」で、リストが選択出来るようになり、前の人との距離のメートルが1m,2m,3mを選択出来ます。

リストを作ったセルの右隣の黄色いセル、F5に、

=IF
(E5が空欄だったら,空欄を返して,
そうでなければVLOOKUP
（前の人との距離,F8〜F10のセリフ範囲,2列目,完全一致のFALSE))

という意味になる、

=IF(E5=””,””,
VLOOKUP
(E5,$E$8:$F$10,2,FALSE))

と、式を入れます。
これは、距離（メートル）、１，２，３に対して、対応するセリフを返す処理をしています。

今回は絶対参照にしなくても良いのですが、実務では表の範囲は固定しないと、他のセルに式をコピーした時に範囲がずれてしまうので、ここでもE8:F10に[F4]を押して$マークを付けて絶対参照にしています。

これで、距離に応じたメッセージが表示されました。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、目白庭園という日本庭園で、紅葉のライトアップの時に撮影したものです。

最後に、挿入メニューから図形描画で好きな図形を挿入して、その図形の中にVLOOKUP関数のメッセージを表示させます。
私は吹き出しを描画しました。

図形描画のショートカットキーは、
[Alt] → [N] → [S] → [H] が便利です。

ここで、描画した図形をクリックした状態で、「＝」とイコールを押して、
黄色いセルのF5をクリックしてEnterを押すと、

この様に、吹き出しの中にメッセージが表示される様になりました。

リストで選択する数字を変えると、吹き出しの中のセリフも変わります。

お疲れ様でした。
ここで一旦、写真で休憩を挟みます。

写真は、地元東京都足立区の桜です。

講師の長内氏も神川氏も私よりもだいぶ若い方ですので、私にとってはまさに中高年の学び直しという感じですね。

できるYouTuber式 Excel現場の教科書
ビジネス教育系ユーチューバー長内孝平氏

できるYouTuber式 Googleスプレッドシート現場の教科書
神川陽太氏＆長内孝平氏

皆さま、大変お疲れ様でした。
この記事最後のブレイクタイムPhotoは・・・

立川市の昭和記念公園の、サンフィニティという小ぶりの向日葵の写真です。

最後までご精読、誠にありがとうございました！！

最後までお付き合い頂き、誠にありがとうございました！！

COUNTIF関数を使って、30歳以上のジャニーズに人数を数える

集計するのに便利な関数、COUNTIF関数を使って、30歳以上にジャニーズ（又は、元ジャニーズ）のタレントさんの人数を数えます。

また、書式を整えて、年齢や日付を見やすくします。

名前順に、井上瑞稀さん（HiHi Jets）・伊野尾慧さん（Hey! Say! JUMP）・浮所飛貴さん（美少年）・大西流星さん（なにわ男子）・岡田准一さん（元V6 / Coming Century）・平野紫耀さん（King & Prince）・松本潤さん（嵐）の名前とグループ名と本日（2023/05/17現在）での年齢を表にしました。