Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい【furigana】

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい

＜VLOOKUP関数・PHONETIC+INDEX+MATCH関数＞

氏名からフリガナを抽出したい時には、PHONETIC関数をつかうことで、表示できます。

ところが、PHONETIC関数は、文字を入力した時のデータを表示するため、VLOOKUP関数やセル参照などで、間接的に表示されているデータからフリガナ情報を表示することができません。

では、どのようにしたら、次のように、フリガナを表示することができるのでしょうか。

B2には、VLOOKUP関数の数式を設定してあります。

=VLOOKUP(A2,A5:F14,2,FALSE)

C2に、PHONETIC関数をつかった数式を設定してみても、何も表示されません。

C2に、

=PHONETIC(B2)

と設定していますが、表示されないわけですね。

解決するポイントは、文字入力が入っている元のデータをつかえばいいわけです。

では、次のようにC2の数式を修正していきます。

=PHONETIC(INDEX(B5:B14,MATCH(B2,B5:B14,0)))

これで、フリガナを表示することができるようになります。

それでは、この数式を説明していきます。

最初のPHONETIC関数は、「フリガナ」情報を表示することができる関数ですね。

INDEX関数で指定した行列番号が交差するセル参照する関数ですね。

そして、INDEX関数と相性抜群のMATCH関数を組み合わせます。

MATCH(B2,B5:B14,0)

と数式を設定していますが、B2の値は、B5:B14のなかで、何番目にあるのかを算出することができます。

MATCH関数は、範囲内にある検索値の位置を算出する関数です。

このように、INDEX関数とMATCH関数を合わせることで、元にあるフリガナ情報を抽出することができます。

INDEX＋MATCH関数は、覚えておくといい、関数の組み合わせかもしれませんね。

Excel。COSH関数をつかうと数値の双曲線余弦を算出できます。【COSH】

Excel。COSH関数をつかうと数値の双曲線余弦を算出できます。

＜関数辞典：COSH関数＞

COSH関数

読み方： ハイパーポリック コサイン　　

分類： 数学/三角 

COSH(数値)

数値の双曲線余弦を算出します　

Excel。桁数が異なっていても文字結合で「0（ゼロ）」をいれて桁を揃え番号をつくりたい【alignment】

Excel。桁数が異なっていても文字結合で「0（ゼロ）」をいれて桁を揃え番号をつくりたい

＜TEXT関数＞

CONCAT関数や「&（アンパサンド）」をつかうことで文字結合をおこなうことができます。

頭文字と数値を結合する場合、数値の桁数が異なると、当然、文字数が揃いません。

では、どのようにしたらいいのでしょうか？

そこで、TEXT関数と「&（アンパサンド）」を組み合わせることで、解決することができます。

B2には次のように数式を設定しました。

="ABC"&TEXT(A2,"0000")

あとは、オートフィルで数式をコピーしています。

TEXT関数は表示形式を設定することができる関数です。

ゼロ付数値を表示することができるのが、「”0000”」という表示形式です。

桁が1桁ならば、4桁にそろえるので「0001」と表示されます。

これに、頭文字のコードをつけることで、文字列と数値の間を「0（ゼロ）」で揃えて埋めることができるというわけです。

Excel。条件付き書式で平均値以上のデータがある行全体を塗りつぶしたい。【above average】

Excel。条件付き書式で平均値以上のデータがある行全体を塗りつぶしたい。

＜条件付き書式＋AVERAGE関数＞

Excelの条件付き書式は便利ですね。

さらに、ちょっとしたアレンジをすることで、色々対応することができます。

例えば、次のようにTotal値が平均以上だったら行全体を塗りつぶすとしたらどうしたらいいのでしょうか。

条件付き書式で用意されているものでは、次の2つが対応できません。

1つ目は、平均以上は用意されていない

上位/下位ルールには「平均より上」はありますが、「平均以上」は無いことがわかります。

2つ目は、セルが対象になっていて、行全体を塗りつぶす設定はない

そのため、数式を使った条件を設定する必要があるわけです。

それでは、設定していきます。

設定するA2:F11を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

ルールの種類から「数式を使用して、書式設定するセルを決定」を選択します。

次の数式を満たす場合に値を書式設定のボックスに次の数式を設定します。

=$F2>=average($F$2:$F$11)

あとは、書式ボタンをクリックして、塗りつぶしを設定したら、OKボタンをクリックして完成です。

それでは、設定した数式を確認しておきましょう。

「$F2」は、列を固定した複合参照にすることで、該当データのセルが含まれる行全体を書式設定の対象にすることができます。

「>=average($F$2:$F$11)」は、平均を算出するお馴染みのAVERAGE関数ですね。

ちなみに、今回のTotalの平均値は、163.7なので、塗りつぶしされている行は、きちんと対応していることがわかります。

