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

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”のページの記載のとおり、[ホーム]、[書式のコピー/貼り付け] の順にクリックし、書式を適用するセルまたはセル範囲にペイントブラシでドラッグして条件付き書式を貼り付けることができた。

 
スポンサーリンク





周期表について

周期表

 
文部科学省のホームページに、周期表のポスターが公開されている。元素の英語名や原子量も記載されているので、以下にその一覧をメモ。

元素と原子量一覧

 

原子番号 元素 英語 記号 原子量
1 水素 Hydrogen H 1.008
2 ヘリウム Helium He 4.003
3 リチウム Lithium Li 6.941
4 ベリリウム Beryllium Be 9.012
5 ホウ素 Boron B 10.81
6 炭素 Carbon C 12.01
7 窒素 Nitrogen N 14.01
8 酸素 Oxygen O 16.00
9 フッ素 Fluorine F 19.00
10 ネオン Neon Ne 20.18
11 ナトリウム Sodium Na 22.99
12 マグネシウム Magnesium Mg 24.31
13 アルミニウム Aluminum Al 26.98
14 ケイ素 Silicon Si 28.09
15 リン Phosphorus P 30.97
16 硫黄 Sulfur S 32.07
17 塩素 Chlorine Cl 35.45
18 アルゴン Argon Ar 39.95
19 カリウム Potassium K 39.10
20 カルシウム Calcium Ca 40.08
21 スカンジウム Scandium Sc 44.96
22 チタン Titanium Ti 47.87
23 バナジウム Vanadium V 50.94
24 クロム Chromium Cr 52.00
25 マンガン Manganese Mn 54.94
26 Iron Fe 55.85
27 コバルト Cobalt Co 58.93
28 ニッケル Nickel Ni 58.69
29 Copper Cu 63.55
30 亜鉛 Zinc Zn 65.41
31 ガリウム Gallium Ga 69.72
32 ゲルマニウム Germanium Ge 72.63
33 ヒ素 Arsenic As 74.92
34 セレン Selenium Se 78.96
35 臭素 Bromine Br 79.90
36 クリプトン Krypton Kr 83.80
37 ルビジウム Rubidium Rb 85.47
38 ストロンチウム Strontium Sr 87.62
39 イットリウム Yttrium Y 88.91
40 ジルコニウム Zirconium Zr 91.22
41 ニオブ Niobium Nb 92.91
42 モリブデン Molybdenum Mo 95.94
43 テクネチウム Technetium Tc (99)
44 ルテニウム Ruthenium Ru 101.1
45 ロジウム Rhodium Rh 102.9
46 パラジウム Palladium Pd 106.4
47 Silver Ag 107.9
48 カドミウム Cadmium Cd 112.4
49 インジウム Indium In 114.8
50 スズ Tin Sn 118.7
51 アンチモン Antimony Sb 121.8
52 テルル Tellurium Te 127.6
53 ヨウ素 Iodine I 126.9
54 キセノン Xenon Xe 131.3
55 セシウム Cesium Cs 132.9
56 バリウム Barium Ba 137.3
57 ランタン Lanthanum La 138.9
58 セリウム Cerium Ce 140.1
59 プラセオジム Praseodymium Pr 140.9
60 ネオジム Neodymium Nd 144.2
61 プロメチウム Promethium Pm (145)
62 サマリウム Samarium Sm 150.4
63 ユウロピウム Europium Eu 152.0
64 ガドリニウム Gadolinium Gd 157.3
65 テルビウム Terbium Tb 158.9
66 ジスプロシウム Dysprosium Dy 162.5
67 ホルミウム Holmium Ho 164.9
68 エルビウム Erbium Er 167.3
69 ツリウム Thulium Tm 168.9
70 イッテルビウム Ytterbium Yb 173.0
71 ルテチウム Lutetium Lu 175.0
72 ハフニウム Hafnium Hf 178.5
73 タンタル Tantalum Ta 180.9
74 タングステン Tungsten W 183.8
75 レニウム Rhenium Re 186.2
76 オスミウム Osmium Os 190.2
77 イリジウム Iridium Ir 192.2
78 白金 Platinum Pt 195.1
79 Gold Au 197.0
80 水銀 Mercury Hg 200.6
81 タリウム Thallium Tl 204.4
82 Lead Pb 207.2
83 ビスマス Bismuth Bi 209.0
84 ポロニウム Polonium Po (210)
85 アスタチン Astatine At (210)
86 ラドン Radon Rn (222)
87 フランシウム Francium Fr (223)
88 ラジウム Radium Ra (226)
89 アクチニウム Actinium Ac (227)
90 トリウム Thorium Th 232.0
91 プロトアクチニウム Protactinium Pa 231.0
92 ウラン Uranium U 238.0
93 ネプツニウム Neptunium Np (237)
94 プルトニウム Plutonium Pu (239)
95 アメリシウム Americium Am (243)
96 キュリウム Curium Cm (247)
97 バークリウム Berkelium Bk (247)
98 カリホルニウム Californium Cf (252)
99 アインスタイニウム Einsteinium Es (252)
100 フェルミウム Fernium Fm (257)
101 メンデレビウム Mendelevium Md (258)
102 ノーベリウム Nobelium No (259)
103 ローレンシウム Lawrencium Lr (262)
104 ラザホージウム Rutherfordiumu Rf (267)
105 ドブニウム Dubnium Db (268)
106 シーボーギウム Seaborgium Sg (271)
107 ボーリウム Bohrium Bh (270)
108 ハッシウム Hassium Hs (269)
109 マイトネリウム Meitnerium Mt (278)
110 ダームスタチウム Darmstadtium Ds (281)
111 レントゲニウム Roentgenium Rg (281)
112 コペルニシウム Copernicium Cn (285)
113 ウンウントリウム Ununtrium Uut (278)
114 フレロビウム Flerovium Fl (289)
115 ウンウンペンチウム Ununpentium Uup (289)
116 リバモリウム Livermorium Lv (293)
117 ウンウンセプチウム Ununseptium Uus (294)
118 ウンウンオクチウム Ununoctium Uuo (294)

 

