【Excel2016、Proplus】小文字のアルファベットを連続挿入する

Excel2016やOffice365 Proplusになってから、オプションのユーザー設定リストにアルファベットを登録しても、小文字で入力できずに難儀している人も多いと思います。

そういった場合に便利なのが関数で挿入する方法です。

以前こちらでも紹介させて頂きました【オートフィルコピーでアルファベットを連続挿入したい】が、これを少し工夫して小文字に変化させます。

元の式はこちら

列の場合(横方向):=SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),1,)

行の場合(縦方向):=SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,)

これにLOWERを付け加えれば良いのです。
以下のようになります。

列の場合(横方向):=LOWER(SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),1,))

行の場合(縦方向):=LOWER(SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,))

それぞれ式を挿入したらオートフィルコピーしてください。

また、最終的に挿入した関数部分をコピーしてその場で「値」貼り付けをすれば、関数が消えて通常の文字列として使用できます。

 






検索したい数値(文字列)が比較したいデータの何行目にあるのか求める

例えば2つの比較したいデータがある時に、「〇〇〇」というものがもう片方のデータの中のどこにあるのか?を探すときに便利です。

ここでは式の作りを分かりやすく把握するために、同ファイル同シート内に2つのデータを作成して比較しています。
別シートや別ファイルでももちろん作成可能です。
画像はExcel2016を使用していますが、古いバージョンでもMATCH関数はあるので互換性があります。

下図のような場合

20200102

続きを読む

【FILTER関数】検索値が範囲内で重複してる場合でも、すべて違った値を抽出できる【SORT関数】

Excel2016やoffice365 Pro Plusで使える関数で、FILTER関数というものです。

選択範囲内で検索値が重複してある場合、VLOOKUPでは検索した一番最初の結果しか返してくれませんので、2個目以降の値を返してくることができません。

今までは配列関数を使って「VLOOKUPではできない、検索値が範囲内で重複している場合」のように複雑でしたが、FILTER関数を使えば非常に簡単に抽出できるようになりました。
フィルター機能で抽出するような感じで取り出すことができます。

例えば以下のように表があります。

20200102_3

続きを読む

【Excel2016 SWITCH関数】条件から結果を抽出するのに便利、IFやVLOOKUPの合わせ技のように使える

Excel2016やoffice365 Pro Plusで使える便利な関数です。

新しいIFS関数は照会しましたが、求める値が「Aならば◎、Bならば△・・・」などと固定してるものならば、SWITCH関数が便利です。
VLOOKUP関数のように別のセルに表を作成しておく必要もなく、従来のIF関数のように何度も何度もIFやカッコを入れ子にする必要がありません。

例えば以下のような表があります。

20191231_7

続きを読む

VLOOKUP応用、複数列に一致するものを抽出したい

抽出したいものが数値で合計を出したい場合はSUMIFS関数を使えば簡単にできますが、抽出したい値が文字列だった場合はどうしたら良いか?

VLOOKUPをそのまま使うと検索値はひとつだけしか指定できません。

これを応用して、複数列の中から検索値に合致する値の文字列を抽出する方法です。

例えば下図のような表があります。

左端には必ず番号を振っておくのがポイントです。

20180420

ここから下図のようなデータに一致するE列の値を抽出してみます。

20180420a

続きを読む

文字列の中から数字を取り出す(検索しやすい記号などがある時)

文字列の中間に数字がある時に取り出す方法は「文字列の中から数字を取り出す(数字が中間にある時)」で記載しましたが、こちらは数字の前後にカッコ「」など検索しやすい記号がある場合に使うと、式が多少短くなるのと配列ではなくなるので使いやすいと思います。

数字の前後にカッコ「」がついてる例だと以下のようになります。
"あいうえおかきくけこ「123」"

続きを読む

FINDで複数の文字を検索する

通常FIND関数だと1件しか検索できません。
=FIND("りんご",A1)
といったように使い、「りんご」という文字が検索セルの中の何文字目にあるかを返します。

検索するものが「りんご、みかん、ばなな・・・」といった具合に複数ある場合はどうしたらよいか?
下図のように検索する文字が複数ある場合の計算をしてみます。

0417

続きを読む

VLOOKUPではできない、検索値を2列以上のセルの中から指定して重複にも対応する

検索値に紐づいた値を抽出するのにVLOOKUP関数が便利です。
しかし検索値は1列しか指定できません。

隣のセルどおしをそのまま使って良いならばセルを繋げるだけなので簡単ですが、セルがバラバラで抽出したいものがどこにあるかわからない、さらに重複もある場合は困難です。

そういった場合の指定方法はVLOOKUPを使わずに以下のような式で対応すると良いです。

例えば下図があります。
この中から「担当者が山田」「評価がA」のものを抽出したいとき、単純にA列とB列を繋げただけでは対応できません。
重複するものが出てきてしまいます。

0403

ではどうしたらよいか。
以下のように配列を使っていきます。

続きを読む

VLOOKUPで検索値の文字列をあいまい検索(セル指定でワイルドカードを使用する)

VLOOKUP関数で文字列のあいまい検索をさせたい場合にワイルドカードを使いますね。

「*」アスタリスクは、文字数を決めないで検索可能
例:「アップル*」と指定すると「アップルジュース」も「アップルパイ」も検索できます。
「?」クエスチョンマークは、1文字を検索
例:「アップル?」と指定すると「アップルA」「アップルB」と検索できますが「アップルCC」は検索できません。

上記の例は検索する単語が決まっている場合です。
では検索する単語が都度変化する場合、セルを指定することが良くあります。
A1に文字入力して、そこを検索値とするケースです。

この場合よくやりがちなのが「*A1*」や「"*"A1"*"」と指定してしまいます。
しかしこれでは検索できません。

正しくは「"*"&A1&"*"」と「&」の記号で繋げるようにします。
例を見てみましょう。

続きを読む