カテゴリー: Utility

リストから検索値に相当するデータを抽出する方法について

Excelでの文字の検索

 
Excelで、VLOOKUP関数では基準となる検索値の列がリストの左側にある必要があるが、リストを修正せずに必要なデータを抽出する方法についてのメモ。

INDEX関数とMATCH関数

 
index-match_01
 
上図のようにA-C列にあるリストから、検索値(E2セル)をB列から検索してF列に抽出したデータを返す場合、INDEX関数＋MATCH関数の組み合わせで入力する。

  • =INDEX(範囲,行(縦)位置,列(横)位置)
  • =MATCH(検索値,範囲,完全一致(0)か近似値(1 or -1))

INDEX関数の「列」は省略でき、MATCHの照合の型(「1」は検査値以下の最大の値、「-1」は検査値以上の最小の値、「0」は完全一致)が省略された場合は、「1」を指定した場合と同じになる。
これらの組み合わせにより、リストから検索値に相当するデータを抽出する場合は、

=index([範囲],match([検索値],[範囲],0)]

 
とすればよい。

 
スポンサーリンク





FORMULATEXTについて

FORMULATEXT関数について

 
Excelではセルに数式を入力すると計算結果が表示されるが、”解”を表示したセルを参照してその数式を表示することができないかと調べてみたところ、EXCEL2013では、『FORMULATEXT関数』というのがあるようだ。職場では、EXCEL2010を使用しているのでこの関数を使用することはできないのだが、VBAのFunctionプロシージャで同様のユーザー定義関数のようなものを登録してみると、意外と使い勝手が良かったので簡単にメモ。
 

VBAへの登録

 
Functionプロシージャは以下のように登録する。

Function プロシージャ名(引数 As データ型) As 戻り値のデータ型
プロシージャ名 = 戻り値
End Function

 
VBAで数式を取得するのにはFormulaプロパティ(.formula)を使えばよいので、標準モジュールに以下のマクロを記載。

Function formulatext(n As Range) As String
formulatext = n.Formula
End Function

 

サンプル

 
formulatext_01
 
スポンサーリンク





周期表について(2)

周期表

 
113番目の元素の名称(「ニホニウム」)が公表された(日本経済新聞のニュース)。以前メモした元素と原子量の一覧に、「Nh」を入れた周期表を作成。
 
periodic_01
 
原子量は、以前と同じ文部科学省のホームページにある周期表のポスターのものとした。【参考：作成した周期表のExcel版】
 
スポンサーリンク





特定の文字の検索について

Excelでの文字の検索

 
文字を検索する際は、FindのFが覚えやすいこともあり、WordでもExcelでも「Ctrl+F」から「検索ウィンドウ」を表示するが、特にExcelで任意のセルに入力した文字を検索する方法についてのメモ。

COUNTIF関数とワイルドカード

 
find_01
 
上図のようにA列にデータが入力されているとき、キーワード(D1セル)で”始まる”、”含む”、”終わる”データの数を検索する場合は、ワイルドカード(*)を用いて関数を入力する。

  • キーワードで始まる：=COUNTIF(A2:A11,D1&”*”)
  • キーワードを含む：=COUNTIF(A2:A11,”*”&D1&”*”)
  • キーワードで終わる：=COUNTIF(A2:A11,”*”&D1)

また、データのある列(A列)の隣に、見出し行(1行目)の文字を含む場合に「1」を返す列(B列)を示す場合は、

=if(countif(A2,”*”&B1″*”),1,””)

とすればよい。

find_02

 
スポンサーリンク





ユーザー定義の書式について

Excelでの有効数字の表示について

 
Excelで数字データを取り扱うとき、多くの場合は表示させる有効数字を自分で揃える必要がある。大抵は指数表示にして小数点以下の桁数を指定しておけば良いのだが、時々、指数表示せずに有効数字を3桁や4桁に揃えたいときがあるので、”セルの書式設定”のユーザー定義で設定する方法についてメモしておく。
 

「セルの書式設定」について

 
セルの書式設定では、2つまでの条件について設定することができる。たとえば、有効数字4桁で、10未満の数字は「*.***」、100未満の数字は「**.**」と表示させる場合は、セルの書式設定→ユーザー定義から以下のように設定する。

[<10]0.000;[<100]00.00;G/標準
( 条件は[　]で指定し『;』で区切る：”条件1″;”条件2″;”それ以外” )

 
excel_cell_format_01
 
ただし、この条件で1000以上の数字を入力すると、上図のように「1000.0」と表示される。これも有効数字4桁で「1000」と表示したい場合には、『条件付き書式』で複数の条件を設定することで表示することができる。

「条件付き書式」について

 
条件付き書式を設定したいセルを選択して、「ホーム」タブの「条件付き書式」から「ルールの管理」を選択する。「新規ルール」から1000以上の場合は整数表示、100以上の場合は小数点以下第1位まで、10以上の場合は小数点以下第2位まで・・ というように任意の条件を設定して「適用」すると「条件付き書式」を設定することができる。
 
excel_cell_format_02
 
また、この「条件付き書式」の書式をコピー＆ペーストする方法については、Microsoftの”Office”のページの記載のとおり、[ホーム]、[書式のコピー/貼り付け] の順にクリックし、書式を適用するセルまたはセル範囲にペイントブラシでドラッグして条件付き書式を貼り付けることができた。

 
スポンサーリンク