8月, 2020 | ねこ先生がプログラミングをガリ勉してみた(=^x^=)♪

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

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

・VLOOKUP関数
・IFERROR関数
・OFFSET関数
・COUNTA関数
・テーブル機能

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

では、国民的アニメを題材としたVLOOKUP関数の説明に入ります。
この様な簡易的な表を作成致します。

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

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

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

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

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

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

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

と式を入れます。

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

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

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

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

式の意味は、

=IFERROR(OKな値 , エラーメッセージなど)

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

具体的には、

=IFERROR(VLOOKUP(D2,A2:B5,2,FALSE),”左のセルにキャラ名を入力して下さい。”)

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

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

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

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

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

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

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

まず、その為には、この表は何行分のデータがあるのかを数えるCOUNTA関数を使います。

D列のオレンジのセルに式を入れます。
オレンジのセルの所に、

=COUNTA(A:A)

と式を入れました。
これで、A列の件数が数えられます。

表のタイトルの分があるので、1を引いておきます。

=COUNTA(A:A)-1

これにより、キャラクターの「のび太」〜「みぎわさん」まで「7行」であることが求まりました。

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

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

OFFSET関数と組み合わせます。

=IFERROR(VLOOKUP(D2,A2:B5,2,FALSE),”左のセルにキャラ名を入力して下さい。”)

の、表の範囲である、A2:B5を修正します。

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

=IFERROR(
VLOOKUP(
D2,
OFFSET(A2,0,0,COUNTA(A:A)-1,2),2,FALSE),”左のセルにキャラ名を入力して下さい。”)

となります。

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

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

です。

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

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

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

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

では、更に表を追加して、追加した「いくらちゃん」を検索値にしてみます。
作品名が「サザエさん」と表示され、COUNTAも1行増えて8行になりました。

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

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

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

ここまでご覧の読者様の中には、
「OFFSET関数やCOUNTA関数が少々面倒ですので、もっと手軽な方法は無いの？」
と思われた方もいらっしゃるかも知れません。

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

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

先頭行を見出しにして、OKを押します。
テーブル内を選択して、「テーブルタブ」を選択します。

この状態で、左側のテーブル1の所に、このテーブルの名前を付けます。

このテーブルに付ける名前は「anime_Table」としてみました。

それでセルE2のVLOOKUP関数の第2引数の式を

=IFERROR
(VLOOKUP
(D2,anime_Table,2,FALSE)
,”左のセルにキャラ名を入力して下さい。”)

と修正します。

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

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

無事にアーニャの作品名に「SPY×FAMILY」が表示されました。
また、COUNTAの所も9行になりました。

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

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

ここで、このanime_Tableが見づらい配色なので調整します。
テーブル内の範囲を選択して、「テーブルタブ」をクリックして、なるべく「淡色」から好きな色を選びます。

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

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

■ Excel / スプレッドシートの学習にオススメ本の紹介 ■

「できるYouTuber式 Excel現場の教科書（長内孝平氏）」
単行本（ソフトカバー）
Kindle版

「できるYouTuber式 Googleスプレッドシート現場の教科書」
（神川陽太氏＆長内孝平氏）
単行本（ソフトカバー）
Kindle版

「なんの才能もない発達障害者の声: 〜届かない声を届けたい〜 (障害者雇用向け)」
私、猫★シエスタの著作です。
殆どが「発達障害者として働くことの悩み」の内容ですが、Excelを含む障害者雇用の事務作業に便利なショートカットキーについても少しだけ掲載しております。
Kindle版

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

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

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

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

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

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

フォトストック写真ACさんのプロフィールページ

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

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関数と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)

と指定します。

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

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

●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といったベタ打ちは殆ど使わないのです。

そこで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に迷子になったトランクスを探します。

オレンジのセルに、

=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^=)！

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

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

■ Excel / スプレッドシートの学習にオススメ本の紹介 ■

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

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

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

フォトストック写真ACさんのプロフィールページ

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