今回は、RANK関数～RATE関数までをご紹介しております。
RANK関数
読み方： ランク
RANK(数値,参照,[順序])
数値の大小で順位を算出する
RANK.AVG関数
読み方： ランク・アベレージ
RANK.AVG(数値,参照,[順序])
同順位を平均順位で算出する
RANK.EQ関数
読み方： ランク・イコール
RANK.EQ(数値,参照,[順序])
数値の大小で順位を算出する
RATE関数
読み方： レート
RATE(期間,定期支払額,現在価値,[将来価値],[支払期日],[推定値])
元利均等返済における利率を算出する
CHISQ.TEST関数
読み方： カイスクウェア・テスト
分類： 統計
CHISQ.TEST(実測値範囲,期待値範囲)
カイ二乗検定の上側確率を算出します
テキストファイルなどをインポートして、データを読み込んでみたら、セル内に余計な空白がアチラコチラ入っている。
その空白も、全角や半角が混じっているし、文字と文字の間は半角空白を一つだけで揃えたいといった場合、どのようにしたら、手早く、データを綺麗にすることができるのでしょうか。
サンプルの表をつくってみました。
=TRIM(SUBSTITUTE(B1," "," "))
という数式を設定しました。
その数式をオートフィルで数式をコピーしてD2まで設定しています。
TRIM関数は、セルの前後にある空白を削除してくれます。
これで、B1のようなパターンでは、前後の空白を削除してくれます。
そして、このTRIM関数は、文字と文字の間の空白が複数ある場合は1つだけ残して削除してくれる便利機能も付いているので、B1のように、全角空白2文字の場合は、全角空白1文字にしてくれます。
なお、半角空白と全角空白の組み合わせでも、空白1文字残してくれます。
空白を削除するだけならば、TRIM関数だけでいいのですが、半角空白で揃えたいので、SUBSTITUTE関数をつかって、半角空白に揃えていきます。
SUBSTITUTE関数は、指定した文字に置換してくれる関数です。
文字と文字の間の空白は、半角空白にしたいので、全角空白を半角空白に置換するように引数を設定してあげるだけで、文字の前後の空白と、文字と文字の間の空白を半角空白にすることができました。
ExcelでもAccessでも、データ内に「重複」があって、それを削除したデータを作成する場合には、なかなか面倒だったりします。
Accessの場合、重複を除いたクエリを作成する時は、作成タブの「クエリウィザード」から「重複クエリウィザード」をつかって、重複クエリを作成することが多いかと思います。
この重複クエリウィザードをつかってもいいのですが、クエリデザインで簡単に重複を除いた重複クエリをつくることができます。
次のテーブルをつかって紹介します。
作成タブの「クエリデザイン」をつかって、店舗名フィールドを3つ連続して挿入します。
1つ目の「店舗名」フィールドの集計には「先頭」と設定します。
2つ目の「店舗名」フィールドの集計には「カウント」と設定したら、抽出条件に「>1」と設定します。
この抽出条件を「>1」にすることで、重複しているかしていないかの判断をしているわけです。
なお表示する必要がないので、表示のチェックマークは、オフにしています。
最後の「店舗名」フィールドの集計には「グループ化」と設定します。
たったこれだけの設定で完成しましたので、実行をクリックしてデータシートビューで確認してみましょう。
このように、重複を除いたクエリをつくることができました。
重複クエリウィザードも便利ですが、仕組みや構造を知っていると、簡単につくるのとアレンジすることも可能になりますので、アレコレ試してみるといいかもしれませんね。
Facebookページに書いた、Excelの豆知識（Trivia）です。
7月18日
Excel。
EVEN関数
読み方は、イーブンで、数値を偶数に切り上げる
7月19日
Excel。
EXACT関数
読み方は、イグザクトで、英字の大文字と小文字を区別して文字列が一致するか比較する
7月20日
Excel。
EXP関数
読み方は、イクスポネンシャルで、オイラー数eのべき乗を算出
7月21日
Excel。
EXPONDIST関数
読み方は、エクスポンディストで、指数分布の確率密度関数と累積分布関数を計算する
7月22日
Excel。
EXPON.DIST関数
読み方は、エクスポン・ディストで、指数分布の確率密度関数と累積分布関数を計算する
7月23日
Excel。
FACT関数
読み方は、ファクトで、数値の階乗を算出する
7月24日
Excel。
FACTDOUBLE関数
読み方は、ファクトダブルで、数値の二重階乗を算出する
Excelの関数は、どんどん追加されていきますね。
Office Insider版の、Windows2203:Build 15104で追加された、
「TOROW関数」は、次のような時に威力を発揮しそうです。
B2:B4に上半期のデータが入力されています。
C2:C4に下半期のデータが入力されています。
このような、表を、列方向に年ごとに並べて表示したいとしたら、イチイチ、コピーするのは面倒ですし、OFFSET関数などをつかって数式をつくるとしても、結構面倒です。
まして、このような処理だけのために、Excel VBAでプログラムをつくるのも、面倒です。
そこで、TOROW関数をつかうと、簡単に処理することができます。
A9の数式は、
=TOROW(B2:C4,0,FALSE)
あとは、スピル機能によって、数式が設定されてるので、オートフィルで数式をコピーする必要はありません。
たった、一行で完成することができます。
TOROW関数の引数を確認しておきましょう。
読み方は、トゥロウ。
分類： 検索/行列
TOROW(array,[ignore],[scan_by_column])
最初の引数「array」は、配列（表）ですね。
2番目の引数の「ignore」は、特定の種類のデータを無視するかどうかを判断させます。
0だと、すべての値を保持します。
1だと、空白を無視します。
2だと、エラーを無視します
3だと、空白とエラーを無視します。
最後の引数「scan_by_column」は、行順か列順かを設定します。
FALSEが行順で並べていきます。
TRUEにすると、列順で並べることできます。
次の表のように、B列の氏名の列のデータ内で、重複しているデータがあったら、見た目でわかりやすくしたいわけです。
また、そのセルだけでなく、行全体を塗りつぶしたい場合、どのようにしたらいいのでしょうか？
そして、用意されている条件では対応できないので、数式で条件を作る必要があります。
では、A2:C11を範囲選択して、ホームタブの条件付き書式にある「新しいルール」をクリックします。
「新しい書式ルール」ダイアログボックスが表示されます。
ルールの内容のボックスには、重複を判定するための数式を設定します。
=COUNTIF($B$2:$B$11,$B2)>=2
あとは、書式ボタンをクリックして、塗りつぶしたい書式を設定したら、条件設定が完成します。
最後に、OKボタンをクリックしましょう。
これで、重複したデータに該当したデータの行全体を塗りつぶしに設定することができました。
COUNTIF関数をつかうのは、B2が範囲の中で、2件以上あれば、重複ということがわかるからです。
D2に、
=COUNTIF($B$2:$B$11,$B2)>=2
を設定して、オートフィルで数式をコピーすると、「TRUE」と「FALSE」を算出してくれます。この「TRUE」が重複していることを合わせしています。
ポイントは2つ。
最初の引数は、範囲なので、「$B$2:$B$11」。
絶対参照を忘れないようにします。
2つ目の引数は、検索条件で、B2。
行全体を塗りつぶしたいので、「$B2」と列番号を固定する必要があります。
条件付き書式の条件に様々な数式を応用して設定することで、わかりやすい資料をつくることができますので、色々試してみるのはいかがでしょうか。
CHISQ.INV.RT関数
読み方： カイスクウェア・インバース・ライトテール
分類： 統計
CHISQ.INV.RT(確率,自由度)
上側累積確率からカイ二乗分布のパーセント点の値を逆算します
＜Facebookページ＞
Facebookページに書いた、Excelの豆知識（Trivia）です。
7月11日
Excel。
ENCODEURL関数
読み方は、エンコードユーアールエルで、URL形式でエンコードされた文字列を返す
7月12日
Excel。
EOMONTH関数
読み方は、イーオマンスで、月数後の月末日を算出する
7月13日
Excel。
ERF関数
読み方は、イーアールエフで、誤差関数の積分値を算出
7月14日
Excel。
ERFC関数
読み方は、イーアールエフシーで、相補誤差関数の積分値を算出します。
7月15日
Excel。
ERFC.PRECISE関数
読み方は、イーアールエフシー・プリサイズで、相補誤差関数の積分値を算出します。
7月16日
Excel。
ERF.PRECISE関数
読み方は、イーアールエフ・プリサイズで、誤差関数の「0～下限」までの積分値を算出します。
7月17日
Excel。
ERROR.TYPE関数
読み方は、エラー・タイプで、エラーのタイプを表す数値を算出する
販売している「商品A」と、「商品A」をカスタマイズした「商品A改良」の評価値のデータ表があります。
見た目、「商品A改良」のほうが、評価値がいいようにみることができますし、商品Aの平均値と商品A改良の平均値の差はたった「6ポイント」程度しかありません。
差はあるのかを手早く検証したいわけです。
また、「商品A改良」のほうが「商品A」より良いハズという仮定で算出していきます。
なお、説明上データ数が少なくしていますが、本来データは多いに越したことはありません。
F1にT.TEST関数をつかった数式を設定しました。
=T.TEST(B2:B11,C2:C11,1,1)
この手の統計や分析の関数は、引数自体がとてもシンプルで算出してくれるのですが、引数の意味がわかりにくいので確認しておきましょう。
1番目の引数は、配列1。一つ目の範囲なので、B2:B11
2番目の引数は、配列2。比較したい範囲なので、C2:C11
3番目の引数は、「検定の指定」で尾部のことです。
片側分布なら「1」。
両側分布なら「2」とします。
配列1と配列2のどっちかが大きいかを調べたいなら「片側分布」の1。
配列1と配列2に差があるのかを調べたいなら「両側分布」の2。
つかいわけることができます。
「商品A」を改良したわけですから、「商品A改良」のほうが、きっと数値はいいだろうという想定したので、「1」を採用しています。
4番目の引数は、「検定の種類」
今回は、同じ人が「商品A」と「商品A改良」をチェックしているとします。
同じ人が2回チェックした場合のデータならば「1」の対を採用するわけです。
「等分散の2標本」なら「2」を設定します。
等分散とは、母集団の分散が等しいと仮定できる場合という意味です。
なので、逆に仮定できない場合は、「3」の「非等分散の2標本」を設定します。
算出結果は、「0.0112873」
確率は0.05（5%）より0.01(1%)と小さいので、「差がある」といえることが数値的に検証できたというわけです。
5%よりも大きければ、「差はナイ」ということを意味します。
データと平均値だけの資料ではもったいないので、日頃使っているデータから違ったことが見えてくるかもしれませんね。
CHISQ.INV関数
読み方： カイスクウェア・インバース
分類： 統計
CHISQ.INV(確率,自由度)
カイ二乗分布の下側確率から確率変数を算出します
Excelで何気なく行や列を非表示にしたら、図形やグラフが、勝手に縮んだり、消えちゃったりするので、困ってしまうことがあります。
試しにやってみましょう。
今回の場合は、まだ見えていますが、図形の高さよりも大きい行数を非表示にすると、図形は見えなくなってしまいます。
確かに再表示すれば、復活はしますが、図形は非表示に連動しないで、そのままの大きさを保ってほしい場合、どうしたらいいのでしょうか？
ちなみに、今回は図形ですが、オブジェクトなら共通なので、グラフも非表示の影響を受けます。
では、図形を右クリックします。
たった、これだけです。
改めて、行を非表示にしてみましょう。
ちょっとしたことですが、イライラする前に、プロパティを変更するほうがよさそうですね。
データベースの機能はExcelにもたくさん用意されています。
データベースの機能の為、あくまでも行というレコードに対して処理できるけど、列というフィールドに対しては対応してくれません。
そのため、応用できないものもあります。
例えば、データタブにある「重複の削除」です。
次の表をつかって、実行してみます。
実行すると、「重複の削除に関する警告」ダイアログボックスが表示されます。
どちらを選択して、重複の削除ボタンをクリックしても、エラーメッセージが表示されます。
そこで、「UNIQUE関数」をつかうと、手早く、列方向の重複を除いたデータを抽出することができます。
=UNIQUE(B2:G2,TRUE,FALSE)
設定後、スピル機能によって、数式がコピー（スピル）されます。
UNIQUE関数の引数も確認しておきましょう。
最初の引数は、配列。範囲選択のことだと思ってもらえればいいので、B2:G2と設定します。
スピル機能によって、絶対参照は不要です。
2つ目の引数は、列の比較です。
TRUEは一意の列を返す。FALSEは一意の行を返す。
列が対象なので、TRUEと設定しました。
3つ目の引数は、回数の指定です。
TRUEは、「1回だけ出現するアイテムだけを返す」。
FALSEは、「個別のアイテムをすべて返す」。
を設定することができます。
重複していないデータのみを抽出したい場合は、「TRUE」を設定しますが、重複データも含めたデータを抽出したいので、今回は「FALSE」を設定しました。
たったこれだけですが、手早く抽出すうることができる効果抜群の関数ですね。
このUNIQUE関数は、「重複の削除」と同じように、行方向に対しても対応してくれます。
ちょっとした注意点として、比較的新しい関数なので、お使いのExcelには無いかもしれません。
CHISQ.DIST.RT関数
読み方： カイスクウェア・ディスト・ライトテール
読み方： カイスクウェア・ディストリビューション・ライトテール
分類： 統計
CHISQ.DIST.RT(x,自由度)
カイ二乗分布の上側確率を算出します
列内にある、空白セルに文字を入力したい場合、少なければ、コピーするとか入力でもいいのですが、空白セルの数も多く、しかも、「飛び地」だとすれば、面倒な処理といえます。
例えば、次の表。
しかも飛び地状態ですね。
今回は説明用のサンプルなので、たった5件のデータですが、もっといっぱいあるとします。
この空白セルに「未提出」という文字を入力・設定したい場合、コピーや入力では時間がかかってしまいます。
そこで、Excel VBAでプログラムを作ってみるという方法もあります。
プログラム文は大変なのではと考えてしまうところですが、次の一行で、終わらせることができます。
Sub 未提出()
Range("c2:c6").SpecialCells(xlCellTypeBlanks) = "未提出"
End Sub
まずは、実行してみましょう。
プログラム文を確認しておきましょう。
Range("c2:c6").SpecialCells(xlCellTypeBlanks) = "未提出"
Range("c2:c6")は、範囲選択ですね。
この範囲が対象になるわけです。
SpecialCells(xlCellTypeBlanks) = "未提出"
この「SpecialCells(xlCellTypeBlanks)」で、セルが空白なのかどうなのかを知ることができます。
空白だったら、「= "未提出"」
すなわち、未提出と設定することができたというわけです。
このように、データ量が多くて、やる処理は単純で反復のような場合には、Excel VBAでプログラムをつくってみるのもいいかもしれませんね。
Facebookページに書いた、Excelの豆知識（Trivia）です。
7月4日
Excel。
DSTDEVP関数
読み方は、ディースタンダードディブピーで、条件を満たすレコードの標準偏差を算出します。
7月5日
Excel。
DSUM関数
読み方は、ディーサムで、条件を満たすレコードの合計を算出します。
7月6日
Excel。
DURATION関数
読み方は、デュレーションで、定期的に利子に支払われる証券の年間マコーレー係数を算出します。
7月7日
Excel。
DVAR関数
読み方は、ディーバリアンスで、条件を満たすレコードの不偏分散を算出します。
7月8日
Excel。
DVARP関数
読み方は、ディーバリアンスピーで、条件を満たすレコードの標本分散を算出します。
7月9日
Excel。
EDATE関数
読み方は、イーデイトで、何か月後の日付を算出する
7月10日
Excel。
EFFECT関数
読み方は、エフェクトで、実効年利率を算出します。
2種類のデータには、関係性があるのかを表すものに【共分散】というのがあります。
【相関係数】の元になる値ですね。
関係性とは、例えば、「身長と体重」とか「気温と売上」など、「Aという商品は、気温が上がると売り上げも上がるのか」といったものです。
この共分散を算出するには、
COVARIANCE.P関数（コバリアンス・ピー）
をつかうことで、簡単に算出することができます。
実際にCOVARIANCE.P関数をつかってみることにします。
C12に設定した数式は、
=COVARIANCE.P(B2:B10,C2:C10)
算出結果は、「3991.1」
この値は何なのかというと、
正の値だったら「配列1の来店客数が大きいければ、配列2の売上高も大きい傾向になる」
逆に、
負の値が算出されたとしたら「配列1の来店客数が大きいければ、配列2の売上高は、逆に小さい傾向にある」
という関係性がわかるというわけです。
なお、COVARIANCE.P関数の「P」は、Populationの「P」です。
データの一部をサンプルにするのではなく、データ全体を対象にするという意味です。
データの一部だけをサンプルとして算出する場合には、COVARIANCE.S関数をつかいます。
データを読み込んでみたら、データがセルごとに分かれてなく、セル内にまとまっていると、不便なことが結構発生します。
例えば、次の表。
受講科目ごとに何件あるのか集計したいのですが、セル内に「Word」や「Excel」といった科目名が、列ごとに区切らえれず、まとめて入力されています。
そのため、集計することができません。
では、どのようにしたら、手早く科目名ごとに集計することができるのでしょうか
受験希望欄に「Word」だったらという条件で件数を算出したいので、使用する関数は「COUNTIF関数」をつかえば、集計することはできます。
ここで、考えないといけないのが、どのような条件を設定すればいいのでしょうか。
そこで、次のような条件はどうでしょうか。
「セル内にWordという文字が含まれている」
この条件ならば、件数を算出することができそうです。
では、どのようにしたら、「含まれる」という条件を設定することができるのでしょうか
「含まれる」を表現するには「ワイルドカード」をつかうことで、対応することができます。
E2に設定した数式を確認していきます。
=COUNTIF($B$2:$B$6,"*"&D2&"*")
設定後、オートフィルで数式をコピーします。
たった、これだけなのですが、COUNTIF関数でワイルドカードを使用するということに気が付かないと、算出するのに時間がかかってしまいます。
最後に、COUNTIF関数の引数の内容を確認しておきましょう。
最初の引数は、範囲。
今回は、B列が対象なので「$B$2:$B$6」。
範囲選択ですが、オートフィルで数式をコピーするので、絶対参照を設定することも忘れないようにします。
2つ目の引数は、検索条件。
「D列の文字を含む」というのが条件なので、D2を「*（アスタリスク）」で囲います。
「*（アスタリスク）」は、「”（ダブルコーテーション）」をつかって挟みます。
さらに文字結合をする「&（アンパサンド）」をつかい、条件が完成します。
ワイルドカードをつかうことで、条件の幅が広がります。
ただ、B列のように、セル内にまとめて入力するよりも、別々の列にするほうが、Excelの様々な機能を使いやすくなります。
表を、データベース化することも、選択肢の中にいれておくのもいいかもしれませんね。
連番を入力する場合、オートフィル機能をつかって、簡単に設定できます。
ただし、この連番が例えば、1から100000（十万）までで入力しようとしたら、どうしたら効率よく入力することができるのでしょうか？
次の表があります。
ROW関数をつかった数式を設定したとしても、結局、100000までドラッグしなければなりません。
わざわざ、この程度の処理だけのために、Excel VBAでプログラムを作るのも、作業対効果として割に合いません。
そこで、フィル機能の「連続データの作成」をつかうことで、解決します。
ホームタブのフィルにある「連続データの作成」をクリックします。
連続データダイアログボックスが表示されます。
列方向に連番を設定したいので、範囲には「列」と設定します。
連番なので、加算させますから、種類は「加算」と設定します。
増分値は、連番ですから、「1」のままにします。
最後に、停止値は、「100000」と設定したら、OKボタンをクリックします。
CHISQ.DIST関数
読み方：カイスクウェア・ディスト
読み方：カイスクウェア・ディストリビューション
分類： 統計
CHISQ.DIST(x,自由度,関数形式)
カイ二乗分布の確立を算出します。
データを読み込んでみたら、日付ではなくて、年月日が別々の列に入力されていました。
DATE関数をつかえば、年月日を結合させて、日付のデータにすることができます。
そこで、Excel VBAでプログラムをつくって、データを読み込んだのと同時に日付をつくってしまえば、作業効率が改善しそうです。
Excel VBAのプログラム文で、DATE関数をつかいそうですが、効率がいいものがありますので、次のようにプログラム文をつくってみました。
Sub 日付()
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
Cells(i, "e") = DateSerial(Cells(i, "a").Value, Cells(i, "b").Value, Cells(i, "c").Value)
Next
End Sub
参考として、A列からC列までの年月日のデータをつかって、E列に日付をつくるようにしましたので、まずは、実行して確認します。
では、プログラム文を確認します。
基本的には、一行だけのシンプルなプログラム文です。
Dim i As Long
Dim lastrow As Long
変数宣言です。
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
変数のlastrowには、データの最終レコード行を代入します。
このデータは次のFor～To～Next文での繰り返しカウントとしてつかいます。
For i = 2 To lastrow
Cells(i, "e") = DateSerial(Cells(i, "a").Value, Cells(i, "b").Value, Cells(i, "c").Value)
Next
繰り返し文の中は、DateSerialプロパティをつかって、日付にしています。
DATE関数と同じ使い方なのが、DateSerialプロパティです。
DateSerialプロパティの引数は、
DateSerial(年の値,月の値,日の値)
となっていますので、それぞれの列を当てはめてあげればいいだけです。
DATE関数でも、全く問題はありませんが、データ量が多い、毎回同じ処理をして面倒などの場合は、簡単な処理であっても、Excel VBAでプログラム文をつくってみてもいいかもしれませんね。
皆様に閲覧していただいた項目の2022年6月TOP10をご紹介
1位
Excel。セル内の文字に半角が含まれているか、手早く知るにはどうしたらいい
https://infoyandssblog.blogspot.com/2022/06/excelhalf-size.html
2位
Excel。新しい関数TOCOL関数をつかえば、表を1列で表示することができます。
https://infoyandssblog.blogspot.com/2022/06/exceltocol1tocol.html
3位
Excel。VBA。表をコピーする。値でコピーするにはどうしたらいいの
https://infoyandssblog.blogspot.com/2022/06/excelvbacopy.html
4位
Excel。一日のタイムスケジュールを管理する24時間横棒グラフを作ってみる
https://infoyandssblog.blogspot.com/2016/03/excel24hour-schedule24.html
5位
Excel。手早く指定行ごとの累計を算出するには、どうすればいいの。
https://infoyandssblog.blogspot.com/2022/06/excelcumulative.html
6位
Excel。折れ線グラフを交点0からスタートさせるには？
https://infoyandssblog.blogspot.com/2013/07/excel0.html
7位
Excel。相関図（相関グラフ）を作るにはどうしたらいいの？
https://infoyandssblog.blogspot.com/2019/01/excelcorrelation-diagram.html
8位
Excel。散布図の近似曲線。外れ値を除いて描きたいけどどうしたらいい？
https://infoyandssblog.blogspot.com/2016/08/excelscatter-plot.html
9位
Excel。列ごとに違う行にある値の減算を手早く算出するにはどうすればいいの。
https://infoyandssblog.blogspot.com/2022/06/excelsubtraction.html
10位
Excel。同じ列内での複数条件をつかって手早く件数を算出したい
https://infoyandssblog.blogspot.com/2022/06/excelmultiple-conditions.html
Facebookページに書いた、Excelの豆知識（Trivia）です。
6月27日
Excel。
DVARP関数
読み方は、ディーバリアンスピーで、条件を満たすレコードの標本分散を算出
6月28日
Excel。
EDATE関数
読み方は、イーデイトで、何か月後の日付を算出する
6月29日
Excel。
EFFECT関数
読み方は、エフェクトで、実効年利率を算出
6月30日
Excel。
ENCODEURL関数
読み方は、エンコードユーアールエルで、URL形式でエンコードされた文字列を返す
7月1日
Excel。
EOMONTH関数
読み方は、イーオマンスで、月数後の月末日を算出する
7月2日
Excel。
ERF関数
読み方は、イーアールエフで、誤差関数の積分値を算出します。
7月3日
Excel。
ERFC関数
読み方は、イーアールエフシーで、相補誤差関数の積分値を算出します。
大きなクロス表で、指定したセルがどこにあるのかを見つけるのは、なかなか見つけにくいですね。
セルを塗りつぶしすることができれば、手早くそのセルの内容を把握確認することができます。
10月2日14時のセルを塗りつぶしすることで、該当するセルをすぐに確認することができるわけですが、どのようにしたらいいのでしょうか？
条件付き書式をつかうことで、対応することができます。
ポイントは、どのような数式をつかった条件をつくるかです。
では、早速設定していきましょう。
条件付き書式を設定するところを範囲選択しますので、B5:D9を範囲選択したら、ホームタブの条件付き書式をクリックします。
「新しい書式ルール」ダイアログボックスが表示されます。
「数式を使用して、書式設定するセルを決定」をクリックしたら、ルールを設定します。
数式を入力するボックスに、次の数式を設定します。
=AND(B$4=$B$2,$A5=$B$1)
あとは、書式ボタンをクリックして、セルを塗りつぶしする色を設定します。
最後に、OKボタンをクリックすれば完成です。
設定したAND関数の数式を確認しておきます。
AND関数は、引数内で設定した条件をすべて満たした場合「TRUE」と判断する関数です。
最初の引数 「B$4=$B$2」で、列を対象として検索しています。
列方向に参照が変わってほしいので、行番号だけを固定した、複合参照として設定します。
B2は、セル参照先が変化してほしくないので、絶対参照を設定します。
次の引数は、日付である行を検索します。
そのため、列は固定したいので、列を固定した複合参照をつかった「$A5=$B$1」と設定します。
クロス表から指定した交差する値を算出するならば、INDEX＋MATCH関数をつかいますが、セルの値を抽出するわけではないので、INDEX+MATCH関数など複雑な関数を使わなくても大丈夫です。
条件付き書式のルールに、色々な数式を設定することで、使い勝手がよくなるかもしれませんので、試してみると面白いかもしれませんね。
「2-1」のようなハイフンの枝付きデータを連番で設定したいとします。
そこで、次のようなプログラム文をつくってみました。
Sub 日付ではなく文字()
Dim i As Integer
For i = 2 To 6
Cells(i, "a").Value = "2-" & i - 1
Next
End Sub
For～Next文で、繰り返し処理をしています。
「Cells(i, "a").Value = "2-" & i - 1」は、A2に、「2-1」という文字を入力する
という処理ですね。
ところが、実行してみると、おかしな表示になります。
原因は、「2-1」と入力すると「2/1」と同じように、Excelは自動的に日付が入力されてきたと判断して、日付型の「月日」という表示形式に変わってしまったわけです。
ただ、今回は、「2-1」という文字で表示したいわけですね。
そこで、文字型の表示形式を先に設定しておけば、日付で表示されることはありません。
では、次のように、プログラム文を修正していきます。
Sub 日付ではなく文字()
Dim i As Integer
For i = 2 To 6
Cells(i, "a").NumberFormat = "@"
Cells(i, "a").Value = "2-" & i - 1
Next
End Sub
先ほどのプログラム文に
「Cells(i, "a").NumberFormat = "@"」を追加しました。
NumberFormat = "@" とNumberFormatプロパティをつかうことで、文字型にすることができます。
では、改めて実行して確認してみましょう。
希望通り「2-1」という文字で入力することができました。
Excelは、VBAで処理をしたとしても、Excelで処理した時と同じように、表示形式も考える必要があります。