Excel Tech-3 / 関数
セル内の文字数カウント
例えば、セルA1に"a\b\c"が入力されていていて、"\"がセルA1にいくつあるかは以下の式でカウントできます。
=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))
上記式の返り値は2となります。
【解説】
LEN関数は、文字列内の文字数を返します。
SUBSTITUTE関数は文字列中の指定された文字を他の文字に置き換えます。半角／全角、大文字／小文字が一致してないといけません。SUBSTITUTE関数は以下の引数をとります。
=SUBSTITUTE(文字列, 検索文字列, 置換文字列,
置換対象)
文字列、検索文字列、置換文字列については特に説明の必要がないと思います。
置換対象は、文字列に含まれる何番目の検索文字列（今回の場合、\）を置換文字列（今回の場合""空白）と置き換えるかを指定します。省略した場合は、文字列中のすべての検索文字列が置き換えの対象となります（今回の場合省略しているのですべてが対象となる）。
=SUBSTITUTE(A1,"\","")によって、セルA1内の"\"が""すなわち空白に変換され、返り値は"abc"。
"abc"なので、=LEN(SUBSTITUTE(A1,"\",""))の返り値は3。
=LEN(A1)は"a\b\c"の文字数、すなわち、返り値は5。
=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))は、=5-3、従って返り値は2となる。
[関数貼り付け]ダイアログボックスを表示
Shift+F3
【解説】
なにも入力されていないセルでShift+F3だと、[関数貼り付け]ダイアログボックスが表示されます。
既に関数が入力されているセルでShift+F3（Shiftキーを押しながらファンクションキーのF3を押す）とすると、数式パレットが表示されます。
[関数貼り付け]ダイアログボックスは、[標準]ツールバーにある[関数貼り付け]ボタンをクリックでも表示できます。
数式パレット内でもF3キー（ファンクションキーのF3）で、既に定義した名前を貼り付けることができます。
数式パレットを使って引数を入力
Ctrl+A
【解説】
数式パレットを使って引数を入力するには、例えばセルに「=VLOOKUP」まで入力してから、Ctrl+A（Ctrlキーを押しながらAを押す）とします。
引数のことを英語で"Argument"といいます。Argumentの"A"と覚えましょう。
数式バーの[数式の編集]ボタンでも数式パレットが表示されます。
セルA1など、数式パレットの下に隠れているセルを選択するには、数式パレットの引数となるセル参照を入力または表示するボックスの右にある[ダイアログ縮小]ボタンをクリックします。
そうすると、数式パレットが縮小されて、セルA1なども表示されるようになります。もう一度ボタンをクリックまたはEnterキーを押すと数式パレットが表示されます。
数式パレット内でもF3キー（ファンクションキーのF3）で、名前を貼り付けることができます。
関数の引数を表示
Ctrl+Shift+A
【解説】
関数の引数を表示するには、例えばセルに「=VLOOKUP」まで入力してから、Ctrl+Shift+A（CtrlキーとShiftキーを押しながらAを押す）とします。すると、「=VLOOKUP(検索値,範囲,列番号,検索の型)」というようにどういう引数が入るかが表示されます。
Ctrl+Shift+Aとすると、その関数の最初の引数が選択された状態にあるので、そのままF3で名前を貼り付けたり、セル参照を直接入力することができます。二番目の引数以下については、マウスで選択してF3キーを押します。
マウスを使わないで矢印キーなどで二番目の引数を選択するにはいったんF2キーを押して編集モードにしてから行います。入力モードか編集モードかは、ステータスバーの左に表示されています。
成績を5段階評価するには？
【質問】各科目と合計の点数を以下のような5段階で評価を算出したい。
上位10%： 5
10〜30%： 4
30〜70%： 3
70〜90%： 2
90〜100%：1
セルA1:F11に以下のようにデータが入っているとします。
| Ａ | Ｂ | Ｃ | Ｄ | Ｅ | Ｆ | |
|---|---|---|---|---|---|---|
| 1 | 国語 | 算数 | 理科 | 社会 | 合計 | |
| 2 | Ａ君 | 76 | 23 | 18 | 34 | 151 |
| 3 | Ｂ君 | 98 | 94 | 24 | 65 | 281 |
| 4 | Ｃ君 | 38 | 16 | 46 | 28 | 128 |
| 5 | Ｄ君 | 16 | 73 | 37 | 97 | 223 |
| 6 | Ｅ君 | 75 | 95 | 55 | 43 | 268 |
| 7 | Ｆ君 | 42 | 64 | 84 | 23 | 213 |
| 8 | Ｇ君 | 65 | 95 | 46 | 34 | 240 |
| 9 | Ｈ君 | 84 | 34 | 31 | 54 | 203 |
| 10 | Ｉ君 | 35 | 65 | 62 | 26 | 188 |
| 11 | Ｊ君 | 16 | 72 | 97 | 76 | 261 |
【回答】
1.セルG2に以下の式を入力
=MATCH(PERCENTRANK(B$2:B$11,B2),{0,0.1,0.3,0.7,0.9})
2.セルG2をコピー（Ctrl+C）
3.G2:K11を選択
4.貼り付け（Ctrl+V）
G2:K11に以下のように評価が表示されます。
| G | H | I | J | K | |
|---|---|---|---|---|---|
| 2 | 4 | 2 | 1 | 3 | 2 |
| 3 | 5 | 4 | 2 | 4 | 5 |
| 4 | 3 | 1 | 3 | 2 | 1 |
| 5 | 1 | 3 | 3 | 5 | 3 |
| 6 | 3 | 4 | 3 | 3 | 4 |
| 7 | 3 | 3 | 4 | 1 | 3 |
| 8 | 3 | 4 | 3 | 3 | 3 |
| 9 | 4 | 2 | 2 | 3 | 3 |
| 10 | 2 | 3 | 4 | 2 | 2 |
| 11 | 1 | 3 | 5 | 4 | 4 |
【解説】
セルG2に入っている以下の式を解説します。
=MATCH(PERCENTRANK(B$2:B$11,B2),{0,0.1,0.3,0.7,0.9})
まずは、PERCENTRANK関数から。
■PERCENTRANK(配列, x, 有効桁数)
「配列に含まれる値の中で、百分率を使った
x の順位を返します。PERCENTRANK 関数は、1組のデータの中で値の相対的な位置を計算するために利用します。たとえば、あるテストの成績が
70点である人の相対的な順位を計算するために、PERCENTRANK
関数を使います。」
PERCENTRANK関数の各引数の説明です。
□配列：相対的な位置を決定するため、数値データを含む配列またはセル範囲を指定します。
この式では、B$2:B$11に入っている国語の点数です。
列番号の前に"$"マークがついていますが、これは行だけ絶対参照という意味です。行だけ絶対参照にしておくことで、セルG2の式を例えばすぐ下のG3にコピーしても、B$2:B$11のままにすることが可能です。一方、右隣のH2にコピーすると、C$2:C$11のように参照する列だけが一列ずれます。
□x：相対的な順位を調べる値を指定します。
この式では、B2です。Ａ君の国語の点数「76」です。
□有効桁数：省略可能な引数で、計算結果として返される百分率の有効桁数を指定します。有効桁数を省略すると、小数点以下第三位(0.xxx%)まで計算されます。
この式では、省略しています。
次にMATCH関数の説明です。
■MATCH(検査値, 検査範囲, 照合の型)
「指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の相対的な位置を表す数値を返します。」
MATCH関数の各引数の説明です。
□検査値：表の中で必要な項目を検索するために使用する値を指定します。
この式では、PERCENTRANK(B$2:B$11,B2)の結果である「0.777」です。
□検査範囲：検査値を含む隣接したセル範囲を指定します。検査範囲は、配列または配列に対するセル参照を指定してもかまいません。
この式では、{0,0.1,0.3,0.7,0.9}という配列です。
評価 1 は下位10%までなので「0.1」、評価
2 は下位30%（10%+20%）までなので「0.3」、評価
3 は下位70%（10%+20%+40%）．．．と、それぞれ下位からの累計値を意味しています。
□照合の型：-1、0、1 の数値のいずれかを指定します。照合の型には、検査範囲の中で検査値を探す方法を指定します。
この式では、省略しています。
照合の型を省略すると、1 であると見なされます。
照合の型に 1 を指定すると、検査値以下の最大の値が検索されます。このとき検査範囲は、0、1、2のように昇順で並べ替えられておく必要があります。
検査値「0.777」以下の検査範囲{0,0.1,0.3,0.7,0.9}内の最大の値は、4番目の「0.7」です。
従って、セルG2の
=MATCH(PERCENTRANK(B$2:B$11,B2),{0,0.1,0.3,0.7,0.9})
の返り値は「4」となります。
実際に、この式を使う場合には、評価の区分をどうしているか（以上／超、以下／未満）に注意する必要があります。
今回の場合には以下のような区分になっています。
上位10%以下：5
10%超30%以下：4
30%超70%以下：3
70%超90%以下：2
90%超100%以下：1
全角／半角スペースで分けられた姓名から姓だけを取り出す
■全角スペースで分けられている場合
=LEFT(A1,FIND(" ",A1)-1)
＊「" "」は全角スペースです。
■半角スペースで分けられている場合
=LEFT(A1,FIND(" ",a1)-1)
＊「" "」は半角スペースです。
【解説】
「全角スペースで分けられている場合」で解説いたします。
セルA1に全角スペースで分けられた姓名、例えば「鈴木 太郎」という名前が入っているとします。
セルB1に =LEFT(A1,FIND(" ",A1)-1) と入力すれば、セルB1には「鈴木」と姓だけが表示されます。
まずは、FIND関数から説明いたします。
FIND関数は、FIND(検索文字列, 対象, 開始位置)という引数をとります。検索する文字（文字列）が対象の文字列の何番目にあるのかを返します。
「開始位置」では、検索を開始する位置を指定します。対象の先頭文字から検索を開始するときは
1 を指定し、3 文字目から開始するときは 3
を指定します。開始位置 を省略すると、1を指定したと見なされ、対象の先頭文字から検索が始まります。今回、開始位置を省略しています。
=FIND(" ",A1) は、セルA1の文字列の中で全角スペースは何文字目にあるかを返すわけです。この例の場合、全角スペースは三番目にありますので、返り値は「3」となります。
次にLEFT関数。
LEFT 関数は、文字列の先頭から指定された数の文字を返します。LEFT(文字列,
文字数)という引数をとります。
上記FIND関数の返り値「3」をそのまま使って、
=LEFT(A1,3) とすると、「鈴木 」と全角スペースまで引っ張ってきますので、
=LEFT(A1,3-1)と「-1」をくっつけているわけです。
全角／半角スペースが混在している場合には
■ =LEFT(A1,FIND(" ",SUBSTITUTE(A1,"
"," "))-1)
【解説】
A1:A10に姓名が入力されているとして、全部が全角スペースで分けられている場合には、セルB1に =LEFT(A1,FIND(" ",A1)-1) を入力してB10までコピーで全てうまくいきます。
しかし、A1:A10の中に半角スペースで分けられた姓名がある場合には、#VALUE!というエラーになります。
今回ご紹介するのはSUBSTITUTE関数を使って「全角／半角スペースが混在した場合にも対応した式」です。
=LEFT(A1,FIND(" ",SUBSTITUTE(A1,"
"," "))-1)
全角スペースのみに対応した式との違いは、SUBSTITUTE(A1,"
"," ") です。
セルA1に半角スペースで分けられた姓名「鈴木
太郎」があるとして、=SUBSTITUTE(A1,"
"," ") の返り値は「鈴木 太郎」（全角スペース）となります。
全角／半角スペースで分けられた姓名から名前だけを取り出す
■全角スペースで分けられている場合
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
＊「" "」は全角スペースです。
■半角スペースで分けられている場合
=RIGHT(A1,LEN(A1)-FIND(" ",a1))
＊「" "」は半角スペースです。
■全角／半角スペースが混在している場合
=RIGHT(A1,LEN(A1)-FIND(" ",SUBSTITUTE(A1,"
"," ")))
【解説】
全角スペースで分けられている場合で解説いたします。セルA1には「鈴木 太郎」と入っているとします。
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
LEN関数は文字数を返します。=LEN(A1) すなわち =LEN("鈴木 太郎") の返り値は、「5」となります。
=FIND(" ",A1)の返り値は「3」なので、=LEN(A1)-FIND(" ",A1) の返り値は
=5-3 で「2」となります。
RIGHT関数は、文字列の末尾 (右端) から指定された文字数の文字を返します。
すなわち、=RIGHT(A1,2) は、文字列「鈴木 太郎」の右端から2文字目までを返すので、「太郎」が返り値となります。
全角／半角スペースで分けられた姓名を[区切り位置]で姓と名前に分ける
A1:A10に全角／半角スペースで分けられた姓名が入っているとします。
1.A1:A10を選択
2.メニュー[データ]-[区切り位置]
3.「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」
にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[区切り文字]のところで、「スペース」にチェックを入れる
5.[完了]ボタンをクリック
これで、A列に姓、B列に名前となります。
【解説】
B1:B10に何かデータが入っている状態で上記操作を行うと、5の[完了]ボタンを押したところで、「コピーまたは移動先のセルの内容を置き換えますか？」というメッセージが表示されます。[OK]ボタンを押すとB1:B10に名前が上書きされてしまいます。右の列にデータが何も入っていない状態で上記操作を行った方が良いでしょう。
分けられた姓と名前を関数でくっつける
セルA1に姓：「鈴木」、セルB1に名前：「太郎」が入っているとします。
例えば、セルC1に
=A1&B1
または
=CONCATENATE(A1,B1)
と入力すると、「鈴木太郎」と表示されます。
間に全角スペースを入れて「鈴木 太郎」のようにしたい場合には下記のようにします。
=A1&" "&B1
または
=CONCATENATE(A1," ",B1)
住所を都道府県名とそれ以降に分けるには？
【問題】住所録を作っていて、入力全部終わってから、「やっぱり都道府県いらない」といわれました。どうすれば、住所を都道府県名とそれ以降に分けることができるでしょうか？
【回答】
A1に住所が入っているとして、以下の式を例えばB1に入力し、必要な行までコピーします。
=RIGHT(A1,LEN(A1)-4+SUM((MID(A1,3,1)={"都","道","府","県"})*1))
C1に以下の式を入力し、必要な行までコピーします。
=LEFT(A1,LEN(A1)-LEN(B1))
これで、例えばA1に「東京都世田谷区」と入っている場合には、B1に「世田谷区」、C1に「東京都」と表示されます。
A1に「神奈川県横浜市」と入っている場合には、B1に「横浜市」、C1に「神奈川県」と表示されます。
【解説】
まずは、都道府県以降を取り出す関数から。
=RIGHT(A1,LEN(A1)-4+SUM((MID(A1,3,1)={"都","道","府","県"})*1))
47都道府県が全て三文字であれば、単純に
=RIGHT(A1,LEN(A1)-3)
でいいのですが、神奈川県、和歌山県、鹿児島県は四文字です。
セルA1に「東京都世田谷区」が入っているとして、例えばセルD1に
=MID(A1,3,1)={"都","道","府","県"}
を入力してから、数式バーをクリックしてF9キーを押すと
={TRUE,FALSE,FALSE,FALSE}
と表示されます。これは、セルA1の三文字目が
”都”であるのでTRUE
”道”ではないのでFALSE
”府”ではないのでFALSE
”県”ではないのでFALSE
を返しているわけです。
続いてセルE1に
=(MID(A1,3,1)={"都","道","府","県"})*1
を入力してから、数式バーをクリックしてF9キーを押してみましょう。
={1,0,0,0}
というふうになっています。TRUEやFALSEは加減乗除されると、それぞれ「1」、「0」として扱われます。
=SUM((MID(A1,3,1)={"都","道","府","県"})*1)
という式は、三文字目が"都","道","府","県"のいずれかであれば、「1」を返します。
=LEN(A1)-4+SUM((MID(A1,3,1)={"都","道","府","県"})*1)
で、三文字目が”都道府県”であれば、LEN(A1)-3、そうでない場合（神奈川県、和歌山県、鹿児島県）はLEN(A1)-4
分の文字数をA1の右から取り出すようにしているわけです。
セルC1に入力した式
=LEFT(A1,LEN(A1)-LEN(B1))
は都道府県名だけを返します。
住所が入ったセル(A1)の左端から数えて、住所全体の文字数「LEN(A1)」から都道府県を除いた文字数「LEN(B1)」分の文字数を返しているわけです。
セル内の単語数カウント
【問題】
セルA1に「【エクセル技道場】はエクセルの技を毎日配信しています。」という文章が入っています。
セルA1に”エクセル”という単語がいくつ含まれているかをカウントするには？
【回答】
=(LEN(A1)-LEN(SUBSTITUTE(A1,"エクセル","")))/LEN("エクセル")
【解説】
例えば、セルA1に"a\b\c"が入力されていていて、"\"がセルA1にいくつあるかは以下の式でカウントできます。
=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))
今回の「セル内の単語数カウント」はこれの応用です。
=(LEN(A1)-LEN(SUBSTITUTE(A1,"エクセル","")))
で、セルA1の文字列から”エクセル”という文字列を除いた文字数（19）をセルA1の文字数（27）から引いています。これで返り値は8（=27-19）となります。
これを =LEN("エクセル") の返り値4で割って、単語数2という答えがでてきます。
検索条件に完全一致するセルの個数カウント
【問題】
セルA1:A5に以下が入力されているとします。"晴れ"が入っているセルの個数をカウントするにはどういう数式を使うでしょうか？
晴れ
くもり
晴れ
雨
くもり
【回答】
=COUNTIF(A1:A5,"晴れ")
【解説】
=COUNTIF(A1:A5,"晴れ")の結果は2です。
COUNTIF関数は、指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。COUNTIF(範囲,
検索条件) という書式です。
検索条件に部分一致するセルの個数カウント
【問題】
セルA1:A5に以下が入力されているとします。セル内の一部に"晴れ"が入っているセルの個数をカウントするにはどういう数式を使うでしょうか？
晴れ、時々雨
くもり後、晴れ
晴れ、ところにより雨
雨
くもり
【回答】
=COUNTIF(A1:A5,"*晴れ*")
【解説】
ワイルドカード文字を使用して部分一致するセルの個数カウントをすることができます。
ワイルドカード文字には、?（疑問符）と*（アスタリスク）の二つがあります。
? (疑問符)は、同じ位置にある任意の1文字を意味します。
* (アスタリスク)は、同じ位置にある任意の数の文字を意味します。
例えば、今回の問題であれば、以下のようになります。
=COUNTIF(A1:A5,"*晴れ*") →"晴れ"の前後に任意の数の文字があるセルをカウントするので、返り値は3。
=COUNTIF(A1:A5,"*晴れ") →"晴れ"の前に任意の数の文字があるセルをカウントするので、返り値は1。
=COUNTIF(A1:A5,"晴れ*") →"晴れ"の後に任意の数の文字があるセルをカウントするので、返り値は2。
文字列が入っているセルの個数カウント
【問題】
セルA1:A5に以下が入力されているとします。文字列のセルの個数（3個）をカ
ウントするにはどういう数式を使うでしょうか？
文字
1
moji
もじ
2
【回答】
=COUNTA(A1:A5)-COUNT(A1:A5)
【解説】
COUNTA関数は、範囲内の数値、論理値、文字列、またはエラー値が入っているセルの個数、すなわち空白以外のセルの個数を返します。
従って、=COUNTA(A1:A5) の返り値は‖5 となります｜
COUNT関数は、範囲内の数値が入っているセルの個数を返します。
従って、=COUNT(A1:A5) の返り値は‖2 となります｜
5-2=3 ということで a1:a5内の文字列が入っているホルの個数
3 という結果になります。
文字列が入っているセルの個数カウント２
【問題】
セルA1:A5に以下が入力されているとします。文字列のセルの個数（2個）をカウントするにはどういう数式を使うでしょうか？
3番目のTRUEは論理値です。例えば、セルに
=1=1 と入力すると‖論理値trueが表示されます｜
5番目の#DIV/0!はエラー値です。エラー値
#DIV/0! は、数式で 0 (ゼロ) による除算が行われた場合に返されます。例えば、セルに
=1/0 と入力するとトラー値 #div/0! が表示されます｜
文字
1
TRUE
もじ
#DIV/0!
【回答】
=COUNTIF(A1:A5,"*")
【解説】
COUNTIF関数は、指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。
ワイルドカード文字 *（アスタリスク）は、任意の数の文字を意味します。
範囲内で任意の数の文字が入っているセルをカウントして、
=COUNTIF(A1:A5,"*")
の返り値は 2 となります。
前回の =COUNTA(A1:A5)-COUNT(A1:A5) を使うと‖結果は
4 になります。
COUNTA関数は、範囲内の数値、論理値、文字列、またはエラー値が入っているセルの個数、すなわち空白以外のセルの個数を返します。
従って、=COUNTA(A1:A5) の返り値は‖5 となります｜
COUNT関数は、範囲内の数値が入っているセルの個数を返します。
従って、=COUNT(A1:A5) の返り値は‖1 となります｜
従って、=COUNTA(A1:A5)-COUNT(A1:A5) は
5-1=4 となるのです｜
=COUNTA(A1:A5)-COUNT(A1:A5) は‖正確に言うとa1:a5内の文字列‖論理値‖トラー値が入っているホルの個数をニテントするということになります｜
ただし、範囲内に文字列と数値しかない（数式を使っていない）場合には、
=COUNTA(A1:A5)-COUNT(A1:A5) のような数式で‖範囲内で文字列が入っているホルの個数を正しくニテントできます｜
特定の数の文字列が入っているセルの個数カウント
【問題】
セルA1:A6に以下が入力されているとします。
1文字のセルの個数（"雨","雪","雷"で3）をカウントするにはどういう数式を使うでしょうか？
2文字のセルの個数（"台風","晴れ"で2）をカウントするにはどういう数式を使うでしょうか？
3文字のセルの個数（"くもり"で1）をカウントするにはどういう数式を使うでしょうか？
台風
雨
雪
晴れ
くもり
雷
【回答】
=COUNTIF(A1:A6,"?")
=COUNTIF(A1:A6,"??")
=COUNTIF(A1:A6,"???")
【解説】
ワイルドカード文字を使用して部分一致するセルの個数カウントをすることができます。ワイルドカード文字には、?（疑問符）と*（アスタリスク）の二つがあります。
* (アスタリスク)は、同じ位置にある任意の数の文字を意味します。
? (疑問符)は、同じ位置にある任意の1文字を意味します。
従って、例えば、=COUNTIF(A1:A6,"?")
は、A1:A6内の1文字だけのセルの個数をカウントします。
特定の数より大きい数値が入っているセルの個数カウント
【問題】
セルA1:A5に以下が入力されているとします。正の数のセルの個数（3個）をカウントするにはどういう数式を使うでしょうか？
-1
0
1
2
3
【回答】
=COUNTIF(A1:A5,">0")
【解説】
COUNTIF関数は、指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。
=COUNTIF(範囲, 検索条件) という書式をとります｜
検索条件に今回の問題のように、式を指定する場合は、">0"のように、半角のダブルクォーテーション
(") で囲む必要があります。検索条件が数値の場合にはその必要がありません。
今回の問題は正の数のカウントですが、その他の場合は以下のような式になります。
負の数 =COUNTIF(A1:A5,"<0")
0以上 =COUNTIF(A1:A5,">=0")
0以下 =COUNTIF(A1:A5,"<=0")
0 =COUNTIF(A1:A5,0)
2以上 =COUNTIF(A1:A5,">=2")
他のセルの数値以上の数値が入っているセルの個数カウント
【問題】
セルA1:A5に以下が入力されているとします。
セルB1に入っている数値以上のセルの個数をカウントするにはどういう数式を使うでしょうか？
-1
0
1
2
3
【回答】
=COUNTIF(A1:A5,">="&B1)
【解説】
例えば、セルB1に1を入力すれば、1以上の数値が入っているセルの個数
3 が返ります。
このようにCOUNTIF関数の検索条件にセルを参照する時には、>=
などの比較演算子を半角のダブルクォーテーション
(") で囲み、文字列演算子 & (アンパサンド)でセル参照と組み合わせます。
特定の数値以外の数値が入っているセルの個数カウント
【問題】
セルA1:A5に以下が入力されているとします。
0以外の数値が入っているセルの個数をカウントするにはどういう数式を使うでしょうか？
【回答】
=COUNTIF(A1:A5,"<>0")
【解説】
COUNTIF関数の検索条件に比較演算子 <>
(不等号) を使用することで、特定の数値以外の数値が入っているセルの個数カウントをすることができます。
比較演算子には以下の6種類があります。
= (等号)
> (〜より大きい)
< (〜より小さい)
>= (〜以上)
<= (〜以下)
<> (不等号)
特定の範囲の数が入っているセルの個数カウント
【問題】
セルA1:A5に以下が入力されているとします。
1以上3未満の数値が入っているセルの個数をカウントするにはどういう数式を使うでしょうか？
-1
0
1
2
3
【回答】
=COUNTIF(A1:A5,"<3")-COUNTIF(A1:A5,"<1")
【解説】
3未満の数値が入っているセルの個数から、1未満の数値が入っているセルの個数を引くことで1以上3未満の数値が入っているセルの個数をカウントします。
特定セルの数式表示
【問題】
A列には計算結果をB列にはその数式を表示するには、どうしたら良いでしょうか？
【回答】
マクロでユーザー定義関数を作成します。
1.Alt+F11 (メニュー[ツール]-[マクロ]-[VBE])
→VBEが起動
2.メニュー[挿入]-[標準モジュール]
→プロジェクトエクスプローラに[Module1]といった形で標準モジュールが追加／[Module1]のコードウィンドウが右に表示される。
3.コードウィンドウに以下のコードを入力
Function 数式表示(Rng As Range) As String
数式表示 = rng.formula
End Function
上記手順を行った後、ワークシートで例えば、以下のように入力します。
=数式表示(A2)
【解説】
4.0マクロ関数でやる場合は以下の手順です。
まずは、名前定義です。
1.セルB2を選択
2.Ctrl＋F3 (メニュー[挿入]-[名前]-[定義])
3.[名前]に例えば 数式表示2 と入力
4.[参照範囲]に =GET.CELL(6,!A2)&T(NOW())
と入力
5.Enter
これで例えば、セルB2に =数式表示2 と入力すると‖ホルa2に入力されている数式が表示されます｜
引数としてセル参照をとることができません。この例だと常に左のセルを参照するようになってます。
[参照範囲]のところで !A2 としているのは、名前定義を行ったシート以外でもそのシートの左のセルを参照させるためです。
&T(NOW()) は再計算対策です。
年度始の日付を求める
【問題】
今日現在の年度始（4月1日）の日付を求めるにはどういう数式にすれば良いでしょうか？
例えば以下のような感じです。
今日 年度始
1999/10/10 → 1999/4/1
2000/1/23 → 1999/4/1
2000/3/31 → 1999/4/1
2000/4/1 → 2000/4/1
2000/10/10 → 2000/4/1
【回答】
=DATE(YEAR(TODAY())-(MONTH(TODAY())<=3),4,1)
【解説】
2000/3/31までは1999年度、2000/4/1から2000年度になります。
単に =DATE(YEAR(TODAY()),4,1) とすると‖2000/1/1から2000/3/31の馬度始が2000/4/1と翌馬度の馬度始の日付となります｜
そこで、 -(MONTH(TODAY())<=3) という式をかませています｜
MONTH(TODAY())<=3 で今日の月が3以下であれば
TRUE を、それ以外であれば FALSEを返します。
論理値 TRUE/FALSE は算術演算子とともに使われるとそれぞれ数値
1, 0 に変換されることを利用し、 YEAR(TODAY())-(MONTH(TODAY())<=3)
で、今日の月が3以下であれば 今日の年から
-1 することで、年度を調整しているわけです。
満年齢の算出
【問題】
A列に誕生日が入っています。例えばセルA2に入っている誕生日の人の今日現在の年齢を算出するにはどういう数式を使えばよいでしょうか？
【回答】
=DATEDIF(A2,TODAY(),"Y")
【解説】
DATEDIF関数は、指定された期間内の日数、月数、または年数を返します。
DATEDIF(開始日,終了日,単位)という引数をとります。
[開始日]は[終了日]より前の日付でなければいけません。[終了日]が[開始日]より前の日付の場合にはエラーとなります。
[単位]で戻り値の種類を指定します。
"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の満日数
"MD" 1ヶ月未満の日数
"YM" 1年未満の月数
"YD" 1年未満の日数
DATEDIF関数は、Lotus 1-2-3 関数との互換性を保つために用意されています。[関数ウィザード]
の「関数名一覧」には含まれていません。
DATEDIF関数は、[開始日]が閏年か平年かで違った値を返します。
=DATEDIF("1998/2/1","1999/3/1","YD")
は 28（平年−平年）
=DATEDIF("1999/2/1","2000/3/1","YD")
は 28（平年−閏年）
=DATEDIF("2000/2/1","2001/3/1","YD")
は 29（閏年−平年）
=DATEDIF("2000/2/1","2004/3/1","YD")
は 29（閏年−閏年）
生年月日から学年を自動表示
【問題】
A列に生年月日が入っています。B列に現在時点での学年を表示させるにはどうしたら良いでしょうか？
B列には、未就学児、小１、小２、・・・大３、大４というふうに表示させます。
例えば、本日（2000年10月）時点で、以下のように表示したいのです。
1978/9/1 大４
1984/9/1 高１
1985/9/1 中３
1994/4/1 小１
1994/4/2 未就学児
それから、A列のセルに既に大学を卒業している人の誕生日が入力されている、または未入力の場合は空白が返ってくるようにします。
【回答】
1.Sheet1のA1:B19に以下のリストを作成します。
年齢 学年
0 未就学児
6 小１
7 小２
8 小３
9 小４
10 小５
11 小６
12 中１
13 中２
14 中３
15 高１
16 高２
17 高３
18 大１
19 大２
20 大３
21 大４
22 =""
2.A1:B19を選択して、名前ボックスに「LIST」と入力
3.続いてSheet2のセルD1に以下の式を入力
=DATE(YEAR(TODAY())-(MONTH(TODAY())<=3)*1,4,1)
4.A2に生年月日が入力されているとして、B2に以下の式を入力
=VLOOKUP(DATEDIF(A2,$D$1,"Y"),LIST,2,TRUE)
5.必要な行までコピー
これで、以下のようになります。
1976/9/1
1977/9/1
1978/9/1 大４
1979/9/1 大３
1980/9/1 大２
1981/9/1 大１
1982/9/1 高３
1983/9/1 高２
1984/9/1 高１
1985/9/1 中３
1986/9/1 中２
1987/9/1 中１
1988/9/1 小６
1989/9/1 小５
1990/9/1 小４
1991/9/1 小３
1992/9/1 小２
1993/9/1 小１
1994/3/31 小１
1994/4/1 小１
1994/4/2 未就学児
1994/9/1 未就学児
1995/9/1 未就学児
1996/9/1 未就学児
【解説】
=DATE(YEAR(TODAY())-(MONTH(TODAY())<=3)*1,4,1)
上記式で今年度の年度始の日付 2000/4/1 が算出されます。
=DATEDIF(A2,$D$1,"Y")
上記式で誕生日を開始日、今年度の年度始の日付を終了日とする満年数が算出されます。例えば
1988/9/1 がセルA2に入力されていると、1988/9/1
と年度始 2000/4/1 の満年数として 11 が返り値となるわけです。
VLOOKUP関数は、VLOOKUP(検索値, 範囲, 列番号,
検索の型) という引数をとります。
指定された[範囲]の左端の列で[検索値]を検索し、[範囲]内の対応する[列番号]セルの値を返します。
[検索の型]では、[検索値]と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値で指定します。
TRUE または 1 を指定するか省略すると、検索値
が見つからない場合に、検索値 未満で最も大きい値が使用されます。
FALSE または 0 を指定すると、検索値 と完全に一致する値だけが検索され、見つからない場合は
エラー値 #N/A が返されます。
=VLOOKUP(11,LIST,2,TRUE)
上記式の場合には LISTの左端の列の11に対応したLISTの2列目の
小６ が返ってくるわけです。
=VLOOKUP(4,LIST,2,TRUE)
上記式の場合にはLISTの左端の列に 4 がないので、4未満で最も大きい値、すなわち
0 に対応したLISTの2列目の 未就学児 が返ってくるわけです。
=VLOOKUP(23,LIST,2,TRUE)
上記式の場合にはLISTの左端の列に 23 がないので、23未満で最も大きい値、すなわち
22 に対応したLISTの2列目の "" が返ってくるわけです。
それでは、セルA2が未入力の場合はどうなるでしょうか？
=DATEDIF(A2,$D$1,"Y")
シリアル値 1 は1900/1/1 となり、0は1900/1/0
となります。従って、
=DATEDIF(0,"2000/4/1","Y")
の返り値 100 がVLOOKUP関数の検索値となり、
=VLOOKUP(100,LIST,2,TRUE) となります｜
上記式の場合にはLISTの左端の列に 100 がないので、100未満で最も大きい値、すなわち
22 に対応したLISTの2列目の "" が返ってくるわけです。
サンプルファイルはこちらです。
数字で始まる部分から区分
【問題】
セルA1に入力された住所を丁目、番地などの手前までで区切るにはどういう数式にすれば良いでしょうか？。
つまり、文字列と数字で始まる部分で分けたいのです。
住所は市町村郡などさまざまです。番地は全角数字だったり半角数字だったりします。
例えば以下のようにしたいのです。
現在市過去区未来３−１ → 現在市過去区未来 と ３−１
現在市過去区８丁目２２番 → 現在市過去区未来 と １丁目１番
現在郡1234番地 → 現在郡 と 1234番地
【回答】
セルA1に住所が入っているとして、
セルB1に
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)
セルC1に
=RIGHT(A1,LEN(A1)-LEN(B1))
または
=RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890)-1))
【解説】
セルA1に 現在市過去区未来３−１ が入っているとして、セルB1の
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)
を解説します。
=ASC(A1)
ASC関数：文字列内の全角の英数カナ文字を、半角文字に変換します。それ以外の文字は変換されません。
これで、丁目、番地が全角で入力されていても半角に変換することで、全角／半角どちらにも対応可能になります。
現在市過去区未来３−１ は 現在市過去区未来3-1
となります。
=ASC(A1)&1234567890
英数カナ文字が半角文字に変換された住所の後ろに
1234567890 をくっつけています。これはエラー対策のためです。
現在市過去区未来3-1 に 1234567890 がくっつけられて、現在市過去区未来3-11234567890となります。
=FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890)
FIND関数：指定された文字列 (検索文字列)
を他の文字列 (対象) の中で検索し、その文字列が他の文字列内で最初に現れる位置を左端から数え、その番号を返します。検索文字列
が 対象 の中で見つからない場合、エラー値
#VALUE! が返されます。
この式で「検索文字列」は{0,1,2,3,4,5,6,7,8,9}、「対象」は
現在市過去区未来3-11234567890 です。
例えば =FIND({0,1,2,3,4,5,6,7,8,9},"現在市過去区未来3-11234567890")
という式を入力して、F2キーを押してF9キーを押してください。 数式バーに
={21,11,13,9,15,16,17,18,19,20}
と表示されます。0から9まででFIND処理をした結果を返しています。
現在市過去区未来3-11234567890 で0は 21文字目、1は11文字目、2は13文字目、3は9文字目といった感じです。
なお、F9キーを押した後、Enterを押すとセルは式の結果となり、エスケープキー（ESC）を押すと元の式に戻ります。
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))
MIN関数：引数リストに含まれる最小の数値を返します。引数には、数値、空白セル、論理値、または数値を表す文字列を指定することができます。エラー値または数値に変換できない文字を指定するとエラーになります。
=FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890)
の返り値 ={21,11,13,9,15,16,17,18,19,20}
の最小値 9 が返り値となります。
すなわち、文字列内で最初に数字があるのは9文字目ということです。
1234567890をつけないで、
=FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1))
としていたら、
={#VALUE!,11,#VALUE!,9,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
となります。
MIN関数は引数にエラー値をとるとエラーになりますので、
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)))
の返り値は #VALUE! となってしまうのです。
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)
=LEFT(A1,8) ということで‖ホルa1の文字列
現在市過去区未来３−１ の最初に数字がある9文字目の一つ前までを返します。
結果は、 現在市過去区未来 となります。
セルC1の
=RIGHT(A1,LEN(A1)-LEN(B1))
について解説します。
RIGHT関数：文字列の末尾 (右端) から指定された数の文字を返します。
この式だと、LEN(A1)-LEN(B1)は11-8=3なんでセルA1の文字列の末尾から3文字分を返し、結果は
３−１ となります。
セルB1の式を以前は以下のようにしていました。
=LEFT(A1,MIN(IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1))),FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1))-1,LEN(A1))))
エラー対策を&1234567890とした結果、こんなに短くなりました。(^o^)丿
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)
住所を市、区、郡で分ける関数にトライしたことがあります。
セルB1に住所が入っているとして以下です。ただし、政令都市以外の区で区分したりと完璧ではありません。
C1（都道府県名）： =LEFT(B1,4-SUM((MID(B1,3,1)={"都","道","府","県"})*1))
D1（市区郡）： =IF(F1="","",LEFT(F1,IF(OR(MID(F1,2,2)="郡市",LEFT(F1,3)="今市市",MID(F1,2,2)="市郡",MID(F1,2,3)="日市市",LEFT(F1,5)="八日市場市"),SUM(IF(ISERR(FIND({"蒲郡市","小郡市","今市市","余市郡","高市郡","日市市","市場市"},LEFT(F1,5))),0,FIND({"蒲郡市","小郡市","今市市","余市郡","高市郡","日市市","市場市"},LEFT(F1,5))+2)),IF(ISNUMBER(FIND("区",F1,2)),FIND("区",F1,2),IF(ISNUMBER(FIND("郡",F1,2)),FIND("郡",F1,2),IF(ISNUMBER(FIND("市",F1,2)),FIND("市",F1,2)))))))
E1（それ以降）： =RIGHT(F1,LEN(F1)-LEN(D1))
F1（作業列）： =RIGHT(B1,LEN(B1)-LEN(C1))
エクセルファンクラブの「住所を区市町村で区切るには？」という質問に対して爺爺岳さんが以下の式を作成されました。これは完璧に区市町村で区分するようです。
D1（区市町村）
=IF(C1="東京都",IF(COUNT(FIND({"東村山","武蔵村","羽村市"},LEFT(F1,3))),LEFT(F1,FIND("市",F1)),LEFT(F1,MIN(FIND({"市","区","町","村"},F1&"市区町村",2)))),IF(COUNT(FIND({"今市市","四日市","八日市","廿日市"},LEFT(F1,3))),LEFT(F1,FIND("市",F1,FIND("市",F1)+1)),IF(IF(COUNT(FIND({"蒲郡市","大和郡","小郡市"},LEFT(F1,3))),"市",IF(COUNT(FIND({"余市郡","高市郡"},LEFT(F1,3))),"郡",MID(F1,MIN(FIND({"市","郡"},F1&"市郡",2)),1)))="市",IF(COUNT(FIND({"札幌市","仙台市","千葉市","横浜市","川崎市","名古屋","京都市","大阪市","神戸市","広島市","北九州","福岡市"},LEFT(F1,3))),LEFT(F1,FIND("区",F1)),LEFT(F1,FIND("市",F1,2))),IF(COUNT(FIND({"佐波郡玉村","恵那郡岩村","東宇和郡野","杵島郡大町","北松浦郡鹿"},LEFT(F1,5))),LEFT(F1,FIND("郡",F1)+3),LEFT(F1,MIN(FIND({"町","村"},F1&"町村",FIND("郡",F1)+2)))))))
サンプルファイルはこちらです。
未入力セルの場合は空白を表示
【問題】
セルA1の値が100未満の場合はB1に●を表示し、そうでない場合は空白にしたい。
=IF(A1<100,"●","")
では、未入力のセルも100未満と認識されて●が表示されてしまいます。
未入力のセルも空白にしたい場合はどうすればいいでしょうか？
【回答】
=LEFT("●",LEN(A1)*(A1<100))
【解説】
=IF(AND(ISNUMBER(A1),A1<100),"●","")
といった数式の方が一般的です。
LEN 関数は、文字列の文字数を返す関数です。セルA1が未入力の場合には、
0を返します。
LEFT 関数は、文字列の先頭から指定された数の文字を返します。
LEFT(文字列, 取り出す文字数) という引数をとります。
取り出す文字数が文字列の文字数より大きい場合、文字列全体が返されます。
=A1<100 はホルa1の値が100未満の場合に
TRUEを、100以上の場合に FALSE を返します。
セルA1が未入力のセルの場合には
=LEN(A1) は 0
=A1<100 はtrue
従って、 =0*True となり‖これは 0 を返します｜
=LEFT("●",0)
すなわち、文字列 ● の左から0文字分を返す、ということになり、セルA1が未入力の場合には空白が表示されるのです。
セルA1に例えば 50 という数値が入っていた場合、
=LEN(A1) は 2
=A1<100 は trueとなり‖
=2*TRUE は 2となります｜
=LEFT("●",2)
すなわち、文字列 ● の左から2文字分を返す、ということになり、取り出す文字数が文字列の文字数より大きい場合、文字列全体が返されることから、●
が表示されます。
セルA1に例えば 150 という数値が入っていた場合、
=LEN(A1) は 3
=A1<100 は falseとなり‖
=3*FALSE は 0となります｜
=LEFT("●",0)
すなわち、文字列 ● の左から0文字分を返す、ということになり、空白が表示されます。
数字を漢数字に変換
【問題】
数字の1234567890をそれぞれ、壱弐参四五六七八九〇に変換するにはどうすれば良いでしょうか？
【回答】
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(NUMBERSTRING(A1,3),"一","壱"),"二","弐"),"三","参")
【解説】
セルA1に1234567890が入っているとします。
=NUMBERSTRING(A1,3)
は、一二三四五六七八九〇を返します。
これにSUBSTITUTE関数を三つかませることで、壱弐参四五六七八九〇になるのです。
なお、表示形式で [DBNUM2]0 とした場合には、壱弐参四伍六七八九〇と五が伍と表示されます。
文字間のスペースを削除
【問題】
○○ ××
○○ ××
のように文字の間に入っているスペースを削除して
○○××
のようにするにはどうしたら良いでしょうか？
【回答】
=SUBSTITUTE(SUBSTITUTE((A1)," ","")," ","")
【解説】
最初の" "は半角スペース、次の" "は全角スペースです。
SUBSTITUTE関数は、文字列中の指定された文字を他の文字に置き換えます。
SUBSTITUTE(文字列, 検索文字列, 置換文字列,
置換対象) という引数をとります。[検索文字列]を[置換文字列]に置き換えます。
[置換対象]で、[文字列]に含まれるどの[検索文字列]
を[置換文字列]と置き換えるかを指定します。[置換対象]を指定した場合、[文字列]中の[置換対象]番目の[検索文字列]だけが置き換えられます。省略した場合は、[文字列]中のすべての[検索文字列]が置き換えられます。今回の数式では、[置換対象]を省略しています。
例えばセルA1に
EXCEL 2000 SR-1 という文字列（最初のスペースは全角、次のスペースは半角）が入っている場合、
=SUBSTITUTE((A1)," ","") で‖半角のベペーベが削除され‖
EXCEL 2000SR-1 となります。
次に=SUBSTITUTE("EXCEL 2000SR-1"," ","") で、全角スペースが削除され、
EXCEL2000SR-1 となるわけです。
置換だと以下の手順です。
1.セル範囲を選択
2.Ctrl+H （メニュー[編集]-[置換])
3.[検索する文字列]に半角スペースを入力
4.[置換後の文字列]には何も入力しない
5.[半角と全角を区別する]と[完全に同一なセルだけを検索する]のチェックをオフに
6.[すべて置換]ボタンをクリック
セル範囲（例えばA1:A10のように）を選択してから置換を実行すると、そのセル範囲内だけが置換の対象になります。一方、セルを一つだけ選択した状態で置換を行うと全セルが置換の対象になります。
それでは、ある特定の一つのセルだけ置換の対象にしたい場合にはどうすれば良いでしょうか？
置換の対象にしたいセルを選択して、Ctrl+H（メニュー[編集]-[置換])とし、[検索する文字列]、[置換後の文字列]にそれぞれ入力後、[すべて置換]ボタンではなく[置換]ボタンを押します。
単語間のスペースを 1 つずつ残す（余分なスペースは削除）
【問題】
□鈴木□太郎
□伊藤□花子
□佐藤□三郎
というように姓名の前と姓と名前の間にスペース（□で表示）が入っています。
以下のように姓と名前の間のスペースは残して、姓名の前についているスペースだけを削除するにはどうしたら良いでしょうか？
鈴木□太郎
伊藤□花子
佐藤□三郎
【回答】
=TRIM(A1)
【解説】
TRIM関数は、文字列に複数のスペースが連続して含まれている場合、単語間のスペースを一つずつ残して、不要なスペースを全て削除します。
文字列の先頭及び一番後ろのスペースについても削除します。
区切り位置だと以下の手順です。
1.列を選択
2.メニュー[データ]-[区切り位置]
3.[スペースによって右または左に揃えられた固定長フィールドのデータ]にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[データのプレビュー]のところで先頭のスペースと姓の間でクリック
5.[次へ]をクリック
6.[データのプレビュー]のところで1列目が選択されている状態で[列のデータ形式]のところにある[削除する]をクリック
7.[完了]をクリック
なお、Ctrl+H （メニュー[編集]-[置換])で、[検索する文字列]に半角スペースを入力、[置換後の文字列]で何も入力しないというやり方では、姓と名前の間のスペースも消えてしまいます。
月末の日付
【問題】
先月末、今月末、来月末の日付を求めるにはそれぞれどういう数式にすればよいでしょうか？
例えば、今日が2000/10/16であった場合、それぞれ2000/9/30、2000/10/31、2000/11/30を求めたいのです。
【回答】
先月末 =DATE(YEAR(TODAY()),MONTH(TODAY()),0)
今月末 =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
来月末 =DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)
【解説】
今日が2000/10/16だとして、来月末を求める数式を解説します。
=YEAR(TODAY())
YEAR関数は、日付に対応する年を返します。従って、2000が返ります。
=MONTH(TODAY())
MONTH関数は、日付に対応する月を返します。従って、10が返ります。
その結果、=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)は、
=DATE(2000,10+2,0) すなわち‖=DATE(2000,12,0)
となります。
=DATE(2000,12,1) は2000/12/1です｜
=DATE(2000,12,0) は2000/12/1の一日双と解釈され‖2000/11/31となります｜
=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)は、
=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1
と同じです。"2ヵ月後の月初の一日前"="1ヶ月後の月末"というわけです。
メニュー[ツール]-[アドイン]で[分析ツール]にチェックをつけていれば、以下の数式でも算出することができます。
=EOMONTH(TODAY(),-1)
=EOMONTH(TODAY(),0)
=EOMONTH(TODAY(),1)
EOMONTH関数は、=EOMONTH(開始日, 月) という引数をとります｜
月に正の数を指定すると、起算日より後の日付となり、負の数を指定すると、起算日より前の日付となります。
[分析ツール]にチェックが入っていない場合には、#NAME?
というエラー値が表示されます。
重複したデータの二番目以降にマークを付ける
【問題】
A2:A11に以下のデータが入っています。
a
b
a
c
c
b
d
c
d
e
B2:B11にA列のデータが重複している場合に、*
を表示させるようにしたい。ただし、重複していても最初のデータの場合には、*
を表示させないようにするにはどうしたら良いでしょうか？
a
b
a *
c
c *
b *
d
c *
d *
e
【回答】
=IF(COUNTIF($A$2:A2,A2)>1,"*","")
をセルB2に入力して、セルB11までコピーします。
【解説】
セルB2では、 =IF(COUNTIF($A$2:A2,A2)>1,"*","") です。
=COUNTIF($A$2:A2,A2) は 1 なので‖=COUNTIF($A$2:A2,A2)>1
はFALSEとなり、* は表示されません。
セルB4では、=IF(COUNTIF($A$2:A4,A4)>1,"*","")
となります。
=COUNTIF($A$2:A4,A4) は =COUNTIF({"a";"b";"a"},"a")
なので、2が返ってきます。=2>1 でtrueが返るので‖*
が表示されます。
$A$2:A2 のように絶対参照:相対参照という形にしておくことで、下方に式をコピーした際に、セル範囲を最初のセルからその行のセルまでというふうに拡張することができるのです。
以下の式でも同じ結果になります。
=LEFT("*",COUNTIF($A$2:A2,A2)>1)
月の日数
【問題】
今月が何日あるか、その日数を求めるにはどういう数式を使えば良いでしょうか？
例えば今日が2000/10/19であれば31、今日が2000/2/19であれば29を求めたいのです。
【回答】
=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))
【解説】
日割り計算などを行う際などに、この数式が役に立つのではないでしょうか？
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
で”来月の1日の前日”、すなわち今月末の日付が算出されます。
DAY関数は、その日付が何日かを返します。
=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))
で、月末の日が何日か、すなわちその月の日数が何日あるかを返すわけです。
メニュー[ツール]-[アドイン]で[分析ツール]にチェックをつけていれば、以下の数式でも、その月の日数を算出することができます。
=DAY(EOMONTH(TODAY(),0))
特定の文字列で分割
【問題】
A列に以下のようなデータが入っています。
| A123(0) |
| A45(1) |
| A6(23) |
| A123 |
| A45 |
| A7 |
これを
| A123(0) | A123 | (0) |
| A45(1) | A45 | (1) |
| A6(23) | A6 | (23) |
| A123 | A123 | |
| A45 | A45 | |
| A7 | A7 |
というように "(" 以前（B列)と以降(C列)に分けて表示する為には、どのような関数を使用すれば良いでしょうか？
【回答】
セルB1に
=LEFT(A1,FIND("(",A1&"(")-1)
セルC1に
=RIGHT(A1,LEN(A1)-LEN(B1))
【解説】
セルA1に A123 が入っている場合を例に解説します。
まずは、=LEFT(A1,FIND("(",A1&"(")-1)
から、
=A1&"("
& (アンパサンド)は文字列演算子で、複数の文字列を連結させることができます。
これで、 A123( が返されます。
なぜ、&"(" としているかは、後述します。
=FIND("(",A1&"(")
FIND関数は、FIND(検索文字列, 対象, 開始位置)
という引数をとります。
FIND関数は、検索文字列を対象の中で検索し、その文字列が対象内で最初に現れる位置を左端から数え、その番号を返します。つまり、何文字目にあるのかを返すわけです。
この式のように開始位置を省略すると、1を指定したと見なされ、対象の先頭文字から検索が始まります。
A123( において、( が何文字目にあるかということですから、返り値は5となります。
さて、ここで、&"(" をつけないで、
=FIND("(",A1) としていたら‖どうなる
でしょうか？
FIND関数は検索文字列（今回の例の場合、"("です）が対象の中で見つからない場合、エラー値
#VALUE! を返します。
そうすると、=LEFT(A1,FIND("(",A1&"(")-1)
の結果もエラー値 #VALUE! となってしまいます。
このエラーを回避するために =LEFT(A1,FIND("(",A1&"(")-1)
と&"("をつけているのです。
=FIND("(",A1&"(")-1
-1しているのは、 ( の前で区切るためです。
=5-1 で4となります｜
=LEFT(A1,FIND("(",A1&"(")-1)
LEFT関数は、文字列の先頭から指定された数の文字を返します。
A123 の先頭から4文字分ですから、返り値は
A123 となります。
次に =RIGHT(A1,LEN(A1)-LEN(B1))
LEN関数は、文字列の文字数を返します。
=LEN(A1) は‖=LEN("A123")で返り値は4。
=LEN(B1) も‖=LEN("A123")で返り値は4。
=LEN(A1)-LEN(B1) は =4-4 で0となります｜
RIGHT関数は、文字列の末尾 (右端) から指定された文字数の文字を返します。
A123 の右端から0文字分の文字列を返すということになりますから、セルC1には何も表示されません。
続いて,セルA1に A123(0) が入っている場合を例に解説します。
=A1&"("
これで、 A123(0)( が返されます。
=FIND("(",A1&"(")
A123(0)( において、最初に現れる ( が何文字目にあるかということですから、返り値は5となります。
=FIND("(",A1&"(")-1
=5-1 で4となります｜
=LEFT(A1,FIND("(",A1&"(")-1)
は、すなわち
=LEFT("A123(0)",4)
ということになり、返り値は A123 となります。
次に =RIGHT(A1,LEN(A1)-LEN(B1))
=LEN(A1) は‖=LEN("A123(0)")で返り値は7。
=LEN(B1) は‖=LEN("A123")で返り値は4。
=LEN(A1)-LEN(B1) は =7-4 で3となります｜
A123(0) の右端から3文字分の文字列を返すということになりますから、(3)
が返り値となります。
サンプルファイルはこちらです。
成人の日、体育の日
【問題】
その年の成人の日と体育の日が何日かを求めるには、それぞれどういう数式を使えば良いでしょうか？
成人の日は1月の第2月曜日、体育の日は10月の第2月曜日です。
セルA1に四桁の年を入力するとします。
例えばセルA1に 2001 と入力したら、それぞれ、2001/1/8、2001/10/8を返します。
【回答】
成人の日
=DATE(A1,1,14-WEEKDAY(DATE(A1,1,0),3))
体育の日
=DATE(A1,10,14-WEEKDAY(DATE(A1,10,0),3))
【解説】
=DATE(A1,1,0) は1月1日の双日‖すなわち双馬の12月31日を返します｜
=DATE(A1,10,0) は10月1日の双日‖すなわち9月30日を返します｜
WEEKDAY関数は、日付（シリアル値）から曜日に対応する値を返します。
WEEKDAY(シリアル値, 種類)という引数をとります。種類によって曜日に対応する値が異なります。
種類は以下の3種類があります。種類を省略すると1になります。
上記式では種類として3を指定しています。
1: 1 (日曜) 〜 7 (土曜)
2: 1 (月曜) 〜 7 (日曜)
3: 0 (月曜) 〜 6 (日曜)
第2月曜日は8日から14日までの範囲のどれかです。
12月31日が月曜日(WEEKDAY関数の返り値は0）の場合、翌年の1月の第2月曜日は14日（=14-0)になります。
12月31日が火曜日(WEEKDAY関数の返り値は1）の場合、翌年の1月の第2月曜日は13日（=14-1)になります。
12月31日が日曜日(WEEKDAY関数の返り値は6）の場合、翌年の1月の第2月曜日は8日（=14-6)になります。
それぞれ以下の式でも同じ結果が返ります。
成人の日
=DATE(A1,1,14-WEEKDAY(DATE(A1-1,12,31),3))
体育の日
=DATE(A1,10,14-WEEKDAY(DATE(A1,9,30),3))
第n曜日を求める
【問題】
A1:A4に以下を入力するとします。
第n曜日の日付を求めるにはどういう数式にすれば良いでしょうか？
A1: 年
A2: 月
A3: n
A4: 曜日に対応する値：0 (月曜) 〜 6 (日曜)
この数式は、A1:A4に以下が入力されていると、
（例1）
2001
1
1
1
2001年1月の第1火曜日（1月の1番目の火曜日）、すなわち2001/1/2を返します。
（例2）
2001
1
3
4
2001年1月の第3金曜日（1月の3番目の金曜日）、すなわち2001/1/19を返します。
（例3）
2000
12
1
1
2000年12月の第1火曜日（12月の1番目の火曜日）、すなわち2000/12/5を返します。
（例4）
2000
12
3
4
2000年12月の第3金曜日（12月の3番目の金曜日）、すなわち2000/12/15を返します。
【回答】
=DATE(A1,A2,A3*7-WEEKDAY(DATE(A1,A2,-A4),3))
【解説】
サンプルファイルはこちらです。
7の倍数から、前月末日の曜日と求める曜日の日数の差を引くといった感じの数式になります。
2001年1月の第1火曜日の例で解説します。
=DATE(2001,1,1*7-WEEKDAY(DATE(2001,1,-1),3))
=DATE(2001,1,-1)
これは、2001/1/1(月)の前日、2000/12/31(日)の1日前、すなわち2000/12/30(土)を返します。
土曜日なので、=WEEKDAY(DATE(2001,1,-1),3)
の返り値は5になります。
=1*7-5の返り値は2なので、=DATE(2001,1,2)すなわち、2001/1/2(火)という結果になります。
次に2001年1月の第3金曜日の例で解説します。
=DATE(2001,1,3*7-WEEKDAY(DATE(2001,1,-4),3))
=DATE(2001,1,-4)
これは、2001/1/1(月)の前日、2000/12/31(日)の4日前、すなわち2000/12/27(水)を返します。
水曜日なので、=WEEKDAY(DATE(2001,1,-4),3)
の返り値は2になります。
=3*7-2の返り値は19なので、=DATE(2001,1,19)すなわち、2001/1/19(金)という結果になります。
WEEKDAY関数は、日付（シリアル値）から曜日に対応する値を返します。
WEEKDAY(シリアル値, 種類)という引数をとります。種類によって曜日に対応する値が異なります。
種類は以下の3種類があります。種類を省略すると1になります。
上記式では種類として3を指定しています。
1: 1 (日曜) 〜 7 (土曜)
2: 1 (月曜) 〜 7 (日曜)
3: 0 (月曜) 〜 6 (日曜)
国民の休日
【問題】
セルA1に四桁の年を入力するとして、その年の国民の休日がない場合には
− を、ある場合にはその日付を返すにはどういう数式にすれば良いでしょうか？
以下は「国民の祝日に関する法律」第三条（休日）です。
1 「国民の祝日」は、休日とする。
2 「国民の祝日」が日曜日にあたるときは、その翌日を休日とする。
3 その前日及び翌日が「国民の祝日」である日（日曜日にあたる日及び前項に規定する休日にあたる日を除く。）は、休日とする。
”その前日及び翌日が「国民の祝日」である日”は、5月4日だけです。
憲法記念日(5月3日)
こどもの日(5月5日)
5月4日が日曜日の場合には、国民の休日にはなりません。
5月3日が日曜日の場合には、5月4日は振替休日となり、国民の休日にはなりません。
例えば、2003年、2008年、2009年には国民の休日はありません。
【回答】
=IF(WEEKDAY(DATE(A1,5,3),3)>=5,"−",DATE(A1,5,4))
【解説】
5月3日または5月4日が日曜日の場合、国民の休日はありません。言い換えると5月3日が土曜日か日曜日の場合、国民の休日はありません。
WEEKDAY関数は、日付（シリアル値）から曜日に対応する値を返します。
WEEKDAY(シリアル値, 種類)という引数をとります。種類によって曜日に対応する値が異なります。
種類は以下の3種類があります。種類を省略すると1になります。
上記式では種類として3を指定しています。
1: 1 (日曜) 〜 7 (土曜)
2: 1 (月曜) 〜 7 (日曜)
3: 0 (月曜) 〜 6 (日曜)
=WEEKDAY(DATE($A$1,5,3),3)>=5
は、5月3日が土曜日か日曜日であればTRUEを返します。
90度回転したセル参照
【問題】
A1:C3に以下のようなデータが入っているとします。
1 2 3
4 5
6
7 8 9
これを90度右に回転して、E1:G3に
7 4 1
8 5 2
9 6 3
という配置のセル群を作成するにはどうしたら良いでしょうか。
また、180度回転、270度回転させるにはどうしたら良いでしょうか。
【回答】
A1:C3に LIST
と名前を付けます。
E1:G3を選択して、
=INDEX(LIST,COLUMNS(LIST)-COLUMN(LIST)+1,ROW(LIST))
をCtrl+Shift+Enterで入力。これで90度回転の行列ができます。
180度の場合は以下をCtrl+Shift+Enter
=INDEX(LIST,COLUMNS(LIST)-COLUMN(LIST)+1,ROWS(LIST)-ROW(LIST)+1)
9 6
3
8 5 2
7 4 1
270度は以下をCtrl+Shift+Enter
=INDEX(LIST,COLUMN(LIST),ROWS(LIST)-ROW(LIST)+1)
3 6 9
2 5 8
1
4
7
【解説】
上記は3x3の行列の例ですが、5x5、7x7などの行列も同様にして回転させることができます。
A1:C3に以下のようなデータが入っているとして考えるとわかりやすいかも知れません。
R1C1
R1C2 R1C3
R2C1 R2C2 R2C3
R3C1 R3C2
R3C3
これを90度右に回転させると、以下のようになります。
R3C1 R2C1 R1C1
R3C2 R2C2
R1C2
R3C3 R2C3 R1C3
これを180度右に回転させると、以下のようになります。
R3C3 R2C3
R1C3
R3C2 R2C2 R1C2
R3C1 R2C1
R1C1
これを90度右に回転させると、以下のようになります。
R1C3 R2C3 R3C3
R1C2 R2C2
R3C2
R1C1 R2C1 R3C1
最大値
【問題】
セルA1とセルC1の数値を比べて大きいほうのセルの数値を返すにはどういう数式にすれば良いでしょうか？。
【回答】
=MAX(A1,C1)
【解説】
問題文をそのまま式にすると
=IF(A1>C1,A1,C1)
となり、もちろんこれでも同じ結果が返ります。
しかし、=MAX(A1,C1)
の方が式としてよりわかりやすいです。
A1:C1内で最大値を返す、といった場合には、
=MAX(A1:C1)
になります。
2番目に大きい値
【問題】
=MAX(A1:A100)
でA1:A100の最大値を取得することができます。
では、A1:A100のデータの内、2番目、3番目に大きな値を取得するにはどうすればよいのでしょうか？
セルB1に数値Xを記入し、B2にX番目に大きな値を取得するようにしたいのです。
【回答】
=LARGE(A1:A100,B1)
【解説】
同様にX番目に小さな値を取得するには以下の数式です。
=SMALL(A1:A100,B1)
最終営業日
【問題】
月末の日付を求める数式、例えば今月の月末であれば、
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
あるいは、
=EOMONTH(TODAY(),0)
で求めることができます。
それでは、例えば月末が休日（土日祝日）であったら、その前日の営業日の日付を取得するようにするにはどうしたら良いでしょうか。
つまり、月の最終営業日を求める数式を作りたいのです。
セルA2に日付を入力するとします。
2001年1月
2001/1/31 (水)
2001年2月 2001/2/28 (水)
2001年3月 2001/3/30 (金)
2001年4月
2001/4/27 (金)
2001年5月 2001/5/31 (木)
2001年6月 2001/6/29
(金)
【回答】
祝日を入力した範囲に 祝日 と名前をつけているとします。
=WORKDAY(EOMONTH(A2,0)+1,-1,祝日)
【解説】
WORKDAY関数、EOMONTH関数どちらもアドイン関数なので、メニュー[ツール]-[アドイン]で分析ツールにチェックしておく必要があります。
=WORKDAY(EOMONTH(A2,0)+1,-1,祝日)
は、月末日の翌日の前営業日というやり方
で最終営業日を求めています。
例えば、セルA2に 2001/3/1
が入力されているとすると、
月末日 2001/3/31 (土)
↓
翌日 2001/4/1 (日)
↓
前営業日
2001/3/30
(金)
というふうにして月の最終営業日を求めています。
もう少し詳細に解説すると、
=EOMONTH(A2,0)
これで月末の日が返ります。
セルA2には 2001/3/1 が入っているので、=EOMONTH(A1,0) は 2001/3/31
(土)
を返します。
=EOMONTH(A2,0)+1
月末の翌日、すなわち月初の 2001/4/1 (日)
を返します。
=WORKDAY(EOMONTH(A2,0)+1,-1,祝日)
WORKDAY関数は、WORKDAY(開始日,
日数,
祭日)という引数をとります。開始日
から起算して、指定された稼動日数だけ前または後の日付に対応する値を返しま
す。
2001/4/1
(日) の前営業日で、2001/3/30 (金)
が返ります。
翌月の最終営業日という場合は、
=WORKDAY(EOMONTH(A2,1)+1,-1,祝日)
前月の最終営業日という場合は、
=WORKDAY(EOMONTH(A2,-1)+1,-1,祝日)
という式になります。
月末が休日だった場合には翌月の月初営業日というふうにするには、
=WORKDAY(EOMONTH(A2,0)-1,1,祝日)
とします。
月末日
2001/3/31 (土)
↓
前日 2001/3/30 (金)
↓
翌営業日 2001/4/2
(月)
という処理を行っているわけです。
サンプルファイルはこちらです。
ランク
【問題】
2
6
5
4
3
A1:C5に上の表のように数字を入力し、B列には大きい数字から順番に順位をつけ、C列には小さい数字から順番に順位をつけて上の表の結果になるようにするには、どの関数を使えばよいでしょうか？
A B C
2 5 1
6 1 5
5 2 4
4 3 3
3 4
2
【回答】
1.セルB1に =RANK(A1,$A$1:$A$5) を入力｜
2.セルC1に
=RANK(A1,$A$1:$A$5,1) を入力｜
3.B1:C1をB2:C5にコピー
【解説】
RANK関数は、順序 に従って 範囲 内の数値を並べ替えたとき、数値 が何番目に位置するかを返します。
RANK(数値, 範囲, 順序)
という書式をとります。
順序 に 0 を指定するか、または 順序 を省略すると、範囲 内の数値が ...3、2、1
のように降順に並べ替えられます。
順序 に 0 以外の数値を指定すると、範囲 内の数値が 1、2、3、... のように昇順で並べ替えられます。
数値を0.5単位で切り上げる
【問題】
ある数値を0.5単位で切り上げるにはどういう数式を使えば良いのでしょうか。
例えば、
1.23 → 1.5
1.56 → 2.0
2.34 → 2.5
というような感じです。
【回答】
=CEILING(A1,0.5)
【解説】
CEILING関数は、数値を挟む基準値の倍数のうち、0
から遠い方の値を返します。
CEILING(数値, 基準値)
という引数をとります。
逆に0.5単位で切り捨てる場合には、以下の数式です。
=FLOOR(A1,0.5)
FLOOR関数は、数値を挟む基準値の倍数のうち、0
に近い方の値を返します。
CEILING関数、FLOOR関数は、数値と基準値の符号が異なる場合、エラー（#NUM!）が返されます。
セル内の一部の文字列を抽出
【問題】
セル内の "『" と "』" で挟まれた文字列をマクロを使って他のセルに書き出すにはどうしたら良いでしょうか？
例えば、セルA1に、
あいうえお『かき』くけこ
が入力されているとします。。
これを、
"『" と "』" で挟まれた文字列 "か" のみ抽出し、セルB1に、
かき
と表示させたいのです。
※ "『" と
"』"は、セル内にそれぞれ一つずつしか存在しません。
※ "『" と "』"の位置は固定されていません。何文字目か不明確です。
※ "『" と
"』"に挟まれた文字数は一定ではありません。何も入っていないこと
もありますし、１文字以上入っていることもあります。
【回答】
範囲を選択して、以下のマクロを実行すると右隣のセルに"『" と
"』" で挟まれた文字列を抽出します。
Sub test()
Const Chr1 As String = "『"
Const Chr2 As String = "』"
Dim c As Range
Dim Srch As
String
Dim Btwn As String
For Each c In Selection
Srch =
c.Value & Chr1 & Chr2
Btwn = mid(srch, instr(srch, chr1) + 1,
_
InStr(Srch, Chr2) - InStr(Srch, Chr1) - 1)
c.Offset(, 1).Value =
Btwn
Next
End
Sub
【解説】
ワークシート関数だと、
=MID(A1,FIND("『",A1&"『』")+1,FIND("』",A1&"『』")-FIND("『",A1&"『』")-1)
といった感じになります。
べき乗
【問題】
3の二乗は9、4の三乗は64です。
これをエクセルの数式を使って算出するには、どうすれば良いでしょうか？
【回答】
3の二乗
=POWER(3,2)
4の三乗
=POWER(4,3)
【解説】
POWER関数の代わりに ^ （キャレット）を使って、以下の数式でも同じ結果が返ります。
=3^2
=4^3
平方根
【問題】
平方根を返すにはどういう数式を使えば良いでしょうか？
例えば、 2
の平方根（1.41421356・・・）を返す数式は？
【回答】
=SQRT(2)
【解説】
同様に3の平方根を返す数式は、=SQRT(3)
になります。
平方根は、英語では square root（スクエア ルート）と言います。
SQRT関数は、SQuare
RooTが語源です。
関数の読み方・語源については、以下のページにまとめていますので、ご覧になってみてください。
ワークシート関数の読み方と語源
http://www2.odn.ne.jp/excel/function.html
2 の平方根（1.41421356・・・）は、「ひとよひとよにひとみごろ」、 3 の平方根（1.7320508・・・）は「ひとなみにおごれや」というふうに覚えるのだ、と教わった記憶があります。最近は、どうなんでしょう？(^_^)
立方根
【問題】
平方根を返すにはSQRT関数を使います。
例えば、2の平方根の場合には、 =SQRT(2)
という数式になります。
では、立法根を返すには、どういう数式になるでしょうか？
【回答】
=POWER(2,1/3)
【解説】
1/n乗でn乗根が返ります。
2の4乗根であれば、 =POWER(2,1/4) という数式になります｜
POWER関数の代わりに ^ （キャレット）を使って、以下の数式でも同じ結果が返ります。
=2^(1/3)
積
【問題】
A2:A6に以下のデータが入力されています。
2
3
4
5
6
A2:A6をすべて乗算した数値（720）を返すには、どういう数式を使えば良いでしょうか？
【回答】
=PRODUCT(A2:A6)
【解説】
SUM関数は、引数にとった範囲のデータをすべて加算した数値を返します。
PRODUCT関数は、引数にとった範囲のデータをすべて乗算した数値を返します。
複利
【問題】
ある投資信託の収益率は以下でした。表はA1:B6に入力されています。
年 収益率
1996
5%
1997 25%
1998 30%
1999 -7%
2000
-3%
1996年初に100万円投資していたら2000年末にはいくらになっているかを計算するにはどうしたら良いでしょうか？
【回答】
1.C2:C6を選択
→セルC2がアクティブでC2:C6を選択している状態
2.
=1+B2 と入力してenterの代わりにctrl+enter
3.セルC7に =1000000*PRODUCT(C2:C6)
と入力
【解説】
結果は、1,539,208 になります。
作業列を使用したくない場合には、 =1000000*PRODUCT(1+B2:B6) をctrl+shift+enterで入力します｜
幾何平均
【問題】
ある投資信託の収益率は以下でした。
年 収益率
1996 5%
1997
25%
1998 30%
1999 -7%
2000
-3%
年率換算の収益率を算出するにはどうしたら良いでしょうか？
【回答】
1.C2:C6を選択
→セルC2がアクティブでC2:C6を選択している状態
2.
=1+B2 と入力してenterの代わりにctrl+enter
3.セルC7に =PRODUCT(C2:C6)^(1/5)-1
と入力
【解説】
作業列を使用したくない場合には、 =PRODUCT(1+B2:B6)^(1/5)-1 をctrl+shift+enterで入力します｜
一の位、十の位で四捨五入
【問題】
A2:A7に以下のデータが入っています。
55
86
37
135
146
161
これを一の位で四捨五入して以下の結果を返すようにするにはどういう数式にすれば良いでしょう。
60
90
40
140
150
160
【回答】
=ROUND(A2,-1)
【解説】
ROUND関数はROUND(数値,
桁数)という引数をとります。
=ROUND(3.14,1) のように桁数に正の値を指定すると‖返り値の小数点以下の桁数は指定した桁数に等しくなります｜従って‖=ROUND(3.14,1)
の結果は 3.1 が返ります。
=ROUND(A2,-1) のように桁数に負の値を指定すると‖数値は小数点の左側 (整数部分)
で四捨五入されます。
今回の【問題】のケースで =ROUND(A2,-2) という式を使うと十の位で四捨五入され‖結果は以下のようになります｜
100
100
0
100
100
200
=ROUND(3.14,0)
のように桁数を0にすると、整数にまるめられます。
五捨六入
【問題】
A2:A7に以下のデータが入っています。
55
86
37
135
146
161
これを一の位で五捨六入して以下の結果を返すようにするにはどういう数式にすれば良いでしょう。
50
90
40
130
150
160
【回答】
=ROUND(A2-1,-1)
【解説】
元の数値から1を引いて四捨五入をすれば、五捨六入になるというわけです。
行列逆転
【問題】
A1:C3に以下のようなデータが入っているとします。
1 4 7
2 5 8
3 6
9
これを列を逆にして、E1:G3に
7 4 1
8 5 2
9 6
3
という配置のセル群を作成するにはどうしたら良いでしょうか。
また、行を逆にするにはどうしたら良いでしょうか。
3 6 9
2 5 8
1 4
7
また、行列とも逆にするにはどうしたら良いでしょうか。
9 6 3
8 5 2
7 4
1
【回答】
A1:C3に LIST と名前を付けます。
列を逆にするには、例えばE1:G3を選択して、
=INDEX(LIST,ROW(LIST),COLUMNS(LIST)-COLUMN(LIST)+1)
をCtrl+Shift+Enterで入力。
行を逆にするには、例えばE5:G7を選択して、
=INDEX(LIST,ROWS(LIST)-ROW(LIST)+1,COLUMN(LIST))
をCtrl+Shift+Enterで入力。
行列とも逆にするには、例えばE9:G11を選択して、
=INDEX(LIST,ROWS(LIST)-ROW(LIST)+1,COLUMNS(LIST)-COLUMN(LIST)+1)
をCtrl+Shift+Enterで入力。
【解説】
サンプルファイルはこちらです。
90度、180度、270度回転については以下のページをご覧ください。
【エクセル技道場】−関数−90度回転したセル参照
http://www2.odn.ne.jp/excel/waza/function.html#SEC38
文字列が入っているセルの隣に連番
【問題】
A列に文字列が入っている場合はB列に順番に連番をふりたいです。
A1に「横浜」と文字が入っている場合はB1に 1 。A2に「東京」と入っていたらB2に
2。
ただ、A列は途中で空白があったり数値が入ってたりします。例えばA3が空白や数値の場合はB3は空白にして、A4が「大阪」だったらB4は
3 のようにしたいのです。
【回答】
セルB1に
=IF(COUNTIF(A1,"*"),COUNTIF($A$1:A1,"*"),"")
と入力して、下にコピー。
【解説】
以下のページもご参考になるのでは？
【エクセル技道場】−関数−文字列が入っているセルの個数カウント２
http://www2.odn.ne.jp/excel/waza/function.html#SEC16
一ヶ月後の日付
【問題】
セルA2に日付が入力されています。その日付の一ヶ月後を求めるにはどういう関数を使用すれば良いでしょうか？
例えば、セルA2に 2001/5/29 が入力されていたら、セルB2に 2001/6/29 を返したいのです。
2001/5/31 のように対応する日がない場合には、月末の日付 2001/6/30 を返すこととします。
【回答】
=EDATE(A2,1)
セルB2を選択して、Ctrl+1 (メニュー[書式]-[セル])で[表示形式]タブで[分類]を
日付 にして、[種類]で表示させたい日付の形式を選択します。
【解説】
メニュー[ツール]-[アドイン]で[分析ツール]にチェックをしておく必要があります。
EDATE関数は、EDATE(開始日, 月) という引数をとります。
月 に正の数を指定すると、起算日より後の日付となり、負の数を指定すると、起算日より前の日付となります。
二ヶ月前の日付を求めるには、
=EDATE(A2,-2) のようにします｜
セル内のスペースを削除
【問題】
一つのセル内に入力されているデータからスペースを削除するにはどうすれば良いでしょうか。
例えば、123
456 789 と入力されているものを 123456789 にしたいのです。
【回答】
=SUBSTITUTE(A1,"
",)
または
=SUBSTITUTE(A1,"
",)*1
【解説】
=SUBSTITUTE(A1," ",)
の場合、文字列として返されます。
=SUBSTITUTE(A1," ",)*1
とすると数値として返されます。
置換を使う方法もあります。
1.Ctrl+H
（メニュー[編集]-[置換])
2.[検索する文字列]に半角スペースを入力
3.[置換後の文字列]には何も入力しない
4.Enter
以下のページもご参考になるのでは？
[XL2002] 置換により文字列が数値に変わる
http://support.microsoft.com/default.aspx?scid=kb;ja;404387
オートフィルタ抽出結果のデータ件数のカウント
【問題】
データがA2:A20に入っています。
オートフィルタでデータを抽出したあと、抽出したデータの件数をカウントすることはできるのでしょうか？
COUNT関数でやってみましたが、全部の件数がでてしまいました。
【回答】
=SUBTOTAL(2,A2:A20)
【解説】
SUBTOTAL関数は、抽出した結果非表示になっている行は計算されません。
SUBTOTAL関数は、SUBTOTAL(集計方法,
範囲1) といった引数をとります。
集計方法には以下が指定できます。
集計方法 関数
1 AVERAGE 関数
2 COUNT 関数
3 COUNTA 関数
4 MAX 関数
5 MIN 関数
6 PRODUCT 関数
7 STDEV 関数
8 STDEVP 関数
9 SUM 関数
10
VAR 関数
11 VARP 関数
例えば、抽出の結果の合計を算出したい場合には、
=SUBTOTAL(9,A2:A20)
のようにします。
以下のページもご参考になるのでは？
[XL2000] オートフィルタで抽出を行った際のステイタスバーの表示について
http://support.microsoft.com/default.aspx?scid=kb;ja;401137
EDATE関数
【問題】
EDATE関数は =EDATE(C1,1) とあった場合‖c1より1ヶ月後の日付を表示させる関数です｜
EDATE関数を使用するには、メニュー[ツール]-[アドイン]で[分析ツール]にチェックを入れておく必要があります。
EDATE関数を使わないで、1ヶ月後の日付を返す数式はないでしょうか？
【回答】
=MIN(DATE(YEAR(C1),
MONTH(C1)+1, DAY(C1)),DATE(YEAR(C1),
MONTH(C1)+1+1,0))
【解説】
例えば、2ヶ月後の日付の場合には以下になります。
=MIN(DATE(YEAR(C1),
MONTH(C1)+2, DAY(C1)),DATE(YEAR(C1), MONTH(C1)+1+2,0))
特定の語尾の場合
【問題】
「〜には」とある文字列の「〜」の前に中点をつけたいのですが、関数で実現できるでしょうか？
「平行な2直線に接する円弧を作るには
」
↓
「・平行な2直線に接する円弧を作るには」
という具合です。
【回答】
最後に"には"が付く文字列の場合、あたまに"・"を付けるという条件でしたら、
=IF(COUNTIF(A1,"*には"),"・","")&A1
または、
=IF(RIGHT(A1,2)="には","・","")&A1
文中に"には"がある文字列の場合、あたまに"・"を付けるという条件でしたら
=IF(COUNTIF(A1,"*には*"),"・","")&A1
でいかがでしょうか？
閏年
【問題】
以下のようにB1:B5に日付が入力されています。
2001/8/31
2003/12/31
2004/1/1
2004/2/29
2008/2/29
隣のA列に、B列の日付が閏年（うるうどし）なら 1 を、平年の場合には 0 を表示させたいのですがどういう数式を使えばよいでしょうか？
セルＡ セルＢ
１ 0 2001/8/31
２ 0 2003/12/31
３ 1 2004/1/1
４ 1 2004/2/29
５ 1 2008/2/29
【回答】
=(DAY(DATE(YEAR(B1),2,29))=29)*1
【解説】
その年の2/29が29日かどうかを判定し、その結果返るTRUE/FALSEに 1 をかけて数値化しています。
=(MONTH(DATE(YEAR(B1),2,29))=2)*1
でも同じ結果が返ります。
この式では、その年の2/29が2月かどうかを判定し、その結果返るTRUE/FALSEに 1
をかけて数値化しています。
以下のページもご参考になるのでは？
XL2000: Excel Incorrectly Assumes 1900 Is a Leap Year
http://support.microsoft.com/default.aspx?scid=kb;en-us;214326
[XL2002]Excel で用いている閏年の判定方法
http://support.microsoft.com/default.aspx?scid=kb;JA;118923
結果がマイナスならば0を返す
【問題】
=A1-B1
でセルA1からセルB1を引き算した結果が返ります。
では、引き算した結果がマイナスであれば0を、引き算の結果がプラスであればその値を返すにはどういう数式にすれば良いでしょうか？
【回答】
=MAX(A1-B1,0)
【解説】
=IF(A1-B1<0,0,A1-B1)
でも同じ結果が返ります。
最下行の数値を返す
【問題】
A列に数値が入力されています。
A列で最下行のセルに入力されている数値を返すにはどういう数式を使えば良いでしょうか？
ただし、数値はセルA1から連続して入力されており、途中に未入力のセルはありません。
例えば、A1:A5に以下が入力されていた場合には、5を返したいのです。
1
2
3
4
5
【回答】
=INDEX(A:A,COUNT(A:A))
【解説】
上記数式ではA列に数値が全く入力されていない場合には0が返ります。
以下の数式は、A列に数値が全く入力されていない場合には
"" を返します。
=IF(COUNT(A:A),INDEX(A:A,COUNT(A:A)),"")
最下行の数値を返す（途中に未入力セルあり）
【問題】
A列に数値が入力されています。
A列で最下行のセルに入力されている数値を返すにはどういう数式を使えば良いでしょうか？
ただし、数値はどのセルから入力されているか決まっていないし、途中に未入力のセルがある場合もあります。
例えば、A2:A6に以下が入力されていた場合には、2を返したいのです。
1
3
2
【回答】
=INDEX(A:A,MATCH(MAX(A:A)+1,A:A,1))
【解説】
MATCH関数は、MATCH(検査値,
検査範囲, 照合の型)という引数をとります。
[照合の型]では、 -1、0、1 の数値のいずれかを指定します。この[照合の型]で、[検査範囲]の中で[検査値]を探す方法を指定しています。
照合の型 に 1 を指定すると[検査値]以下の最大の値が検索されます。このとき[検査範囲]は、-2、-1、0、1、2、...、A、...、Z、...、ア、...、ン、...、FALSE、TRUE
のように昇順で並べ替えられておく必要があります。
つまり、[検索範囲]内の最下行からチェックしています。
ここで、
・昇順に並べ替えをせず
・[検査値]を[検査範囲]内の最大値の+1
としておくと、今回の式のように最下行に入力された数値を返します。
最下行の文字列を返す
【問題】
A列に文字列が入力されています。
A列で最下行のセルに入力されている文字列を返すにはどういう数式を使えば良いでしょうか？
ただし、文字列はセルA1から連続して入力されており、途中に未入力のセルはありません。
例えば、A1:A5に以下が入力されていた場合には、
"お"
を返したいのです。
あ
い
う
え
お
【回答】
=INDEX(A:A,COUNTA(A:A))
【解説】
上記数式ではA列に文字列が全く入力されていない場合には0が返ります。
以下の数式は、A列に文字列が全く入力されていない場合には
"" を返します。
=IF(COUNTA(A:A),INDEX(A:A,COUNTA(A:A)),"")
最下行の文字列を返す（途中に未入力セルあり）
【問題】
A列に文字列が入力されています。
A列で最下行のセルに入力されている文字列を返すにはどういう数式を使えば良
いでしょうか？
ただし、文字列はどのセルから入力されているか決まっていないし、途中に未
入力のセルがある場合もあります。
例えば、A2:A6に以下が入力されていた場合には、
"う"
を返したいのです。
あ
お
う
【回答】
=INDEX(A:A,MATCH("",A:A,-1))
【解説】
MATCH関数は、MATCH(検査値,
検査範囲, 照合の型)という引数をとります。
[照合の型]では、 -1、0、1 の数値のいずれかを指定します。この[照合の型]で、[検査範囲]の中で[検査値]を探す方法を指定しています。
[照合の型]に -1 を指定すると、[検査値]以上の最小の値が検索されます。このとき[検査範囲]は、TRUE、FALSE、...、ン、...、ア、...、Z、...、A、...、2、1、0、-1、-2、...
のように降順で並べ替えられている必要があります。
つまり、[検索範囲]内の最下行からチェックしています。
ここで、
・降順に並べ替えをせず
・[検査値]を
""
としておくと、今回の式のように最下行に入力された文字列を返します。
最下行にある数値または文字列を返す数式
【問題】
A列に数値や文字列が入力されています。
A列で数値であろうと文字列であろうととにかく最下行のセルに入力されている数値または文字列を返すにはどういう数式を使えば良いでしょうか？
ただし、数値／文字列はどのセルから入力されているか決まっていないし、途中に未入力のセルがある場合もあります。
例えば、A2:A6に以下が入力されていた場合には、
"う"
を返したいのです。
あ
2
お
1
う
【回答】
=INDEX(A:A,
MAX(
IF(COUNTIF(A:A,"*"),MATCH("",A:A,-1)),
IF(COUNT(A:A),MATCH(MAX(A:A)+1,A:A,1))))
【解説】
最下行の数値を返す数式
=INDEX(A:A,MATCH(MAX(A:A)+1,A:A,1))
と、最下行の文字列を返す数式
=INDEX(A:A,MATCH("",A:A,-1))
を組み合わせました。
数式内のCOUNTIF(A:A,"*")、COUNT(A:A)は、範囲内に文字列、数値が全く入力されていない場合のエラー処理のためのものです。
上記数式ではA列に数値または文字列が全く入力されていない場合には0が返ります。
以下の数式は、A列に数値／文字列が全く入力されていない場合には ""
を返します。
=IF(COUNTA(A:A),
INDEX(A:A,
MAX(
IF(COUNTIF(A:A,"*"),MATCH("",A:A,-1)),
IF(COUNT(A:A),MATCH(MAX(A:A)+1,A:A,1)))),
"")
配列数式で「最下行にある数値または文字列を返す数式」を実現するには、以下のような数式をCtrl+Shift+Enterで入力します。配列数式では列全体を範囲とすることができません。
=INDEX(A1:A100,MAX(ROW(A1:A100)*(A1:A100<>"")))
翌営業日
【問題】
セルA1に日付が入っているとします。
5日後は、=A1+5
と簡単に算出できます。
これを5日後が土日祝日の場合には次の営業日にするにはどういう数式にしたら良いでしょうか？
5営業日後ではなく、暦日で5日後の日付が土日祝日の場合には、その翌営業日を求めたいのです。
【回答】
祝日を入力した範囲に
祝日 と名前をつけているとします。
=WORKDAY(A1+4,1,祝日)
【解説】
WORKDAY関数を使用するには、メニュー[ツール]-[アドイン]で[分析ツール]にチェックをしておく必要があります。
暦日で5日後の日付が土日祝日の場合には、その翌営業日 = 4日後の翌営業日というふうに考えます｜
以下のページもご参考になるのでは？
【エクセル技道場】−関数−最終営業日
http://www2.odn.ne.jp/excel/waza/function.html#SEC41
8文字入力されているセルのカウント
【問題】
A1:A10に8文字入力されているセルの個数をカウントするにはどういう数式を使えば良いでしょうか？
【回答】
=COUNTIF(A1:A10,"????????")
【解説】
セルに2文字入力されているセルの個数をカウントする場合は、
=COUNTIF(A1:A10,"??")
セルに3文字入力されているセルの個数をカウントする場合は、
=COUNTIF(A1:A10,"???")
といった感じです。
?（クエスチョンマーク、または疑問符）は、ワイルドカード文字の一つで ?
一つで一文字を意味します。
8文字以上入力されているセルのカウント
【問題】
A1:A10に8文字以上入力されているセルの個数をカウントするにはどういう数式を使えば良いでしょうか？
【回答】
=COUNTIF(A1:A10,"????????*")
【解説】
セルに2文字以上入力されているセルの個数をカウントする場合は、
=COUNTIF(A1:A10,"??*")
セルに3文字以上入力されているセルの個数をカウントする場合は、
=COUNTIF(A1:A10,"???*")
といった感じです。
?（クエスチョンマーク、または疑問符）は、ワイルドカード文字の一つで ?
一つで一文字を意味します。
*（アスタリスク）は、ワイルドカード文字の一つで、任意の数の文字を意味します。
2文字から4文字入力されているセルのカウント
【問題】
A1:A10に2文字から4文字が入力されているセルの個数をカウントするにはどういう数式を使えば良いでしょうか？
【回答】
=COUNTIF(A1:A10,"??*")-COUNTIF(A1:A10,"?????*")
【解説】
A1:A10に2文字以上入力されているセルの個数から、5文字以上入力されているセルの個数を引くことで、2文字から4文字が入力されているセルの個数をカウントしているわけです。
?（クエスチョンマーク、または疑問符）は、ワイルドカード文字の一つで ?
一つで一文字を意味します。
*（アスタリスク）は、ワイルドカード文字の一つで、任意の数の文字を意味します。
指定した桁の数字を返すには？
【問題】
あるセルに入力されている数値の、指定した桁の数字を返すには、どういう数式を使えば良いでしょうか？
例えば、下記のような数値の100の位、10の位、小数点第2位を返すにはどういう数式を使えば良いでしょうか？
1234.567
12345.67
-1234.567
【回答】
100の位：
=INT(MOD(SIGN(A1)*A1/100,10))
10の位：
=INT(MOD(SIGN(A1)*A1/10,10))
小数点第2位：
=INT(MOD(SIGN(A1)*A1/0.01,10))
【解説】
以下の数式でも同じ結果が返ります。
100の位：
=INT(MOD(SIGN(A1)*A1/10^2,10))
10の位：
=INT(MOD(SIGN(A1)*A1/10^1,10))
小数点第2位：
=INT(MOD(SIGN(A1)*A1/10^-2,10))
電話番号
【問題】
B列に
012-345-6789
012-354-6879
012-435-9876
のように電話番号が入力されています。
これを
(012)345-6789
(012)354-6879
(012)435-9876
のようにするには、どういう数式を使えば良いでしょうか？
【回答】
="("&SUBSTITUTE(B1,"-",")",1)
【解説】
012-345-6789 のように必ず 3桁 - 3桁 - 4桁でしたら、- をはずして数値化して、表示形式で
000-000-0000 とする手もあります。
最大値の隣のセル
【問題】
A1:B4に以下が入力されています。
A B
1 1 100
2 2 500
3 3 200
4 4 300
B列の最大値を探してそのときのA列の値を返すにはどのようにしたらよいでしょうか？
【回答】
=INDEX(A:A,MATCH(MAX(B:B),B:B,0))
数字＋文字列から数字のみを取り出す
【問題】
数字＋文字列から数字のみを取り出すにはどういう数式にすれば良いでしょうか？
例えば、
100錠
1000Ａ
500ＣＰ
100ｇ
を↓のように
100
1000
500
100
数字だけを取り出したいのです。数字の桁数、文字数は可変です。
また、
ABC-A165BTL
のように文字列＋数字＋文字列のパターンから、
165
のように数字のみ取り出すには、どういう数式を使えば良いでしょうか？
【回答】
○数字＋文字列から数字のみを取り出す
=LEFT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
○文字列＋数字＋文字列から数字のみを取り出す
=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
【解説】
=LEFT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
を別の書き方をすると、
=LEFT(A1,
LEN(A1)-LEN(SUBSTITUTE(A1,0,))
+LEN(A1)-LEN(SUBSTITUTE(A1,1,))
+LEN(A1)-LEN(SUBSTITUTE(A1,2,))
+LEN(A1)-LEN(SUBSTITUTE(A1,3,))
+LEN(A1)-LEN(SUBSTITUTE(A1,4,))
+LEN(A1)-LEN(SUBSTITUTE(A1,5,))
+LEN(A1)-LEN(SUBSTITUTE(A1,6,))
+LEN(A1)-LEN(SUBSTITUTE(A1,7,))
+LEN(A1)-LEN(SUBSTITUTE(A1,8,))
+LEN(A1)-LEN(SUBSTITUTE(A1,9,))
)
となります。
LEN(SUBSTITUTE(A1,0,))
式中の↑の部分は、セルA1の文字列から0を除いた文字数をカウントしてます。
LEN(A1)-LEN(SUBSTITUTE(A1,0,))
で、
文字数 − 0を除いた文字数
すなわち、 0の数を計算してます。
全体では、
=LEFT(A1,セルA1中の0〜9の数の合計)
ということになっています。
=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
↑の数式については、以下のページもご参考になるのでは？