その他

 
2015年末には、113番目の元素の命名権を日本の理化学研究所が獲得しているので、今後、どのような名前に確定されるのか楽しみである。

 
スポンサーリンク





完全混合槽の濃度変化について

完全混合槽の濃度計算

 
貯槽に初期濃度C0 mol/Lの試薬がV m3入っているとき、C1 mol/Lの試薬がF m3/hで流入する場合のt時間後の貯槽内および流出液(F m3/h)の濃度 C2 mol/Lを推算する場合についてメモ。
 
complete_mixing_01
 
槽内は完全混合しているとした場合、槽内の濃度変化は以下となる。
 
d (V・C2)/dt = F・C1-F・C2
V・dC2/dt = F・C1-F・C2
 
ここで、上式を変数分離して
 
V/F ・dC2/dt = C1-C2
dC2/C1-C2 = F/Vdt
 
これを積分。
 
-ln|C1-C2| = F/V・t+C
 
ここで、t=0のときC2=C0なので、Cは以下となる。
 
C=-ln|C1-C0|
 
これより、t時間後の貯槽濃度C2は以下式で推算される。
 
-ln|C1-C2| = F/V・t-ln(C1-C0)
ln|C1-C2| = -F/V・t+ln|C1-C0|
|C1-C2| = exp(-F/V・t)・|C1-C0|
 (前提条件より C1>C0のときはC1>C2であり C1<C0のときは C1<C2)
∴ C2 = C1-(C1-C0)・exp(-F/V・t)
 
complete_mixing_02
 
 
スポンサーリンク





横置円筒槽の液量について

横置円筒槽の液量

 
円筒状の槽が横向きに設置されている時の液高さと液量について以下にメモ。

cylinder_01

cylinder_02

扇部(①+②)の面積
1/2・θ・r2 ・・・(1)

①の面積
1/2・(sinθ)(cosθ)・r2 ・・・(2)

②の面積 (1) – (2)
1/2・θ・r2・(θ – (sinθ)(cosθ) ) ・・・(3)

これより、高さ(h)まで液を入れた時の液量は以下となる。
1/2・θ・r2・( θ – (sinθ)(cosθ) )・2・L

cylinder_03

 
スポンサーリンク





“Ctrl+矢印”でのジャンプについて

“長さ0の文字列”を含む見た目が空白のセルを”Null”に変換

 
Excelで「=IF(A1=1,”○”,””)」などのような関数で””(長さゼロの文字列)を返したときは、見た目が空白でもデータが入力されたセルとして取り扱われる。こうした関数を含むリストをコピーして、別シートなどに”値”で張り付けて使用することがあるのだが、この際も、”長さゼロの文字列”が”値”として残ってしまい、”Ctrl+矢印”でもジャンプしてしまう。
 
“値”のみのデータ群で、”長さゼロの文字列”を”Null”に変換したい場合のメモ。
 

  • “長さゼロの文字列”を含む”値”のみのデータ群を選択。
  • “Ctrl+H”で「置換」
  • 「検索する文字列」は何も入力せず、「置換後の文字列」に「あああああ」※を入力
  • 「すべて置換」した後に、再度、同じデータ群を選択して”Ctrl+H”で「置換」
  • 「検索する文字列」に「あああああ」を入力し「置換後の文字列」は何も入力しないで「すべて置換」
※ “長さゼロの文字列”を含む見た目が空白のセルを「置換する文字列」は、置換した際にすぐ分かるようなそのデータ群にない文字列(あああああ、○○○○○、11111、AAAAAなど)を任意に設定。

 
replacement_01
 

 
スポンサーリンク





圧力の単位換算について

圧力単位の換算表

 
以下に、圧力の単位換算についてメモ。(引用:計量法附則第三条の計量単位等を定める政令(平成四年十一月十八日政令第三百五十八号))

 

Pa kPa bar atm mbar Torr mAq
1Pa 1 10-3 10-5 9.86923×10-6 10-2 7.50062×10-3 1.01972×10-4
1kPa 103 1 10-2 9.68923×10-3 10 7.50062 1.01972×10-1
1bar 105 102 1 0.986923 103 750.062 10.1972
1atm 101325 101.325 1.01325 1 1013.25 760 10.3323
1mbar 100 10-1 10-3 9.86923×10-4 1 7.50062×10-1 1.01972×10-2
1Tarr 133.322 0.133322 1.33322×10-3 1.31579×10-3 1.33322 1 1.35951×10-2
1mAq 9806.65 9.80665 9.80665×10-2 9.67841×10-2 98.0665 7.35560×10 1

 
スポンサーリンク