8月, 2020 | 働きたいロスジェネ「負け組」・発達障害者向け、PC技術習得等の情報発信局

国民的アニメのキャラクターを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),””)

となりました。

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

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

・
・
・
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は・・・

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

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

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

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

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

Javaで芸能人のBMIを調べてみた

Java自作サンプルに戻る
 これからプログラミングを始める方へ
 プログラミングと資格
 息抜きに、写真で癒し(=^・^=)

関連記事：C言語で芸能人のBMIを調べてみた

令和2年夏、お盆初日にぎっくり腰になってしまい、
もう少し痩せた方が良いらしいので、
BMIを計算するプログラムを作ってみました。
BMIの計算方法は、BMIと適性体重 で調べました。
このプログラムのフローチャートはこちらです。

男性芸能人3名の方のBMIについて調べました。

●ホンジャマカの石塚英彦さん
ニックネーム：石ちゃん
身長175.0cm、体重130kg

石ちゃんは、サカゼンのイメージキャラクターですね。
良く電車で広告を見かけます。

それでは、プログラムを実行して見ましょう。
まずお名前を入力。

それから身長、体重を入力。

結果です。

石ちゃん、本当に、腰痛とかぎっくり腰には
気を付けて下さい。
心配です・・・。

●元NEWSの山下智久さん
ニックネーム：山P
身長174.0cm、体重62.0kg
山Pは筋肉質なので、見た目より重めです。

結果です。
完璧王子らしいです。

●V6の森田剛さん
ニックネーム：森田君？剛くん？
身長163.0cm、体重45.0kg

結果です。
小柄で身軽なイメージがあります。

では、プログラムになります。

import java.util.Scanner;
public class BMI{

public static void main(String[] args){
Scanner scn=new Scanner(System.in);
double high,weight,h2,bmi;
String yourname,msg;

System.out.print(“あなたのお名前は？= “);
yourname=scn.next();
System.out.print(yourname+”さんの身長は？（例189.4）= “);
high=scn.nextDouble();
high/=100;
System.out.print(yourname+”さんの体重は（例65.8）= “);
weight=scn.nextDouble();
h2=Math.pow(high,2);
bmi=weight/h2;

System.out.printf(yourname+”さんのBMIは、%.1fです。”,bmi);
if(bmi<18.5){
msg=”いっぱいご飯を食べようね(=^・^=)”;
}

else if(bmi<25.0){
msg=”その調子(^^♪”;
}

else{
msg=”脱！めたぼっち( ；∀；) 腰痛に気を付けてねｍ(=^・^=)ｍ”;
}

System.out.println(msg);
}
}

皆さま、BMIに限らず、腰痛にはお気を付けて…。

ここまで読んで下さって、誠にありがとうございました。
もしも、この記事がちょっとでもお役に立てていましたら、
お手数ですが、あなたのSNSやブログで広めて頂けたら嬉しいです。

このプログラムを作るにあたって、勉強になった本を紹介します。

関連記事：C言語で芸能人のBMIを調べてみた

Java自作サンプルに戻る
 これからプログラミングを始める方へ
 プログラミングと資格
 息抜きに、写真で癒し(=^・^=)

アセンブラCASL2でクール、クーラー、クーリッシュな暗号

この記事では、アセンブラ言語の「配列や文字列の扱い」について簡単な例題で学べます。配列の添字と文字を対応づけている、簡易的な暗号文です。

少し前に竹内涼真さんが出演されているアイスのCMを
見ました。
「クール、クーラー、クーリッシュ」っていうアレです。
TVのCMは、聴いているうちに、耳に残る上、
あのアイスの広告が、通勤電車にもあったので、
時々、「クール、クーラー、クーリッシュ」と、
独り言を言ってしまいます。

そこで、アイスをCOMET2のアセンブラ、CASL2で作ろうと思いました。
この様な配列があります。

0番目はA、1番目はB、2番目はC…という風に
暗号を解読するような感じで、数値を文字に当てはめていきます。
例えば、ICEの「I」は8、「C」は2、「E」は4になります。

なので、最初は「8」を入力します。

すると、「8」は文字コードで「38」なので、
レジスタに38と入ります。

GR2に38と入りました。
これを8にする為には、
文字コード30の「0」を使って減算します。

GR2が8になりました。

今度はこの8をアイスの「I」に変換します。
配列MOJIの先頭アドレスをGR3に入れます。

GR3には配列MOJIの先頭アドレスが
ここでは1045と入りました。

このアドレスを8加算（先頭アドレスから8だけ右に移動）すると、
「I」のアドレスになります。

アドレス1045に8を加えて104Dになりました。

レジスタGR4にIを納めます。
Iの文字コードは49です。

この「I」を、出力用の配列DASUの先頭アドレスに格納します。

格納済み文字数をGR1に設定します。

この文字数が3になったらループを抜けて出力します。

同様に、ICEの残りの文字「C」は2、「E」は、4を入力して、
格納します。

出力結果です。
暗号文みたいなプログラムを作ってみたいのと、
「クール、クーラー、クーリッシュ」がループしていたので、
これを作ってみました。

全然汎用性がないのですが、
アイスの売り上げに貢献出来るかも？？

以下、プログラムになります。

TEST START
RPUSH

LAD GR1,0 ;出力用配列に格納する場所と格納文字数

LOOP IN KEY,LEN ;文字に変換する数字入力用
LD GR2,KEY
SUBA GR2,=’0′ ;文字から数値に変換
LAD GR3,MOJI
ADDA GR3,GR2 ;変換した数値分右に移動
LD GR4,0,GR3
ST GR4,DASU,GR1
LAD GR1,1,GR1
CPA GR1,=3
JZE FIN
JUMP LOOP

FIN
OUT DASU,LEN2

RPOP
RET

KEY DS 1
LEN DC 1
MOJI DC ‘ABCDEFGHIJ’
DASU DS 3
LEN2 DC 3

END

読んで下さってありがとうございました。

この記事を書くのに勉強になった本を紹介します。
多分この記事を読んで面白かったと思われた方は、きっとハマると思います。

◆アルゴリズム問題がちゃんと解ける本
アルゴリズム学習の定番。
アルゴリズムが苦手で何とかしたい方におススメ

◆アルゴリズムはじめの一歩完全攻略
実際に作って学べます。
Javaを使っています。
私はJava初めてでしたが、
それでも、ハッシュ関数の所までは、
どうにかついていけました。
もっと頑張ります。

◆速習言語CASL2
CASL2、何それ、テーマパークの絶叫マシーン
みたいな名前だね、ぐらいだった私でも、入門書として楽しく読めた本です。

◆プログラミング入門CASL2
入門が卒業出来たら、ガシガシ例題解いて、
演習問題を解いて、力を付けたいという時に
読む本です。

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まで取得していますが、
その範囲外の割には仕事で使うので、私も頑張って覚えます。

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

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

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

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

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

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

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

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

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

月	火	水	木	金	土	日
					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