本ページはプロモーションが含まれています
ガスボンベの交換間隔を調べるExcelシートを作りたいんだよ。
例えばA列に日付、B列に交換した実績を書いておいて
B列に何日間連続で記入がなかったかを表示させたいんだけどどうすればいい？
それなら配列数式を使えばできるよ。
配列数式って何？
配列数式は普通の関数じゃできない配列を対象にした複雑な計算が可能になるの。
数式などを入力した時はEnterキーを押すでしょ。
配列数式の場合はCtrl+Shift+Enterを押すんだよ。
この頭文字をとってCSE数式とも呼ばれるよ。
じゃあ、やり方教えて！
じゃあ、まずは配列数式の説明から聞いてね
配列数式の基本的な使い方
配列数式 基礎の基礎
例えば個数と単価が入った表で合計金額を出そうとした場合です。
普通なら個数Ｘ単価＝小計を出してからそれを合計します。
配列数式を使えば簡単に計算できます。
入力内容はこんな感じ。
=SUM(C2:C5*D2:D5)
ここで入力完了する時にEnterキーを押すと
#VALUE
と表示されてしまいます。
正解はCntl+Shift+Enterで確定です。
すると計算結果が表示されます。
ここで数式バーを見てください。
{=SUM(C2:C5*D2:D5)}
と{}でくくって表示されています。
これが「配列数式が使われていますよ」という表示です。
配列数式に条件を追加してみる
同じ表です。
この中でテーブルだけの合計金額を出すにはこちら。
=SUM(IF(B2:B5=”テーブル”,C2:C5*D2:D5,0))
そしてもちろん、Ctrl+Shift+Enter で確定です。
テーブルだけの合計金額が計算できました。
条件を２つにしてみる
表をちょっと追加です。
ここで田中さんが販売した、または販売されたいすの金額の合計を求めます。
「どういう条件だよ」と突っ込まれるかもしれませんが、まぁ、あくまでも例なので(笑)
=SUM(IF((A2:A7=”田中”)+(B2:B7=”いす”),C2:C7*D2:D7,0))
もちろん、Ctrl+Shift+Enterで。←しつこい？
ここで＋で条件をふたつ入れてます。
「＋」はORの意味です。
ANDとORについてはこちらを。
| 論理式 | 記号 | 意味 |
| OR | + | どっちかが条件成立したら含める |
| AND | * | 両方の条件が成立しないと含めない |
条件は２つまでという事ではないです。
ANDとORが混在した式も可能です。
配列数式はいろいろな関数と組み合わせて使用可能ですのでいろいろ試してみてください。
【応用編】 入力されたセルの間隔を数える
さぁ、本題に入ります。
まずはこちらの表をご覧ください。
例題としてガスボンベを交換したのが「中何日だったか？」を調べます。
これを実現するのも配列数式を使います。
こんな感じです。
=IF(ISBLANK(B6),””,ROW()-1-MAX(IF(ISBLANK($B$2:B7)),ROW($B2$2:B7)))
赤線引いたところで配列数式を使ってます。
順番に紹介しますね。
IFとISBLANK
IF(ISBLANK(B6),””,・・・・
の部分はB6のセルがなにも入力されてなかったら何も表示しない。
なにか入力されてたら「・・・・」の計算結果を表示する。
だから「〇を入力」とは書いてますが実際は入力されてたらなんでもいいんですね。
ROW
ROW()
関数の入力されている行番号が何番かを取得します。
MAX
MAX(IF(ISBLANK($B$2:B7)),ROW($B$2:B7))
MAX（）は最大値を取得します。
何の最大値かというと$B$2:B7の間で空白でなかった行番号の最大値です。
つまりは
B列で何か入力されてたら
今の行番号 – 1 – それより上に入力されてた行番号の最大値
を計算するとなります。
よって「中何日」でボンベを交換したのかが計算できます。
この数式をC列にずばっとコピーします。
$B$2と$がついてるのを「なんだこれ？」と思われた方おられるかも。
これは言わばピン止めです。
EXCELはかしこいので普通にB2と書いておくと下の行にコピーした時にB3に変えてくれます。
これをしてほしくない時にB$2と書きます。
$B$2と書いておくと行方向にコピーした時も、列方向にコピーした時もずっと固定だよ。
っていう意味です。
以上、配列数式を使って間隔を開けて入力されたセル同士の間隔を数える方法でした。
Excelの魔術師へ！ さいしょの一歩ほかにもいろいろ書いてます
↓ 下記からご覧ください
サイトマップ
ブックマークして頂けると嬉しいです
よろしくお願いいたします
コメント