月: 2020年8月

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

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

・VLOOKUP
・IFERROR
・OFFSET
・COUNTA
・COLUMN

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

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

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

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

黄色いセルに
=VLOOKUP(E2,A2:B5,2,FALSE)
と式を入れます。

式の意味は、
=VLOOKUP(検索値 , 表の範囲 , 2列目 , 一致)
です。
もっと詳しく書くと、
=VLOOKUP(ワカメなどのキャラ名 , 表の範囲 , 2列目の作品名 , 完全に一致)

では、別のキャラクターを入れて見ます。
ワカメちゃんの所を丸尾くんにしたいので、
一旦ワカメちゃんを消します。

エラーが出ちゃった。
そんな時は、

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

と、式を修正します。
式の意味は、
=IFERROR(OKな値 , エラーの値)
です。

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

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

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

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

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

オレンジのセルに
=COUNTA(A:A)
と式を入れました。
これで、A列の件数が数えられます。
表のタイトルの分があるので、
1を引いておきます。

=COUNTA(A:A)-1

これにより、表の範囲を7行分に拡張すれば良いです。

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

ちょっとこの説明分かりづらいと思われた方は、
OFFSETとMATCH関数でドラゴンボールを探せ！！
をご覧下さい。

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

キルアの作品名がハンターハンターと表示されました。
では、更に表を追加して見ます。

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

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

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

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


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

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

それで式を

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

と修正します。

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

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

このように、VLOOKUP関数でも適用致します。

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

現在は、

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

となっておりますが、この２列目を指定している２に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),””)

となりました。
お疲れ様でした。

ブレイクタイムPhotoは、

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

メインメニューへ
取りあえず事務職で働きたいから手っ取り早くExcel教えてくれへ
プログラミングと資格
これからプログラミングをはじめる方へ
息抜きに、写真で癒し(=^・^=)
自己紹介

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でクール、クーラー、クーリッシュな暗号

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

＜＜関連記事＞＞

基本情報技術者試験トップへ
アセンブラ自作サンプルへ
アセンブラ過去問プログラムへ
令和2年（令和3年1月）合格報告

少し前に竹内涼真さんが出演されているアイスの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
入門が卒業出来たら、ガシガシ例題解いて、
演習問題を解いて、力を付けたいという時に
読む本です。

＜＜関連記事＞＞

基本情報技術者試験トップへ
アセンブラ自作サンプルへ
アセンブラ過去問プログラムへ
令和2年（令和3年1月）合格報告

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

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

良く小学生の頃、ドラゴンボールのキャラになりきっていました。

当時、放送時間が塾の授業中でした為、VHSのビデオを使って録画して塾に行って、ドラゴンボールを見ることを塾帰りのご褒美にしておりました。

しかし、塾から家に帰って来た所、ドラゴンボールが録画出来ていなくて、
母親が掃除機をかけた時に電源を抜いていたと聞いて、

「塾での勉強頑張ったご褒美にドラゴンボール見ようと思っていたのにー( ；∀；)」

と泣きじゃくっていた幼い日を思い出します。

ドラゴンボールと言うのは、ボールを7個集めると願いが叶うという格闘少年漫画です。
今日はOFFSET関数とMATCH関数を使って、ドラゴンボールの「★」と「★★★」を探して見ようと思います。

●OFFSET関数
まず、基準は赤い色で塗りつぶしたセルA1の「おうち」です。
黄色いセルのある「天下一武道会会場」のセルの下で、
「ドラゴンボール★」を呼び出します。

ここに下記の式を入れます。
=OFFSET(A1,3,4)
すると、ドラゴンボール★が呼び出されました。

この式の意味は、
=OFFSET(おうち , 3行下 , 4列右)という意味です。
おうちのあるセルA1を基準にして、下へ3歩、右へ4歩移動しました。

では次は、「ドラゴンボール★★★」を呼び出します。
=OFFSET(A1,10,1)にします。

●MATCH関数
では、次はOFFSET関数とMATCH関数の合わせ技の前に、
MATCH関数を見てみます。

「ベジータがいる行」と、「ブルマがいる列」を表示します。
これは、「縦の糸はあなた」、「横の糸は私」・・・みたいなものですが（笑）

最初にベジータの行を求めます。ベジータの下の黄色いセルに、
=MATCH(H3,B3:B8,0)
と入力します。

この式の意味は、H3の「ベジータ」を検索値として、
B3からB8のキャラクターの範囲を調べ、
4番目であることの結果が返って来ました。
最後の0は、ピッタリ一致するという意味です。
近似一致といって、最も近い値を求めることも出来ますが、
今回はベジータがいる行を求めたいので、ぴったり一致の0にしました。

次にブルマの列を調べます。
ブルマの右側のセルに式を入れます。
=MATCH(I2,C2:F2,0)
と式を入れました。

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

=OFFSET(B2,MATCH(H3,B3:B8,0),MATCH(I2,C2:F2,0))
という式を入れます。

長ったらしいので、これをもう少し分かりやすく書きます。
=OFFSET( おうち , ベジータ行 , ブルマ列 )
になります。
おうちの所に、B2が入り、
「ベジータ行」の所にMATCH関数で求めた4行下、
「ブルマ列」の所にMATCH関数で求めた3列右が入りました。

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

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

お疲れ様でした。
ブレイクタイムPhotoは、スカイツリーと河津桜です。

メインメニューへ
取りあえず事務職で働きたいから手っ取り早くExcel教えてくれへ
プログラミングと資格
これからプログラミングをはじめる方へ
息抜きに、写真で癒し(=^・^=)
自己紹介