文字を抽出するには、様々な方法。
様々な関数が用意されています。
例えば、LEFT関数やRIGHT関数ですね。
ですが、文字列の中の指定された文字の前後を抽出したい場合、これらの関数では、手早く抽出することはできません。
なぜならば、左から何文字目か判断させる必要があるからです。
そこで、新しい技術・新しい関数をつかってみると、楽々抽出することができます。
その方法をご紹介しております。
横長の表から、必要な列だけを抽出するとなると、なかなか面倒です。
サンプルなのでデータ量が多くありません。
このような場合、コピペで対応できるとは思いますが、データ量が多い場合、列を選択するだけでも大変です。
そこで、FILTER関数とCOUNTIF関数を組み合わせるだけで、手早く、抽出した表をつくることができます。
では早速やってみましょう。
事前準備として、抽出したい列の見出し行を用意します。
A8に次の数式を設定します。
=FILTER(A2:H5,COUNTIF(A7:E7,A1:H1))
これだけで、抽出することができました。
FILTER関数は、スピル機能対応なので、オートフィルで数式をコピーする必要はありません。
これで、完成しました。
では、この数式を確認しておきましょう。
FILTER関数は、抽出する関数です。
最初の引数は、「配列」。データなので、A2:H5を範囲します。
見出しは不要です。
2つ目の引数は、「含む」。
条件です。
この引数に、1行の配列を指定すると、列の抽出を行うことができます。
そこで、COUNTIF関数をつかって、用意した見出しと一致したら「1」と算出されてます。
1となった列が抽出されるという仕組みです。
それでは、データタブの「数式の検証」をつかって確認してみます。
COUNTIF関数の結果を見ると、用意した見出しのところが「1」になっていることが確認できます。
ただし、この数式には、欠点があります。
左から同じ順番でないと、抽出することができません。
このように、FILTER関数とCOUNTIF関数を組み合わせることで、手早く指定した表を抽出することができます。
Facebookページに書いた、Excelの豆知識（Trivia）です。
3月16日
Excel。
Ctrl＋Alt＋=
拡大表示します。
3月17日
Excel。
Ctrl＋Alt＋-
縮小表示します。
3月18日
Excel。
Ctrl＋Alt＋F5
ブック内のすべてのデータを更新します。
3月19日
Excel。
Ctrl＋Alt＋F9
最終変更に関わらず開いているブックのすべてのワークシートを再計算
3月20日
Excel。
Shift＋F1
ポップヒントを表示します
3月21日
Excel。
Shift＋F2
メモをセルに表示する
3月22日
Excel。
Shift＋F3
関数の挿入ダイアログボックスを表示
MDETERM関数
読み方： エムデターム
読み方： マトリック ディターミナント
分類： 数学/三角
MDETERM(配列)
配列の行列式を算出します
つぎのような表があります。
数式では、値として固定されていません。
そこで、コピーして、そのまま値として貼り付けを行いたい。
単純な作業ですが、面倒です。
まして、一度きりでなければ、なおさら面倒です。
そこで、今回は、Excel VBAでプログラムをつくってみることにしました。
Sub 値貼り付け()
With Range("A1").CurrentRegion
.Value = .Value
End With
End Sub
実行してみます。
とてもシンプルなプログラム文ですが、コピー値の貼り付けができています。
では、プログラム文を確認しておきましょう。
With Range("A1").CurrentRegion
With～End With文です。With文をつかうことで、そのあとの構文を繰り返し入力しなくて済むようになります。
Range("A1").CurrentRegion
A1を起点とした連続するセル。すなわち、A1を起点とした表を範囲選択します。
.Value = .Value
セル範囲の値（数式の結果）を改めて、同じ範囲に代入します。
この1行だけで、コピー値として貼り付けをしています。
四則演算の「+」で足し算をすると、#VALUE!というエラーが表示されることがあります。
この原因は、範囲に「文字」が入っていることが原因なんですね。
ところが、SUM関数をつかってみると…
だから、単純な足し算であっても、オートSUMボタンでお馴染みのSUM関数をつかうわけですね。
MAXIFS関数
読み方： マックスイフズ
読み方： マックスイフエス
分類： 統計
MAXIFS(最大範囲,条件範囲1,条件1,…)
条件により指定した範囲内の最大値を算出します
通常の連番ではなくて、①などの丸数字で連番を設定したい場合、①と入力してオートフィルでコピーしても、連番を入力することはできません。
そこで、C2に①と入力しておいてから、C3に次の数式を設定します。
=CHAR(CODE(C2)+1)
あとは、オートフィルで数式をコピーすれば、丸数字の連番を入力することができました。
もっとスムーズ数式ならば、SEQUENCE関数を「+1」のところに使う方法もありますが、丸数字は⑳までしかないので、シンプルな数式でもいいように思えます。
では、
=CHAR(CODE(C2)+1) の確認をしましょう。
CHAR関数は、文字コードに基づく、文字を表示する関数です。
文字には、それぞれ文字コードが割り振られています。
そのコード番号という数値から該当する文字を表示してくれるわけです。
要するに①という文字コードがわかれば、+1することで、②・③…と入力することができるわけです。
それでは、①の文字コードをどうやって調べたらいいのか。
そこで登場するのが、CODE関数です。
CODE関数は、CHAR関数とは逆で、文字から、その文字の文字コードを求めることができる関数です。C2に①が入力されていますので、
CODE(C2)
とすることで、①の文字コードを求めることができるというわけです。
オートフィルで数式をコピーしたら②と表示したいのでCODE(C2)に+1 を加えてあげることで、丸数字の連番をつくることができるという仕組みです。
なお、SEQUENCE関数をつかった数式は、
=CHAR(CODE("①")+SEQUENCE(5,,0))
=CHAR(CODE("①")+SEQUENCE(5,,0))
と入力するだけです。
スピル機能によって、オートフィルで数式をコピーする必要はありません。
MAXA関数
読み方： マックスエー
分類： 統計
MAXA(値1,[値2],…)
数値・文字列・論理値を含む最大値を算出します
お一人、お一人、空いている日時を確認して、ダメなら調整してを繰り返す。
日程調整は、事務方泣かせの面倒なお仕事の一つです。
そこで、文字列結合をつかうことで、改善することができるんです。
その対応方法をご紹介しております。
抽出するのに便利なFILTER関数ですが、ちょっと困ることがあります。
次の表をつかって説明します。
チームがBで、合宿参加のデータを抽出したいので、F4にFILTER関数をつかって、抽出しました。
F4につくった数式は、
=FILTER(A2:D11,C2:C11=G1,"データなし")
最初の引数は、「配列」。
範囲選択なので、A1:D11。
FILTER関数は、スピル機能対応の関数なので、絶対参照は不要です。
2つ目の引数は、「含む」。
条件なので、C2:C11=G1 と等しいデータという条件で抽出します。
G1はBなので、チームBを抽出します。
最後の引数は、「空の場合」。
該当データがない場合は、どうするかということで、「データなし」と表示する設定をしました。
抽出結果は、チームBの人が抽出できたのですが、よくみると、合宿参加の列。
I4に「0（ゼロ）」と表示されています。
抽出元のデータは、空白なのですが、0（ゼロ）と表示されてしまっています。
これでは、今回たまたま「○（まる）」ですが「0（ゼロ）」だった場合、空白で0が表示されているのか、抽出元が0なのかわかりません。
では、どのようにしたら、抽出元が空白の場合、抽出結果も空白にすることができるのでしょうか。
抽出元が空白なので、0になってしまいます。
そこで、空白を意味する「””（ダブルコーテーション×2）」に置換した状態にして抽出する必要があります。
よって、FILTER関数を次のように修正することで対応できます。
これで、0ではなく空白のままにすることができました。
修正したのは、「配列」のところにIF関数を追加しました。
IF(A2:D11="","",A2:D11)
A2:D11で、空白セルは、空白。
そうでなければ、A2:D11のセルに設定されているデータのままという意味です。
このように、抽出で便利な関数のFILTER関数ですが、ケースによっては、アレンジする必要があります。
Facebookページに書いた、Excelの豆知識（Trivia）です。
3月9日
Excel。
Ctrl＋Shift＋End
選択範囲を最後のセル (右下隅) まで拡張する。
3月10日
Excel。
Ctrl＋Shift＋Space
全選択。Ctrl+Aと同じ
3月11日
Excel。
Ctrl＋Shift＋Space
図形などオブジェクトを選択時すべてのオブジェクトを選択する
3月12日
Excel。
Ctrl＋Shift＋Tab
ブックを切り替える
3月13日
Excel。
Ctrl＋Shift＋Page Up
現在のシートと前のシートを選択する。
3月14日
Excel。
Ctrl＋Shift＋Page Down
現在のシートと次のシートを選択する。
3月15日
Excel。
Ctrl＋Alt＋V
形式を選択して貼り付けダイアログボックスが表示されます。
MAX関数
読み方： マックス
分類： 統計
MAX(数値1,[数値2],…)
最大値を算出します
3つの地域の売上表があります。
A1:D4には、関東地区。
F1:I3には、関西地区。
そして、K1:N3には、九州地区の販売データがあります。
これを、A6から起点とする表に転記し、結合。
要するに、合体させます。
ただ、合体するのではなく、販売金額を降順で並べ替えした表にしたい。
コピペで貼り付けて、その後並べ替えするのでもいいのですが、少し面倒です。
また、Excel VBAでプログラム文をつくるというのも、面倒です。
そこで、今回は、SORT関数とVSTACK関数を組み合わせた数式で対応しようと思います。
A7をクリックします。
次の数式を設定します。
=SORT(VSTACK(A2:D4,F2:I3,K2:N3),4,-1,FALSE)
数式を確定して確認します。
では、数式を確認します。
SORT関数は、並べ替えをする関数です。
最初の引数は、「配列」。
範囲です。
この範囲は、合体したデータなので、それぞれの表を合体させるのがVSTACK関数です。
VSTACK関数の説明は後述します。
先に、SORT関数の確認を続けます。
SORT関数の2つ目の引数は、「並べ替えインデックス」。
どの列を並べ替えの条件とするかということです。
左から4番目の販売金額で並べ替えをしますので、4と設定します。
3つ目の引数は、「順序」。
昇順で並べ替えならば、1。
降順で並べ替えならば、-1を設定します。
降順で並べ替えをしたいので-1と設定します。
4つ目の引数は、「方向」。
列方向で並べ替えをするならば、TRUE。
行方向で並べ替えをするならば、FALSEと設定します。
今回は、行方向で並べ替えをしますので、FALSE と設定します。
これで、並べ替えを行うことができます。
最初の引数で使用するVSTACK関数ですが、これは、合体したいデータを設定するだけなので、見出し行をのぞいた、それぞれの地域
関東のA2:D4
関西のF2:I3
九州のK2:N3
を設定するだけです。
VSTACK関数は、指定した範囲を結合する関数です。
なお、このVSTACK関数は、便利な関数ですが、Microsoft365など、比較的新しいバージョンに登録されていますので、ご注意ください。
ちょっとした、サンプル文書がほしいときに、まさか、「ああああ…」とか大量に「あ」を入力していませんか？
そんなことをしなくても、サンプル文書を表示させる裏コマンドっぽいものがあるんです。
なんか、Excelの関数みたいな数式を入力するだけです。
ということで、サンプル文書を表示させるさせる方法を紹介しております。
MATCH関数
読み方： マッチ
分類： 検索/行列
MATCH(検査値,検査範囲,[照合の種類])
値を検索してその相対位置を算出します
A1:D8に店舗販売のデータがあります。
そこで、FILTER関数をつかうことにしました。
F5に設定した数式は、
=FILTER(A2:D8,C2:C8=G1,"該当なし")
これで、地域が関西のデータを抽出することができました。
FILTER関数を確認しておきます。
最初の引数は、「配列」。
範囲のことなので、A2:D8。
FILTER関数は、スピル機能対応の関数なので、絶対参照は不要です。
2つ目の引数は、「含む」。
条件のことなので、C2:C8=G1。
条件の列はC2:C8で、抽出条件は、G1に入力されています。
それと合致するかどうかという条件式です。
3つ目の引数は、「空の場合」。
データがなかった場合にどう処理するのかということなので、”該当なし”と表示する様にしました。
スピル機能対応の関数なので、オートフィルで数式をコピーする必要はありません。
さて、抽出することはできましたが、今回やりたいことは、抽出された件数をG2に求めたいわけです。
件数なので、COUNT関数をつかってみればいいはずです。
=COUNT(F5:F6)
と設定すれば、2件と求めることができました。
一度だけならば、これでいいのですが、地域を「関東」に変更してみると、3件抽出されてましたが、件数は2のままです。
範囲は、F5:F6と固定されていますので、連動してくれません。
仮に、F列の下方向に、何もデータがなければ、F5:F200とか想定される上限の範囲設定をしていてもいいかもしれません。
しかし、下方向にデータがある場合などには、少し都合が悪くなります。
抽出に連動した範囲にすることはできないのでしょうか。
そこでROWS関数をつかうことにします。
ROWS関数は、範囲に含まれる行数を求めることができる関数です。
ROW関数は、セル番地の行数なので、ROW関数では対応できません。
G2にROWS関数の数式を設定します。
これで確定します。
条件を関東に変更しても、件数は3件と正しく求めることができました。
FILTER関数で自動的に範囲が変動しても、ROWS関数は抽出結果に合わせて行数を求めてくれます。
行数はデータの件数と同じなので、COUNT系の関数をつかわなくても、抽出したデータの件数を求めることができるというわけです。
Facebookページに書いた、Excelの豆知識（Trivia）です。
3月2日
Excel。
Ctrl＋Shift＋←
左側に連続するセルを範囲選択する
3月3日
Excel。
Ctrl＋Shift＋F1
ウィンドウを最大化してリボンを含め上部を非表示にする
3月4日
Excel。
Ctrl＋Shift＋F2
コメントをセルに追加
3月5日
Excel。
Ctrl＋Shift＋F3
選択範囲から名前を作成ダイアログボックスが表示されます
3月6日
Excel。
Ctrl＋Shift＋F6
アクティブブックの前のブックに移動する。
3月7日
Excel。
Ctrl＋Shift＋F12
印刷プレビューを表示 Ctrl+Pと同じ
3月8日
Excel。
Ctrl＋Shift＋Home
選択範囲をワークシートの先頭のセルまで拡張する。
MAP関数
読み方： マップ
分類： 論理
MAP(array,lambda_or_array2,…)
LAMBDAを適用して新しい値を作成することにより、配列内の各値をmappingで形成された配列を新しい値に返す
会議参加予定フィールド内に「,（カンマ）」で区切られた文字列。
1日目は「町田,成瀬,津田」と入力されていますので、会議参加者数は、3名です。
この3を求めたいわけです。
Excelにエクスポートして、求めてもいいのですが、いちいちExcelにエクスポートするのも、面倒なので、Accessで求めたいわけです。
ExcelもAccessも一発で求めることはできませんが、基本的な考え方は同じです。
フィールド内の文字数から「,（カンマ）」を除いた文字数を引けば、「,（カンマ）」の数が求められます。
求めた数は、区切り数なので、「+1」すれば、「,（カンマ）」で区切られた文字列数を求めることができるというわけです。
Excelならば、
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
のような、数式をつくるわけです。
Accessでは、クエリで演算フィールドをつかって対応することになりますが、Excelと違うのは、SUBSTITUTE関数が、Accessにはありません。
そこで、Accessでは、Replace関数をつかって、「,（カンマ）」を空白に置換して「,（カンマ）」を除いた文字数を求めます。
なお、文字数を数えるのは、Accessでは、Excelと同じLen関数が用意されています。
それでは、クエリで演算フィールドつくっていきます。
作成タブのクエリデザインをつかいます。
また、テーブルのフィールドはすべて使っています
参加人数: Len([会議参加予定])-Len(Replace([会議参加予定],",",""))+1
と設定します。
では、実行してみましょう。
Excelとの違いは、Replace関数をつかって、置換したことです。
入力作業。
結構面倒です。
そして、入力ミスを起こりがち。
例えば、小数点第1位まで入力しちゃダメなのに、入れてしまったとか。
それなら、入力作業の前に、小数点第1位までしか入力できないようにしたらいいわけです。
では、どのようにしたらいいのでしょうか。
その方法をご紹介しております。
作業効率もUPする、知っていると便利なショートカットキー。
なお、Excelのバージョンによって多少変わります。
Ctrl＋A
全選択
Ctrl＋B
太字にする
Ctrl＋C
コピーする
Ctrl＋D
上のセルを下にコピーする
Ctrl＋E
フラッシュフィル
参加者名簿があります。
しかも、1が2回登場するようなことがないように、つまり重複しないようにしたいわけです。
今回は10名なので、自力で振り分けることもできますが、件数が多くなれば、とても大変な処理になってしまいます。
何かいい方法は、ないのでしょうか。
そこで、C2に次の数式を設定してみます。
=SORTBY(SEQUENCE(10),RANDARRAY(10))
使用している関数は、スピル機能対応なので、絶対参照や、オートフィルで数式をコピーする必要はありません。
この数式がどのように動くことで、1から10までの数値をランダムで並べることができるのか、関数を確認していきます。
SORTBY関数は、並べ替えをする関数です。
最初の引数は、「配列」。
2つ目の引数が「基準配列1」なのですが、それぞれ関数をつかっていますので、最初の引数で使用している
最初の引数を使用している関数は、SEQUENCE関数です。連番を作る関数です。
SEQUENCE(10) で、1から10までの連番を設定することができます
この数値をランダムで並べ替える必要があるわけです。
そこで、RANDARRAY関数をつかって、乱数を発生させます。
小数の乱数を発生するので、算出結果が重複することは、まずありません。
なお、セルをアクティブなどするたびに、再計算されます。
説明の都合で、C列の順番を作成した後に、F列をつくっています。
そのため、F列の結果とC列の結果は合致しませんので、ご了承ください。
MAKEARRAY関数
読み方： メイクアレイ
分類： 論理
MAKEARRAY(rows,column,function)
LAMBDA関数を適用して、指定した行と列のサイズの計算配列を返します。
Facebookページに書いた、Excelの豆知識（Trivia）です。
2月23日
Excel。
Ctrl＋Shift＋[
数式で直接参照しているセルをすべて選択します。
2月24日
Excel。
Ctrl＋Shift＋]
数式で直接参照している数式が入力されたすべてのセルを選択します。
2月25日
Excel。
Ctrl＋Shift＋`（アクサングラーブ）
数式を表示する
2月26日
Excel。
Ctrl＋Shift＋￥
選択した列内で、アクティブセルと合致しないセルを選択
2月27日
Excel。
Ctrl＋Shift＋↑
上側に連続するセルを範囲選択する
2月28日
Excel。
Ctrl＋Shift＋→
右側に連続するセルを範囲選択する
3月1日
Excel。
Ctrl＋Shift＋↓
下側に連続するセルを範囲選択する
ボール投げの成績一覧表があります。
空白を選択して、一括入力でもいいのですが、ちょっと面倒です。
そこで、今回は、Excel VBAでプログラム文をつくってみることにしました。
Sub 空白に文字()
Range("A1").CurrentRegion.SpecialCells(Type:=xlCellTypeBlanks).Value = "不参加"
End Sub
たった一行ですが、実行してみます。
たった1行ですが、プログラム文を確認しておきましょう。
Range("A1").CurrentRegion.
A1から連続する範囲。
つまり表を選択しています。
SpecialCells(Type:=xlCellTypeBlanks)
指定した範囲内の空白セルに次の処理をします。
Value = "不参加"
「不参加」という文字列を設定する
という処理をすることができます。
数値のデータを数えるには、COUNT関数です。
数値も文字も、さらに算出結果が空白のデータを数えるのはCOUNTA関数です。
それでは、文字だけのデータを数えるには、どのようにしたらいいのでしょうか。
しかも、文字列は合致していません。
このような場合の算出方法をご紹介してます。
"文字"という条件にアレをつかえば解決しますが、なかなか、思い浮きませんね。
LOWER関数
読み方： ロウアー
分類： 文字列操作
LOWER(文字列)
英字を小文字に変換する
A4:D11に、店舗別の売上表があります。
つまり、1行分のデータを表引きしたいわけです。
そこで、VLOOKUP関数をつかってという方法もあります。
ただし、VLOOKUP関数だと、3番目の引数の列番号にCOLUMN関数をつかうなど、アレンジしなければなりません。
XLOOKUP関数も同様です。
表引きできますが、面倒です。
オートフィルターをつかって抽出したデータをコピーして、貼り付ける方法もあります。
ただし、抽出するだけならばいいですが、抽出後に、別の場所に表示するとなれば、作業工程数が増えます。
そこで、今回はINDEX関数をつかって対応してみようと思います。
B2に数式を設定します。
最初の引数は、配列。範囲です。
B5:D11を範囲選択します。
スピル機能がうごきますので、絶対参照は不要です。
2つ目の引数は、行番号です。
A2のNOをつかいますので、A2と設定します。
3つ目の引数は、B列を起点としますので、0（ゼロ）と設定します。
では、数式を確定して結果を確認してみましょう。
スピル機能によって、オートフィルで数式をコピーする必要はありません。
この方法以外にはも、OFFSET関数を使う方法など、色々な方法があります。
今回は、その中から、INDEX関数をつかった方法をご紹介いたしました。
LOOKUP関数
読み方： ルックアップ
分類： 検索/行列
LOOKUP(検索値,配列)
縦横を指定しないでセルを検索し対応するセルの値を返す
順位を算出するには、RANK.EQ関数をつかうわけです。
さて、今回は、この算出した順位を、①・②とかの「丸付き数値」で算出したいわけです。
では、どのようにしたら、手早く求めることができるのでしょうか。
その解決方法をご紹介していきます。
得点表があります。
「英語・国語・数学・理科・社会」という順番になっています。
これを、「数学・理科・英語・国語・社会」という順番で並べ替えをしたい。
自力で、コピペを繰り返して列を並べ替えるのは、とても面倒です。
何か、効率の良い方法で、手早くつくることはできないものでしょうか。
そこで、SORTBY関数とXMATCH関数を組み合わせることで、列を並べ替えた別表をつくることができます。
I2をクリックします。
=SORTBY(A2:G7,XMATCH(A1:G1,I1:O1))
数式を確定して、並び順を確認してみると、きちんと列方法で並べ替えされていることが確認できます。
この数式をつかうには、事前に、並べ替えをしたい順の見出しを用意する必要があります。
さて、この数式を確認しておきましょう。
SORTBY関数は、並べ替えを行う関数です。
最初の引数は、「配列」。
範囲なので、A2:G7
SORTBY関数は、スピル機能対応の関数なので、絶対参照は不要です。
次の引数は、基準1。
並べ替えをおこなうルールのことです。
ここにXMATCH関数をつかいます。
そのXMATCH関数は、MATCH関数の上位版の関数です。
MATCH関数と同じで、指定したデータが何番目にあるかを求めることができます。
XMATCH関数の最初の引数は、検索値です。
検索値は、A1:G1。
次の引数は、検索範囲で、I1:O1
XMATCH関数の結果は、
{1,2,5,6,3,4,7}
Facebookページに書いた、Excelの豆知識（Trivia）です。
2月16日
Excel。
Ctrl＋Shift＋;
挿入ダイアログボックスが表示されます
2月17日
Excel。
Ctrl＋Shift＋:
セルの周囲の現在の選択範囲を選択
2月18日
Excel。
Ctrl＋Shift＋.
左のセルの内容を右にコピーする
2月19日
Excel。
Ctrl＋Shift＋.
テキストボックス内：フォントサイズを拡大
2月20日
Excel。
Ctrl＋Shift＋,
上のセルを下にコピーする
2月21日
Excel。
Ctrl＋Shift＋,
テキストボックス内：フォントサイズを縮小
2月22日
Excel。
Ctrl＋Shift＋_ （アンダースコア）
セルに外枠罫線を解除
LOOKUP関数（ベクトル形式）
読み方： ルックアップ
分類： 検索/行列
LOOKUP(検索値,検索範囲,対応範囲)
1行/1列のセル範囲でセルを検索し対応するセルの値を返す
ホームタブにある条件付き書式。
その中に、カラースケールというのが用意されています。
確かに、単色のグラデーションは用意されていますが、種類も少ないですし、緑・黄・赤であっても、ちょっと派手な感じもします。
カラースケールを設定した後に、再度範囲選択をして、ホームタブの条件付き書式にある「ルールの管理」をクリックします。
条件付き書式ルールの管理ダイアログボックスが表示されます。
書式ルールの編集ダイアログボックスが表示されます。
最小値・中間値・最大値のそれぞれの色を設定します。
用意されていない、紫色のグラデーションにしてみます。
設定が終わりましたら、OKボタンをクリックします。
改めてOKボタンをクリックします。
