教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010  配列数式の使い方 (パターンを多数ご紹介!)

 

 

 

 

 


 

 

 

 


 

 

 

 

下図を参考に、配列数式で色々な計算をしてみましょう。★配列数式って何?という方はこちら  様々なパターンで配列数式を行うと理解が早いです。

★COUNTAは配列数式では使えませんが、COUNT関数で配列に隣接した空セルを数えることが可能なので、問題ありません。

★ 例1 ★

 

■ 配列数式【SUM】全てのパンの売上金額を出す

{=SUM(C2:C11*D2:D11)}

 

■ 配列数式【IF】【SUM】チョコパンの売上金額を出す

{=SUM(IF(B2:B11="チョコパン",C2:C11*D2:D11))}

 

■ 配列数式【MAX】1日の最高金額を出す

{=MAX(IF(A2=$A$2:$A$11,$C$2:$C$11*$D$2:$D$11))}

日付を手入力するなら {=MAX(IF(A2:A11=DATEVALUE("2015/9/1"),C2:C11*D2:D11))}でもOK

 

■ 配列数式【COUNT】1日の合計金額が1000円以上は何日ある?

{=COUNT(IF(C2:C11*D2:D11>=1000,A2:A11))}

 

■ 配列数式【複数条件】5個以上かつ、合計金額が800円以上は何日ある?

配列数式ではAND/OR関数は使えません。AND条件は(条件)*(条件)、OR関数は(条件)+(条件)とします。

{=COUNT(IF((D2:D11>=5)*(E2:E11>=800),A2:A11))}

 

■ 配列数式【○○が含まれているセルを数える】 ”パン"という文字を含むセルを数える

○○が含まれている場合…はCOUNTIF(セル,"*○○*")を使うことが多いと思いますが、配列数式ではCOUNTIFは使えません。ここではFIND関数かSEARCH関数を使います。

{=COUNT(IF(FIND("パン",B2:B11),A2:A11))}


★例2★

 

■ 配列数式【順位】F列に日付ごとの順位を出す

配列数式ではRANK関数が使えません。代わりにSUM関数を使います。

 

{=SUM(IF(A2=$A$2:$A$11,IF(E2<$E$2:$E$11,1,0)))+1}

 

解説:合計E2がE列の日付が同じ合計値より小さいなら1、そうでないなら0…を繰り返します。9/1の場合、E2(0,0,0,0)なので0,E3(1,0,0,1)なので2です。これでは順位が0位,2位なのでプラス1として1位,2位を出します。

 

★★ 参考サイト:配列数式でRANK関数を使える? ★★

 

 

■ 配列数式【PERCENTRANK関数】日付ごとに上位30%以上に○(つまり70%以上)

=IF(PERCENTRANK(IF(A2=$A$2:$A$11,$E$2:$E$11),E2)>=70%,"○","")

 

★★ 参考サイト:上位○%以上を出すPercentRank関数 ★★

 

なんとなく配列数式を理解できましたでしょうか。配列数式は1つ1つのセルを対象に計算する必要がある(作業列が必要)場合に、1つのセルで計算ができる便利な機能です。ぜひ使ってみてください。

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows