Facebookページに書いた、Excelの豆知識（Trivia）です。
11月24日
Excel。
Ctrl＋T
[ テーブルの作成 ] ダイアログ ボックスを表示
11月25日
Excel。
Ctrl＋U
下線を引く
11月26日
Excel。
Ctrl＋V
貼り付ける
11月27日
Excel。
Ctrl＋W
ブックを閉じる
11月28日
Excel。
Ctrl＋X
切り取りする
11月29日
Excel。
Ctrl＋Y
やり直す
11月30日
Excel。
Ctrl＋Z
元に戻す
表のB列には、会議参加者の氏名が入力されています。
そして、氏名は、「,（カンマ）」で区切られています。
その氏名の人数を、C列に求めたい場合、どのようにしたら、手早く求めることができるのでしょうか。
COUNTIF関数は、セル対象であり、セル内の文字数を求めることは出来ないからです。
そこで、全体の文字数から「,」を除いた文字数を減算すれば、「,」の数を求めることができます。
その値に「+1」すれば、「,」で区切られた文字列数を求めることができます。
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
これで、「,」で区切られた文字列数を求めることができました。
では、この数式の仕組みを確認しましょう。
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
LEN関数は、セル内の文字数を求めることができる関数です。
最初に、セル内の文字列数を求めました。
SUBSTITUTE(B2,",","")
SUBSTITUTE関数は、置換してくれる関数です。
「,」を「””（空白）」に置換します。
そして、LEN関数をつかうことで、「,」を除いた文字数を求めることができます。
全体の文字数から「,」を除いた文字数を減算すれば、「,」の数を求めることができるというわけです。
そして、文字列と文字列の間に「,」がありますので、「+1」することで、「,」で区切られた文字列数を求めることができるというわけです。
ISOMITTED関数
読み方： イズオミテッド
分類： 情報
ISOMITTED(argument)
LAMBDAの値がないかどうかを確認し、TRUEまたはFALSEを返す
見出しやタイトルを目立たせるときにつかえる、「段落罫線」。
その下罫線には、ショートカットキーがあります。
しかも、6種類。
知らないと、線種とページ罫線と網掛けの設定ダイアログボックスから設定しなければなりません。
ちょっと面倒なんですね。
次の得点表。
その条件は、得点が50以上なら赤色で80以上なら緑色に設定します。
では、B2:B11を範囲選択して、ホームタブの条件付き書式にある、新しいルールをクリックします。
今回の条件が以上なので、用意されているものでは対応できません。
セルの値 が 次の値以上 を選び、80と設定します。
あとは、書式ボタンをクリックして、塗りつぶす色を設定します。
設定が完了したら、OKボタンをクリックします。
同じように50以上を塗りつぶしてみましょう。
すると、先程、80以上で塗りつぶされていたセルまで、50以上の色で塗りつぶされてしまいました。
どうしてこのようになってしまったのかというと、優先順位は、あとから設定した条件の方が上になるようになっているからです。
そこで、このような場合、条件の順位を変えることで、対応することができます。
条件付き書式の「ルールの管理」をクリックして、条件付き書式ルールの管理ダイアログボックスを表示します。
設定したのに、おかしい場合には、優先順位が影響している可能性があります。
その場合は、確認してみるといいかもしれません。
作業効率もUPする、知っていると便利なショートカットキー。
なお、Excelのバージョンによって多少変わります。
Alt＋F8
マクロダイアログボックスが表示されます
Alt＋F10
オブジェクトの選択と表示作業ウィンドウを表示する
Alt＋F11
Microsoft Visual Basic For Applications エディターを開く
Alt＋F12
Power Query エディターを開く
ISODD関数
読み方： イズオッド
分類： 情報
ISODD(数値)
対象が奇数の場合にTRUEを返す
Facebookページに書いた、Excelの豆知識（Trivia）です。
11月17日
Excel。
Ctrl＋L
テーブルの作成ダイアログ ボックスを表示
11月18日
Excel。
Ctrl＋N
新規ブックを作成する
11月19日
Excel。
Ctrl＋O
ブックを開く
11月20日
Excel。
Ctrl＋P
印刷プレビューを表示
11月21日
Excel。
Ctrl＋Q
クイック分析 オプションを表示
11月22日
Excel。
Ctrl＋R
左のセルの内容を右にコピーする
11月23日
Excel。
Ctrl＋S
ファイルを保存する
氏名フィールドに、フルネームで氏名が入力されています。
この氏名フィールドから、姓フィールドと名フィールドにデータをわけたいのですが、どのようにしたらいいのでしょうか。
Excelですと、FIND関数という空白の文字を見つける関数があるのですが、AccessにはFIND関数はありません。
その代わりになるのが、InStr関数です。
InStr関数をつかって、空白を文字内から見つけることができれば、分割することは可能です。
では、早速クエリをつくっていくことにします。
作成タブのクエリデザインをつかってつくっていきます。
姓: Left([氏名],InStr(1,[氏名]," ")-1)
この演算フィールドで使用されている関数は、
Left関数。
文字列の左側から指定の文字数を抽出する関数です。
Excelでもお馴染みですね。
Left関数の引数ですが、最初の引数は、文字列なので、氏名フィールドです。
2つ目の引数が、文字数です。文字数は、空白の前までです。
ExcelだとFIND関数をつかうのですが、Accessでは、InStr関数をつかって、空白を見つけます。
InStr(1,[氏名]," ")
「" "」と半角空白にしていますが、これで全角空白にも対応することができます。
InStr関数の最初の引数は、省略可能ですが、開始位置です。
最後に「-1」しているのは、見つけた空白の位置よりも一文字少ないのが姓だからです。
つづいて、名ですが、
Replace関数を使う方法もありますが、今回は、Right関数という右側から指定の文字数分を抽出するようにします。
名: Right([氏名],Len([氏名])-Len([姓])-1)
Right関数の最初の引数は、文字列なので、氏名フィールドを設定します。
2つ目の引数は、右端からの文字数です。
この右端からの文字数を求めるのに、全体の文字数を求める必要があります。
全体の文字列数を求めるには、Len関数を使います。
そして、空白を見つけてもいいのですが、すでに、姓を見つけていますので、その文字数を減算し、さらに空白文字分の「-1」を追加した数式、
名: Right([氏名],Len([氏名])-Len([姓])-1)
とすることで、名を抽出することができます。
では、実行してみましょう。
1件目は、半角の空白、2件目は、全角の空白で区切られていますが、半角・全角に関わらず、分割されています。
ただし、空白が無い3件目のデータは、空白が見つけられないので、エラーがでます。
空白が無い場合という判断も、データによっては必要になります。
ISNUMBER関数
読み方： イズナンバー
分類： 情報
ISNUMBER(テストの対象)
対象が数値の場合にTRUEを返す
IF関数の作り方について、手入力ではなく、
ダイアログボックスをつかってた作り方を、改めて、確認していきます。
Excelには、ちょこちょこと、思っていたことと異なる判断をすることがあります。
次の表をつかって、そのケースをご紹介します。
C2には、
=IF(A2=B2,"○","×")
としています。
オートフィルで数式をコピーしています。
よく見ると、C5とC6の結果がおかしいことが、確認できます。
ひらがな と カタカナ
全角カタカナ と 半角カタカナ
これらは、同じと判断されていませんので、問題ありません。
ところが、
大文字と小文字は、異なっているハズなのに、同じと判定されてしまっています。
そして、
全角と半角のアルファベットは違うと判断されています。
このように、何気なく、IF関数だけで、入力されたデータが、合致するかどうか判断すると、間違った判断をされたものを見逃してしまう恐れがあります。
では、大文字と小文字は、異なると判断させるにはどうしたらいいのでしょうか。
そこで、文字を比較することができる、EXACT関数をIF関数と合わせてつかうことで、対応することができます。
IF+EXACT関数で数式を設定してみます。
=IF(EXACT(A2,B2),"○","×")
オートフィルで数式をコピーします。
今度は、
大文字と小文字も異なると判断されています。
このように、EXACT関数をつかうことで、文字種をきちんと区別して判断することができます。
ISNONTEXT関数
読み方： イズノンテキスト
分類： 情報
ISNONTEXT(テストの対象)
対象が文字列でない場合にTRUEを返す
Facebookページに書いた、Excelの豆知識（Trivia）です。
11月10日
Excel。
Ctrl＋D：上のセルを下にコピーする
11月11日
Excel。
Ctrl＋E：フラッシュ フィル
11月12日
Excel。
Ctrl＋F：検索と置換ダイアログボックスが表示されます。
検索タブ優先
11月13日
Excel。
Ctrl＋G：ジャンプダイアログボックスが表示されます。
セルにジャンプする
11月14日
Excel。
Ctrl＋H：検索と置換ダイアログボックスが表示されます。
置換タブ優先
11月15日
Excel。
Ctrl＋I：斜体にする
11月16日
Excel。
Ctrl＋K：ハイパーリンクダイアログボックスを表示
都道府県からはじまる住所のデータから、都道府県の列と住所の列にわけて抽出したいのですが、どのようにしたらいいのでしょうか。
次の表を用意しました。
関数を作ってもいいですが、今回は、Excel VBAでマクロを作ることにします。
ポイントは、C列の住所です。
関数でも同じですが、都道府県4文字問題をどのように対応したらいいかという点です。
都道府県4文字問題とは、神奈川県・和歌山県・鹿児島県の3県だけが4文字であるということです。
よって、C列は、左から4文字なのか3文字を除いた文字列である必要があります。
そして、住所なので、都道府県を除いた住所の文字数は決まっていません。
そこで、右から何文字という抽出方法では対応できません。
関数の場合は、文字数を算出して、都道府県の文字数分を引いた文字数を求めて、その数値をつかって右から抽出するような方法をつかったりします。
しかしながら、Excel VBAでは、都道府県の文字数を除いた文字数を求める必要はありません。
それを踏まえて、Excel VBAでプログラムをつくってみました。
Sub 都道府県と住所()
Dim i As Long
Dim lastrow As Long
Dim ad As String
Dim moji_ct As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
ad = Cells(i, "a")
If Mid(ad, 4, 1) = "県" Then
Cells(i, "b") = Left(ad, 4)
moji_ct = 5
Else
Cells(i, "b") = Left(ad, 3)
moji_ct = 4
End If
Cells(i, "c") = Mid(ad, moji_ct)
Next
End Sub
では、実行してみましょう。
それでは、プログラムを確認しておきましょう。
変数の宣言文です。
Dim i As Long
Dim lastrow As Long
Dim ad As String
Dim moji_ct As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
データの最終行の行番号をlastrowに代入します。この値まで、For To Next文で繰り返し処理をします。
そのFor文の中の処理ですが、
ad = Cells(i, "a")
adにA列の文字を代入します。
そして、都道府県4文字問題に対応するためにIF Then Else文で分岐処理をしています。
If Mid(ad, 4, 1) = "県" Then
Cells(i, "b") = Left(ad, 4)
moji_ct = 5
Else
Cells(i, "b") = Left(ad, 3)
moji_ct = 4
End If
Cells(i, "c") = Mid(ad, moji_ct)
もし4文字目が”県”ならば、左から4文字をB列に抽出します。
そして、moji_ctに5を代入させます。
このmoji_ctは何をしているのかといと、
C列の住所のためです。
Mid関数は、抽出文字数の指定をしなければ、その数値以降の文字列すべてを抽出することができます。
つまり、都道府県の文字数が4なので、次の文字数は5ということになるので、5を代入したというわけです。
Else以降は、それ以外の都道府県に対応させています。
このように、Mid関数だけで、右から何文字とか、気にしなくていいというわけです。
大きな表で、一番下のデータを見たいので、ジャンプする場合
Ctrl + ↓ というショートカットキーでジャンプできます。
だけど、マウスだけで、楽々ジャンプすることができます。
マウスカーソルが、移動の四方向の矢印で、ダブルクリック。
そして、Shiftキーも併せてつかってみると…
なかなか便利なテクニックです。
ISNA関数
読み方： イズエヌエー
分類： 情報
ISNA(テストの対象)
対象がエラー値の#N/Aの場合にTRUEを返す
次の表があります。
=C2/B2
と設定しています。
オートフィルで数式をコピーしたわけですが、当然、B3やB5など数値がない場合は、ゼロで除算していますので、#DIV/0!というエラーが表示されています。
個人ベースの資料で、原因が、わかっている場合、そのままでもいいかもしれません。
ただ、もしも、急きょ印刷して、会議資料として使うとなると、IFERROR関数などをつかって、数式を修正する必要があります。
しかしながら、急ぎなわけですね。
そこで、数式を修正しなくても、印刷した時に、エラーを非表示する方法があります。
ファイルタブの印刷でプレビューを確認すると、現状では、#DIV/0!が表示されていますので、印刷されてしまうわけです。
そして、OKボタンをクリックしたら、改めて、印刷プレビューを確認してみましょう。
このように、本来は、エラーが表示されないように、数式を設定しておくといいのですが、急な印刷という場合には、今回のような方法で、エラー対応することができます。
セル内改行のショートカットキーはAlt+Enterですが、表示形式内で改行したいときには、このAlt+Enterはつかえません。
それでは、どうしたらいいのでしょうか。
そこで、登場するショートカットキーがあります。
それが「Ctrl+J」。
知っていると便利なショートカットキーの一つです。
Facebookページに書いた、Excelの豆知識（Trivia）です。
11月3日
Excel。
TOROW関数
読み方： トゥロウ
分類： 検索/行列
TOROW(array,[ignore],[scan_by_column])
配列を1つの行として返します
11月4日
Excel。
VSTACK関数
読み方： ブイスタック
分類： 検索/行列
VSTACK(array1,[array2],…)
垂直方向に配列を1つの配列に積み重ね（スタック）する
11月5日
Excel。
WRAPCOLS関数
読み方： ラップコルズ
読み方： ラップカラムズ
分類： 検索/行列
WRAPCOLS(vector,wrap_count,[pad_with])
指定した数の値の後に列で折り返（ラップ）します
11月6日
Excel。
WRAPROWS関数
読み方： ラップロウズ
分類： 検索/行列
WRAPROWS(vector,wrap_count,[pad_with])
指定した数の値の後に行で折り返（ラップ）します
11月7日
Excel。
Ctrl＋A：全選択
11月8日
Excel。
Ctrl＋B：太字にする
11月9日
Excel。
Ctrl＋C：コピーする
Microsoft365のExcel。
Insider版に新しく「トリム参照」という考え方が追加されました。
C列の金額を算出するにあたり、スピル機能に対応した数式をつくってみました。
C2には、
=A2:A7*B2:B7
以前は、配列関数で設定しましたが、今は昔という感じになりましたね。
スピル機能をつかわなければ、
=A2*B2
と求めた後に、オートフィルで数式をコピーする必要がありましたが、スピル機能によりゴーストが発生するので、オートフィルで数式をコピーする必要はありません。
たしかに、ゴーストが発生するので、便利ではありますが、データが増えた場合のことを考慮して、C2の数式を
=A2:A10*B2:B10
としてみるとどうなるのでしょうか。
ただ、A8:B10に数量や単価が入力されていませんので、「0」が表示されています。
この「0」。
いりませんよね。
削除したくても、ゴーストなので、削除することができませんでした。
そこで、登場したのが、「トリム参照」というわけです。
=A2:A10*B2:B10
という数式を「トリム参照」を追加、修正してみます。
すると、「0」が消えました。
数値がないところがトリミングされたというわけです。
これが、「トリム参照」です。
「:.」としていますが、これは、Trim Trailing（トリムトレーリング）という種類になります。
「.:」は、Trim Leading（トリムリーディング）という種類にすると、先頭をトリミングしてくれます。
「.:.」は、Trim All（トリムオール）という種類もあって、Trim TrailingとTrim Leadingを組み合わせたのがTrim Allです。
このトリム参照の関数も登場しました、これが、TRIMRANGE関数です。
TRIMRANGE関数
読み方： トリムレンジ
分類： 検索/行列
TRIMRANGE(Range,[Row_trim_mode],[Col_trim_mode])
範囲または配列の外側のエッジからすべての空の行と列を除外します。
Trim_modeは、
３がデフォルトで「.:.」のTrim All。
1が、「:.」のTrim Trailing
2が、「.:」のTrim Leading
を設定することができます。
先程のトリム参照の数式をTRIMRANGE関数にしてみます。
このように、先程のトリム参照の結果と同じように「0」を表示されていないことが確認できます。
最近、スピル機能とか追加されたばかりというのに、さらに、トリム参照。
近いうちに通常のMicrosoft365にも追加されるかもしれません。
使ってみたい方は、Insider版にしてみるのもいいかと思います。
ISLOGICAL関数
読み方： イズロジカル
分類： 情報
ISLOGICAL(テストの対象)
対象が論理値の場合にTRUEを返す
データだけでは、わかりにくいので、グラフをつかって説明することもあります。
ただ、そのデータが、異なる単位やデータの数値が乖離している場合には、わかりにくいグラフになってしまうことがあります。
そこで、第2軸を表示した「複合グラフ」をつくってみるという方法があります。
今回は、第2軸を表示した複合グラフの作り方を確認していきます。
推移を確認するのにつかう折れ線グラフ。
どのぐらい成長しているのかというような、変動率をチェックするにも、折れ線グラフを使うことで、視覚的に確認することができます。
では、下記の表をつかって、通常の折れ線グラフをつくります。
A1:D4を範囲選択して、マーカー付き折れ線グラフをつくります。
グラフの外周はわかりやすいように、太い線にしております。
この折れ線グラフだと、推移はよくわかります。どのお店も右肩あがりです。
ただ、どのぐらい成長しているのでしょうか。
どの店舗が一番成長したのでしょうか。
そこで、変動率がわかるグラフに変更していきましょう。
グラフの縦軸をクリックします。
書式タブの「選択対象の書式設定」をクリックします。
軸のオプションにある
すると、折れ線グラフは、変動率の折れ線グラフに変更されました。
数値としては、横浜店が一番低いのですが、成長の面から見ると、変動率のグラフにすることで、横浜店の成長が良いことがわかります。
業績などの推移だけではなく、変動率の推移も確認することで、違った角度から確認することができます。
ISFORMULA関数
読み方： イズフォーミュラー
分類： 情報
ISFORMULA(参照)
セルに数式が含まれている場合にTRUEを返す
Facebookページに書いた、Excelの豆知識（Trivia）です。
10月27日
Excel。
EXPAND関数
読み方： エクスパンド
分類： 検索/行列
EXPAND(array,rows,[columns],[pad_with])
配列を指定した行数列数だけ拡大します
10月28日
Excel。
HSTACK関数
読み方： エイチスタック
分類： 検索/行列
HSTACK(array1,[array3],…)
水平方向に配列を2つの配列に積み重ね（スタック）する
10月29日
Excel。
TAKE関数
読み方： テイク
分類： 検索/行列
TAKE(array,rows,[columns])
配列から連続する指定された数の連続する行または列を返す
10月30日
Excel。
TEXTAFTER関数
読み方： テキストアフター
分類： 文字列操作
TEXTAFTER(text,delimiter,[instance_num],[ignore_case])
文字を区切った後のテキストを返す
10月31日
Excel。
TEXTBEFORE関数
読み方： テキストビフォー
分類： 文字列操作
TEXTBEFORE(text,delimiter,[instance_num],[ignore_case])
文字を区切る前のテキストを返す
11月1日
Excel。
TEXTSPLIT関数
読み方： テキストスピリット
分類： 文字列操作
TEXTSPLIT(text,col_delimiter,row_delimiter,ignore_empty,pat_with)
区切り記号を使用してテキストを行または列に分割する
11月2日
Excel。
TOCOL関数
読み方： トゥカル
読み方： トゥカラム
分類： 検索/行列
TOCOL(array,[ignore],[scan_by_column])
配列を1つの列として返します
発注する時に、個数ではなくて、注文単位が10個とかダース（12）でしなければならない場合に、イチイチ、必要単位で考えるのは面倒です。
そこで、CEILING.MATH関数をつかうことで、求めることができます。
次の表を用意しましたので、その表を使って説明していきます。
C列の不足分数は、基本在庫を100として管理しています。
100から在庫数を引いた数が、C列に表示されています。
C2は
=100-B2
という数式が設定しています。
オートフィルで数式をコピーしています。
求めたいのは、D列の発注数です。
本来なら、C列の不足分数を発注したいわけですが、10個ずつでないと注文できないルールになっています。
なので、C2が27と不足数が求められていますが、30で注文する必要があるというわけです。
20では、基本数100になりませんので、30注文する必要があるというわけです。
これを、目視で確認するのは大変です。
まして、ダースだったりすると計算するもの大変です。
そこで、CEILING.MATH関数を使うことをお勧めします。
では、D2にCEILING.MATH関数をつかった数式を設定してみましょう。
あとは、オートフィルで数式をコピーします。
これで、発注数を求めることができました。
不足分27が30になっていることが確認できます。
これで、イチイチ考えなくてもいいというわけです。
CEILING.MATH関数は、最初の引数が、数値です。
C2を設定します。
そして、2つ目の引数は、基準値です。
10ずつならば、10。ダースならば12とすればいいという仕組みの関数です。