【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,))

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

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

 

 

 





分を小数点に変える、かつ30分未満を0.5、30分以上を1として丸める

時間の表示を例えば「98:30」(98時間30分)と表示してるとします。(セルの書式設定のユーザー定義で[h]:mmとしてます)

これを「98.5」と小数点表示にしたい、かつ30分未満なら0.5で、30分以上なら1として表示したい場合です。

「98.5」なら分が30分以上になるので「99」の表示になります。

以下のようになります。

続きを読む

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

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

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

下図のような場合

20200102

続きを読む

【Excel2016 MAXIFS/MINIFS】複数条件を指定した最大値/最小値が簡単に計算できるようになった!

Excel2016やoffice365 Pro Plus で使えるようになったMAXIFS関数MINIFS関数です。

従来は条件を指定した中で最大値や最小値を出すのは結構大変でした。
※古いExcelバージョンでは以下の式を使ってください。

指定した条件の中から最大値を抽出する(MAX)
指定した条件の中から最小値を抽出する(MIN)[0]を無視する

MAXIFS関数MINIFS関数を使ってみます。

下図のような表があり、その中から条件を指定して「最大値」「最小値」を求めていきます。

20191231_4

続きを読む

【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

続きを読む

【Excel2016 IFS関数】 IF関数のネストが簡単になった!条件によって値を変える方法

Excel2016やoffice365 Pro Plusで使える便利な関数です。
今まで条件によって答えを変える場合、例えばAという条件なら答えは◎、Bという条件なら答えは△・・・などと振り分けるとき、
=IF(A2="A","◎",IF(A2="B","△",IF(A2="C"・・・
とIF関数を何度も何度もネスト(入れ子)しないといけませんでした。
Excelのバージョンによってはネストできる個数も少なくなりますし、結構面倒でした。

あらたに使えるようになったIFS関数ではこの煩わしさが解消しました。
条件のネストも127個まで指定できるようになりました。

例えば以下のような表があったときの条件式を作成してみます。

20191231

続きを読む

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

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

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

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

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

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

20180420

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

20180420a

続きを読む