最終行に、集計行を表示したくない場合には、このような方法をつかうことで、平均以上の場合に行全体を塗りつぶすことができます。

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。

＜Excel関数辞典：VOL.73＞

今回は、SLN関数～SORTBY関数までをご紹介しております。

SLN関数

読み方： エスエルエヌ

読み方： ストレートライン

分類： 財務 

SLN(取得価額,残存価額,耐用年数)

減価償却費を定額表で算出します　

Straight LiNe depreciationの略

SLOPE関数

読み方： スロープ　　

分類： 統計 

SLOPE(既知のy,既知のx)

回帰直線の傾きを算出する　

SMALL関数

読み方： スモール　　

分類： 統計 

SMALL(配列,順位)

指定した○番目に小さい値を算出します　

SORT関数

読み方： ソート　　

分類： 検索/行列 

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

範囲または配列を並べ替えます　

SORTBY関数

読み方： ソートバイ　　

分類： 検索/行列 

SORTBY(配列,基準配列1,[並べ替え順序1],…)

範囲または配列を、対応する範囲または配列の値に基づいて並べ替えます　

Excel。10進数を2進数に変換するDEC2BIN関数はありますが、どうなんだろう。【Decimal number】

Excel。10進数を2進数に変換するDEC2BIN関数はありますが、どうなんだろう。

＜DEC2BIN関数＞

ネットワーク関係の方ならお馴染みの「2進数」。

10進数を2進数に変換するという作業を行うときに、Excelでも一応関数をつかって変換することはできるのですが、ちょっと、イマイチ。

次の表で説明します。

B2には、次の数式を設定してあります。

=DEC2BIN(A2,10)

オートフィルで数式をコピーしています。

B3をみると、17を2進数に変換してくれているのですが、「オクテット」ごとに空白がはいらないので、可読性が悪いですね。

数値が大きくなったら大変です。

ところが、「数値が大きく」とはいっても、最大「511」までしか変換してくれません。

B5をみると、#NUM!というエラーが表示されています。

一応負数でもリアクションしますが、「-512～511」の範囲でしか対応しておりません。

確かに、関数を使うことで、対応することはできますが、単純に、10進数を2進数に変換するだけならば、関数電卓があるアプリをつかってしまうほうが、いいように思えます。

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの【every other row】

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの

＜SUMPRODUCT+MOD+COLUMN関数＞

Excelは基本的に上から下へ流れていくテーブル（表）でつくれば、様々なExcelの機能を使うことができます。

ただ、どうしても帳票と同じように表をつくってしまうと、簡単に算出できない場合があります。

例えば、次の表。

来店客数と売上高が一組になったデータが列方向に拡張されている表。

このような表、帳票としてはいいのですが、単純に、来店客数の合計や、売上高の合計を算出する場合、1列おきで範囲を設定する必要があります。

要するに、列が増えれば増えるほど、面倒な作業というわけです。

そこで、来店客数の合計B8には次の数式を設定することで、手早く算出することができます。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=0)*$B$3:$G$5)

また、売上高の合計C8には、次の数式を設定してあります。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=1)*$B$3:$G$5)

数式の引数を確認しましょう。

最初のSUMPRODUCT関数ですが、SUMは、和算。

PRODUCTは乗算で、乗算した結果を和算する関数です。

そして引数のMOD関数とCOLUMN関数は何をやっているのかというと、1列おきで範囲選択したいわけです。

1列おきということは、列番号をつかって、偶数か奇数なのかを判定させればいいわけです。

MOD関数は、除算した、あまりを算出する関数です。

また、COLUMN関数は列番号を算出する関数です。

よって「MOD(COLUMN($B$3:$G$5),2)」で、列番号を2で除算するという数式ですから、結果「0」だったら余りが0ということで、偶数列ということがわかります。

「MOD(COLUMN($B$3:$G$5),2)=0」と「=0」とすれば、「MOD関数の結果が0と等しいか」と判断させています。

「等しい」ならば「TRUE」、「等しくない」ならば「FALSE」と判定されます。

「TRUE=1」で「FALSE=0」とExcelでは定義されていますから、「MOD(COLUMN($B$3:$G$5),2)=0」が成立しているならば、偶数列は「1」。

奇数列は「0」と算出されるわけです。

ここで、SUMPRODUCT関数の出番。

「*$B$3:$G$5」と乗算していますが、偶数の「1」を掛ければ、その値は残り、奇数の「0」を掛ければ、「0」となるわけです。

その結果を和算すれば、偶数列のみの合計値を算出できるというわけです。

1列おきとか1行おきとかで、合計を算出したい場合にはSUMPRODUCT関数をつかってみるといいかもしれませんね。