Excel。複数列のデータから、重複を除いた件数を手早く求めたい【overlapping】

Excel。複数列のデータから、重複を除いた件数を手早く求めたい

＜SUM+COUNTIF関数＞

重複を除いた件数を算出する方法は色々あります。

今回は、複数列にあるデータから重複を除いた件数を手早く算出するには、どのようにしたらいいのでしょうか。

B2の入力されている「箱根」は、4つありますが、これを1として数えたいわけです。

つまり、重複されているものは1件として数えるわけです。

「重複を除く」という条件で数えたいわけですから、COUNTIF関数をつかうわけですが、COUNTIF関数だけは対応することができません。

そこで、D8には、次の数式をつくってみました。

=SUM(1/COUNTIF(B2:D6,B2:D6))

これで、8件と算出することができました。

数式の仕組みを確認します。

SUM関数は、いいとして、COUNTIF関数の動きがどのようになっているのか確認します。

F1を起点として、先程の表をコピーしました。

G2に、

=COUNTIF(B2:D6,B2:D6)

とSUM関数内のCOUNTIF関数の数式部分を抽出した結果を算出してみました。

箱根は4件あるということがわかります。

1のところは、1件しかないというわけです。

続いて、数式を修正します。

=1/COUNTIF(B2:D6,B2:D6)

算出された数値を全部合算することで、8と算出されるという仕組みです。

先程、4と数えられたものを1としたいわけです。

なので、1/4とすれば0.25となります。

0.25が4つあるので、1とすることができるという数式をつくってみたというわけです。

そのため、

=SUM(1/COUNTIF(B2:D6,B2:D6))

という数式で、重複を除いた件数を算出できたというわけです。

Excel。10進数を2進数にするHEX2BIN関数は99までしかできません。【Binary number】

Excel。10進数を2進数にするHEX2BIN関数は99までしかできません。

＜HEX2BIN関数＞

進数というのがあります。

ネットワークとか、PC系でお馴染みの2進数です。

10進数を2進数に変換する関数が、Excelにはあります。

それが、HEX2BIN関数です。

ただ、あまり万能とはいかないようです。

A列には、通常の数値が入力されています。

通常の数値は10進数です。

B2には、次の数式を設定しました。

=HEX2BIN(A2,8)

あとは、オートフィルで数式をコピーします。

HEX2BIN関数の引数は、

HEX2BIN(数値,[桁数])

というようになっています。

数値には、A2を設定しました。

桁数は、2進数は、4桁。

オクテットで表示しますので、4と今回は設定しました。

10進数の2は、2進数では、「00000010」と表示されます。

大きな問題はありませんが、A8に100と入力してみると、＃NUM！というエラーが表示されています。

どうやら2進数で表示することができないようです。

なので、実際にお仕事などで使う場合には、アプリの電卓か、関数電卓をつかうほうがいいように感じられます。

Excel・Word・PowerPoint。図の挿入のSmartArtの写真を移動する・サイズ変更するには【Youtube】

Excel・Word・PowerPoint。図の挿入のSmartArtの写真を移動する・サイズ変更するには

＜便利な機能＞

色々なパターンのSmartArtが用意されています。

図（写真）を挿入することができるSmartArtがありますけど、その挿入した写真をサイズ変更したり、ちょこっとズラす（移動）するには、どうしたらいいのでしょうか。

Excel。複数条件「～かつ～」のAND条件が成立したら行全体を塗りつぶしたい。【fill in】

Excel。複数条件「～かつ～」のAND条件が成立したら行全体を塗りつぶしたい。

＜条件付き書式+AND条件＞

次の行があります。

店舗名が「新宿店」で商品名が「消しゴム」のデータをわかりやすくしたいので、行全体を塗りつぶすことにしました。

では、どのようにしたらいいのでしょうか。

条件によって、塗りつぶすわけですから、「条件付き書式」をつかうわけです。

その条件式を考えるわけですね。

「新宿店で”かつ”消しゴム」という条件です。

このように「～かつ～」は、AND関数をつかうことで、対応することができます。

では、条件付き書式を設定します。

A2:D11を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択したら、条件式を設定します。

設定する条件式は、

=and($B2="新宿店",$C2="消しゴム")

あとは、書式ボタンをクリックして、塗りつぶす色を選択します。

OKボタンをクリックして完成です。

このように、複数条件で合致するデータ全体を塗りつぶしたい場合には、AND条件をつかった条件付き書式で解決できます。

設定したAND条件の数式について補足をします。

=and($B2="新宿店",$C2="消しゴム")

$B2や$C2のように、列固定の複合参照に設定することで、行全体を塗りつぶすことができます。

Excel。2024/4/21-4/27にNETWORKDAYS関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/4/21-4/27にNETWORKDAYS関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

4月21日

Excel。

NEGBINOMDIST関数

読み方： ネガバイノムディスト　　

読み方： ネガティブバイノミアルディストリビューション

分類： 互換性 

NEGBINOMDIST(失敗数,成功数,成功率)

負の二項分布の確率を算出します　

4月22日

Excel。

NEGBINOM.DIST関数

読み方： ネガバイノム・ディスト　　

読み方： ネガティブバイノミアル・ディストリビューション

分類： 統計 

NEGBINOM.DIST(失敗数,成功数,成功率,関数形式)

負の二項分布の累積確率か確率密度を算出します　

4月23日

Excel。

NETWORKDAYS関数

読み方： ネットワークデイズ　　

分類： 日付時刻 

NETWORKDAYS(開始日,終了日,[祭日])

日付の間の稼働日数を算出します　

4月24日

Excel。

NETWORKDAYS.INTL関数

読み方： ネットワークデイズ・インターナショナル　　

分類： 日付時刻 

NETWORKDAYS.INTL(開始日,終了日,[週末],[祭日])

週末(曜日指定OK)と祝日を除いた日付間の日数を算出する　

4月25日

Excel。

NOMINAL関数

読み方： ノミナル　　

分類： 財務 

NOMINAL(実行利率,複利計算期間)

名目年利率を算出します　

4月26日

Excel。

NORMDIST関数

読み方： ノーマルディスト　　

読み方： ノーマルディストリビューション

分類： 互換性 

NORMDIST(x,平均,標準偏差,関数形式)

平均と標準偏差に対する正規分布の確率を算出します　

4月27日

Excel。

NORM.DIST関数

読み方： ノーマル・ディスト　　

読み方： ノーマル・ディストリビューション

分類： 統計 

NORM.DIST(x,平均,標準偏差,関数形式)

平均と標準偏差に対する正規分布の確率を算出する

Excel。HEX2OCT関数で16進数を8進数に変換できます。【HEX2OCT】

Excel。HEX2OCT関数で16進数を8進数に変換できます。

＜関数辞典：HEX2OCT関数＞

HEX2OCT関数

読み方： ヘックストゥオクト　　

読み方： ヘキサデジマルトゥオクタル

分類： エンジニアリング 

HEX2OCT(数値,[桁数])

16進数を8進数に変換する

Access。クエリ。日付を元号表示にするには、どうしたらいいのでしょうか。【Date】

Access。クエリ。日付を元号表示にするには、どうしたらいいのでしょうか。

＜Access：Format関数＞

Excelでは日付を元号表示にするのには、セルの書式設定ダイアログボックスにある表示形式をつかうことで、手早く、グレゴリオ暦から和暦の元号表示にすることができます。

では、Accessの場合、どのようにしたらいいのでしょうか。

Accessには、セルの書式設定ダイアログボックスはありません。

次のテーブルの納品日をクエリで、元号表示にしていきます。

作成タブのクエリデザインで、クエリをつくります。

演算フィールドをつくります。

Accessで、表示形式を設定できるのが、「Format関数」です。

演算フィールドには、次のような演算式を設定します。

納品日(元号): Format([納品日],"ggge¥年m¥月d¥日")

では、表示をデータシートビューに切り替えますので、実行します。

元号で表示されていることが確認できます。

Format関数をつかうことで、ExcelのTEXT関数のように表示形式を変更することができます。

それでは、Format関数を確認しておきましょう。

納品日(元号): Format([納品日],"ggge¥年m¥月d¥日")

Format関数自体の引数自体は難しくはありません。

ただ、ExcelとAccessで表示形式が異なるので、注意が必要です。

Accessでは、「¥」の後に続く文字列を表示するという考え方です。

「¥年」とすることで、「年」と表示することができるというわけです。

あと、元号の「ggge」は、ExcelでもAccessと同じです。

表示形式を変更したい場合、Accessでは、Format関数をつかい、さらに単位など表示したい文字列は、「¥」をつけるという仕組みになっています。

Excelのショートカットキー。Shift＋F1～F6を紹介【shortcut】

Excelのショートカットキー。Shift＋F1～F6を紹介

＜Shiftキー＋Function系キー＞

作業効率もUPする、知っていると便利なショートカットキー。

Shift＋F1

ポップヒントを表示します

Shift＋F2

メモをセルに表示する

Shift＋F3

関数の挿入ダイアログボックスを表示

Shift＋F4

検索を繰り返す

Shift＋F5

検索と置換ダイアログボックスが表示されます

Shift＋F6

リボンにキーボード操作できるキーを表示　Altキーと同じ

Excel。16進数を10進数に変換するのが、HEX2DEC関数です。【HEX2DEC】

Excel。16進数を10進数に変換するのが、HEX2DEC関数です。

＜関数辞典：HEX2DEC関数＞

HEX2DEC関数

読み方： ヘックストゥデック　　

読み方： ヘキサデジマルトゥデジマル

分類： エンジニアリング 

HEX2DEC(数値)

16進数を10進数に変換する

Excel。住所録からラベルをつくるので、複数セルを改行して、一つに結合したい【label】

Excel。住所録からラベルをつくるので、複数セルを改行して、一つに結合したい

＜TEXTJOIN＋CHAR関数＞

住所録から、ラベルをつくりたいと考えました。

複数のセルを一つのセルに結合しますが、結合ごとに改行をいれたいわけです。

CONCAT関数だと、改行するたびに、改行を意味する、CHAR(10)を設定しなければなりません。

そこで、TEXTJOIN関数をつかってみることにします。

B7に次の数式を設定します。

=TEXTJOIN(CHAR(10),TRUE,A2:B2,C2&" 様")

あとは、オートフィルで数式をコピーします。

これで、手早くセルを結合できました。

また結合の間位に改行をいれることもできました。

なお、結合だけの表示になっている場合には、ホームタブにある、「折り返して全体を表示する」をクリックすることで、改行されていることが確認できます。

では、設定した数式を確認します。

=TEXTJOIN(CHAR(10),TRUE,A2:B2,C2&" 様")

TEXTJOIN関数は、セル結合する関数です。

最初の引数は、「区切り文字」です。

結合した時に区切り文字を設定することができます。

今回は、改行したいわけです。改行の文字コードは、CHAR関数をつかって、CHAR(10)とすることで、改行の文字コードをいれることができます。

2つ目の引数は、「空のセルは無視」。

空のセルを無視するならば、TRUEを設定します。

3つ目以降の引数は、結合するセル範囲です。

今回は、氏名の後に「 様」を表示したいので、テキスト1に「A2:B2」。

テキスト2に、「C2&" 様"」と設定しました。

Excel。2024/4/14-4/20にMONTH関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/4/14-4/20にMONTH関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

4月14日

Excel。

MODE.SNGL関数

読み方： モード・シングル　　

分類： 統計 

MODE.SNGL(数値1,[数値2],…)

最頻値を算出します　

4月15日

Excel。

MONTH関数

読み方： マンス　　

分類： 日付時刻 

MONTH(シリアル値)

日付から月を算出します　

4月16日

Excel。

MROUND関数

読み方： エムラウンド　　

分類： 数学/三角 

MROUND(数値,倍数)

指定した数値の倍数で四捨五入します　

4月17日

Excel。

MULTINOMIAL関数

読み方： マルチノミアル　　

分類： 数学/三角 

MULTINOMIAL(数値1,[数値2],…)

多項係数を算出します　数値の和の階乗と数値の階乗の積との比

4月18日

Excel。

MUNIT関数

読み方： エムユニット　　

読み方： マトリック ユニット

分類： 数学/三角 

MUNIT(次元)

指定した次元の単位行列を算出する　

4月19日

Excel。

N関数

読み方： エヌ　　

読み方： ナンバー

分類： 情報 

N(値)

数値または型に対応する数値を算出します　

4月20日

Excel。

NA関数

読み方： エヌエー　　

分類： 情報 

NA()

つねにエラー値#N/Aを返す

Word。Excelにはない、拝啓～敬具などの「あいさつ文」を知っていると、社外文書が手早くつくれます。 【Youtube】

Word。Excelにはない、拝啓～敬具などの「あいさつ文」を知っていると、社外文書が手早くつくれます。

＜あいさつ文＞

ビジネス文書の社外向け文書で、お馴染みのあいさつ文。

そのあいさつ文を

手早く挿入することが、Wordならできるんです。

ちなみに、Excelではできません。

Excel。VBA。指定の文字列が含まれているかを、手早く確認したい。【InStr】

Excel。VBA。指定の文字列が含まれているかを、手早く確認したい。

＜Excel VBA:InStr関数＞

セル内に、該当する文字列がセル内に含まれているならば、「○」というような判定を手早く行いたいわけです。

そこで、今回は、Excel VBAでプログラムをつくって対応していきます。

次のようなプログラム文をつくってみました。

Sub 八王子市()

    Dim i As Long

    Dim lastrow As Long

    

    lastrow = Cells(Rows.Count, "a").End(xlUp).Row

    For i = 1 To lastrow

        If InStr(Cells(i, "a"), "八王子市") <> 0 Then

            Cells(i, "b") = "○"

        End If

    Next

End Sub

では、実行します。

このように、A列の住所に八王子市が含まれているデータに「○」が表示されていることが確認できます。

大量なデータなどで、オートフィルターで抽出して…という処理でもいいかもしれませんが、定期的に大量なデータを使う場合などには、Excel VBAのプログラムを用意しておくといいかもしれません。

では、プログラム文を確認します。

変数の宣言文です。

Dim i As Long

Dim lastrow As Long

lastrow = Cells(Rows.Count, "a").End(xlUp).Row

lastrowは、A列のデータの一番下の行番号を代入させます。

そして、このあとの、繰り返し文で、繰り返す上限として使っています。

For i = 1 To lastrow

    If InStr(Cells(i, "a"), "八王子市") <> 0 Then

        Cells(i, "b") = "○"

    End If

Next

For To Nextで処理を繰り返しています。

そして、

If InStr(Cells(i, "a"), "八王子市") <> 0 Then　～　End If

If文ですね。InStr関数は、セル内に指定した文字列があるか、どうかを検索する関数です。

そして、含まれていなければ、「0（ゼロ）」を算出します。

逆に含まれているならば、最初に見つけた文字位置を算出します。

要するに、0かどうかで、判断することができるというわけです。

InStr(Cells(i, "a"), "八王子市") <> 0

とすれば、A2に八王子市という文字が含まれているか、どうかが判断できます。

「<>0」は「0ではない」という意味です。

「0ではない」ので、文字が含まれていたら、

Cells(i, "b") = "○"

B列に「○」を表示しましょう。

というわけです。

なお、InStr関数は、通常のExcelにはありません。

Excel。16進数を2進数に変換するのが、HEX2BIN関数です。【HEX2BIN】

Excel。16進数を2進数に変換するのが、HEX2BIN関数です。　

＜関数辞典：HEX2BIN関数＞

HEX2BIN関数

読み方： ヘックストゥビン　

読み方： ヘキサデジマルトゥバイナリ

分類： エンジニアリング 

HEX2BIN(数値,[桁数])

16進数を2進数に変換する　

Excel。2024/4/7-4/13にMINUTE関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/4/7-4/13にMINUTE関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

4月7日

Excel。

MINUTE関数

読み方： ミニッツ　　

分類： 日付時刻 

MINUTE(シリアル値)

時刻から""分""を算出する　

4月8日

Excel。

MINVERSE関数

読み方： エムインバース　　

読み方： マトリック インバース

分類： 数学/三角 

MINVERSE(配列)

配列の逆行列を算出します　

4月9日

Excel。

MIRR関数

読み方： エムアイアールアール　　

読み方： モディファイドアイアールアール

分類： 財務 

MIRR(範囲,安全利率,危険利率)

定期キャッシュフローの修正内部利益率を算出する　

4月10日

Excel。

MMULT関数

読み方： エムマルチ　　

読み方： マトリック マルチ プリケーション

分類： 数学/三角 

MMULT(配列1,配列2)

2つの配列の行列積を算出する　

4月11日

Excel。

MOD関数

読み方： モッド　　

分類： 数学/三角 

MOD(数値,除数)

除算した余りを算出します　

4月12日

Excel。

MODE関数

読み方： モード　　

分類： 互換性 

MODE(数値1,[数値2],…)

最頻値を算出します　

4月13日

Excel。

MODE.MULT関数

読み方： モード・マルチ　　

分類： 統計 

MODE.MULT(数値1,[数値2],…)

複数の最頻値を算出します　

Excel。関数をつかって手早く、月末一覧表をつくりたい。【end of month】

Excel。関数をつかって手早く、月末一覧表をつくりたい。

＜EOMONTH+DATE関数＞

オートフィルでその年の月末一覧を作成してもいいのですが、年が変わるたびに、イチイチ、オートフィルで月末一覧をつくるのも、面倒な作業だといえます。

そこで、関数をつかって、月末一覧をつくることにしました。

B4に2024年の1月末日を算出するようにしたいわけです。

月末日を算出する関数は、EOMONTH関数です。

それでは、B4をクリックして、次の数式をつくります。

=EOMONTH(DATE($A$1,A4,1),0)

ところが算出結果が、日付ではなく、数値。シリアル値で算出されます。

表示形式をつかって日付に変えてます。

色々な方法がありますが、ホームタブにある、数値の書式ボックス内の「短い日付形式」をクリックします。

B4のシリアル値が、日付に変わったら、あとはオートフィルで数式をコピーします。

このように、年末日の一覧をつくることができました。

A1の値を2024から2025に変えれば、2025年の月末日一覧に変わります。

さて、B4に設定した数式を確認しておきましょう。

=EOMONTH(DATE($A$1,A4,1),0)

最初のEOMONTH関数は、最初の引数の開始日の何ヵ月前後の月末日を算出できる関数です。

最初の引数の開始日には、DATE関数で日付をつくっていきます。

2つ目の引数には、0と設定します。

0とすることで、開始日の月末日という意味になります。

あとは、最初の引数のDATE関数を設定します。

オートフィルで数式をコピーしますので、A1を絶対参照にしましょう。

Excel。HARMEAN関数で、数値の調和平均を算出します【HARMEAN】

Excel。HARMEAN関数で、数値の調和平均を算出します

＜関数辞典：HARMEAN関数＞

HARMEAN関数

読み方： ハーミーン　　

分類： 統計 

HARMEAN(数値1,[数値2],…)

数値の調和平均を算出します

Harmonicの略

Excel。数値を全角に、しかも三桁区切りのカンマも全角にしたい。【em】

Excel。数値を全角に、しかも三桁区切りのカンマも全角にしたい。

＜表示形式＞

Excelは、全角で数値を入力してみても、半角に変わってしまいます。

どうしても全角の数値を使いたい場合には、表示形式をつかうことで、全角にすることができます。

ところが、そのあとホームタブにある「,」三桁区切りのボタンを押すと、半角に戻ってしまいます。

数値を全角に、しかも三桁区切りのカンマも全角にしたい場合は、表示形式の「全角桁区切り」をつかうことで、対応することができます。

Excel。上位1位～3位までのデータがわかりやすいように、行全体を塗りつぶすには【TOP3】

Excel。上位1位～3位までのデータがわかりやすいように、行全体を塗りつぶすには

＜条件付き書式＋LARGE関数＞

似ているデータが多い時に上位1位～3位までのデータを把握したいとします。

そこで、上位1位～3位のデータならば、行全体を塗りつぶすことで、把握することにしました。

では、どのようにしたらいいのでしょうか。

上位1位～3位ということで、条件によって塗り分けるので、条件付き書式をつかうわけです。

あとは、どのような条件にすればいいのかという点です。

上位1位～3位を算出するには、LARGE関数をつかうことで判断することができます。

では、早速、条件付き書式を設定していきます。

A2:C11を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

条件式を設定しますので、ルールの種類を「数式を使用して、書式設定するセルを決定」を選択します。

条件式のボックスに数式を設定します。

=$C2>=LARGE($C$2:$C$11,3)

あとは、書式ボタンをクリックして、塗りつぶしたい色を選択します。

OKボタンをクリックすれば、条件付き書式の設定は終了します。

これにより、上位1位～3位のデータの行全体を塗りつぶすことができます。

では、設定した条件式を確認します。

=$C2>=LARGE($C$2:$C$11,3)

C2は、列固定の複合参照にすることで、行全体が対象になります。

これにより、行全体を塗りつぶすことができます。

LARGE($C$2:$C$11,3)

LARGE関数をつかうことで、2番目の引数で「3」と設定すれば、最初に設定した引数の範囲ないで、上位3番目の値を算出することができます。

上位3番目の数値が算出できたら、その値以上ならば、1位～3位ということがわかります。

そのために、LARGE関数をつかった条件式を設定したというわけです。

あと、$C$2:$C$11　というように、忘れずに、絶対参照を設定しましょう。

Excel。GROWTH関数で指数回帰分析による値を求めることができます。【GROWTH】

Excel。GROWTH関数で指数回帰分析による値を求めることができます。

＜関数辞典：GROWTH関数＞

GROWTH関数

読み方： グロウス　　

分類： 統計 

GROWTH(既知のy,[既知のx],[新しいx],[定数])

指数回帰分析による値を算出します

Excel。2024/3/31-4/6にMINIFS関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/3/31-4/6にMINIFS関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

3月31日

Excel。

MDURATION関数

読み方： エムデュレーション　　

読み方： モディファイドデュレーション

分類： 財務 

MDURATION(受渡日,満期日,利率,利回り,頻度,[基準])

証券に対する修正マコーレー係数を算出します　Modified DURATIONの略

4月1日

Excel。

MEDIAN関数

読み方： メディアン　　

分類： 統計 

MEDIAN(数値1,[数値2],…)

中央値を算出します　

4月2日

Excel。

MID関数

読み方： ミッド　　

分類： 文字列操作 

MID(文字列,開始位置,文字数)

文字列の任意の位置から文字を取り出す　

4月3日

Excel。

MIDB関数

読み方： ミッドビー　　

分類： 文字列操作 

MIDB(文字列,開始位置,バイト数)

文字列の任意の位置から指定バイト数の文字を返す　

4月4日

Excel。

MIN関数

読み方： ミニ　　

読み方： ミニマム

分類： 統計 

MIN(数値1,[数値2],…)

最小値を算出します　

4月5日

Excel。

MINA関数

読み方： ミニマムエー　　

分類： 統計 

MINA(値1,[値2],…)

数値・文字列・論理値を含む最小値を算出します　

4月6日

Excel。

MINIFS関数

読み方： ミニマムイフズ　　

読み方： ミニマムイフエス

分類： 統計 

MINIFS(最小範囲,条件範囲1,条件1,…)

条件により指定した範囲内の最小値を算出する

Excel。1週間分をまとめて月曜日に出荷するので、翌週の月曜日を手早く、算出したい。【next monday】

Excel。1週間分をまとめて月曜日に出荷するので、翌週の月曜日を手早く、算出したい。

＜WEEKDAY関数＞

手続きした日から、翌週の月曜日にまとめて出荷するとした場合、翌週の月曜日をどのようにしたら、手早く算出することができるのでしょうか。

出荷日は、1週間分をまとめて月曜日に出荷するので、翌週の月曜日を知りたいわけです。

A列には、手続き日を入力しています。

2024/5/6は月曜日です。

月曜日ならば、翌週の月曜日である、2024/5/13と表示したい。

そして、火曜から日曜までが、翌週の月曜日である、2024/5/13と表示したいわけです。

まず考え方ですが、月曜日ならば、翌週月曜日の日付を算出したいわけです。

単純に「+7」してみます。

ただ、火曜日～日曜日までは、「+7」したら月曜日になるように、減算させる必要があります。

そこで、曜日を数値として算出してくれるWEEKDAY関数をつかうことで、曜日ごとに減算する量を調整することができます。

このような考え方で、C2には、次の数式をつくってみました。

=A2+7-(WEEKDAY(A2,11)-1)

A2は2024/5/6

2024/5/6に＋７するので、2024/5/13

WEEKDAY(A2,11)　ですが、2番目の引数の種類は、11番にしました。

別に、2番でもいいのですが、翌週火曜日などに修正する時に1の位だけを変えればいいので、11番を採用しました。

11番は、月曜日を1として日曜日を7と算出してくれます。

ですから、

(WEEKDAY(A2,11)-1)　は、1-1　で「0（ゼロ）」という結果になります。

2024/5/13-0ということですから、2024/5/13と翌週の月曜日を算出することができたというわけです。

A3の2024/5/7の場合でも確認しておきましょう。

2024/5/7＋７なので、2024/5/14です。

(WEEKDAY(A3,11)-1)　は、2-1　ですから「1」

2024/5/14-1　で、2024/5/13　と翌週の月曜日を算出できます。

Excel。平均時速を算出するには、AVERAGE関数ではなくHARMEAN関数です。【HARMEAN】

Excel。平均時速を算出するには、AVERAGE関数ではなくHARMEAN関数です。

＜HARMEAN関数＞

行きは徒歩で、時速3km/hで、帰りは車に乗せてもらったので、時速50km/hで移動した時の往復の平均時速は、どのようにしたら、算出することができるのでしょうか。

というのも、普通のAVERAGE関数で算出すると、おかしな数値が算出されてしまいます。

C5には、

=AVERAGE(B2:B3)

で、31.5と算出されました。

合っているのではと思えますが、移動距離を3kmだった場合で考えてみましょう。

距離を時速で除算すれば、F列の時間を算出することができます。

徒歩の場合は、1時間。車の場合は、0.05時間です。

往復の所要時間は、1.05時間です。

ということは、往復の距離E5をF4の時間で除算すれば、時速が算出されるはずです。

F5には、

=E5/F4

算出結果は、5.714。

C5のAVERAGE関数で算出した値と合致しません。

そこで、HARMEAN関数をつかうというわけです。

C6に

=HARMEAN(B2:B3)

と数式を設定しました。

算出結果は、5.714。

時速の平均を算出する場合には、調和平均のHARMEAN関数をつかうというわけです。

Excel。GROUPBY関数は行の項目ごとに集計をします。【GROUPBY】

Excel。GROUPBY関数は行の項目ごとに集計をします。

＜関数辞典：GROUPBY関数＞

GROUPBY関数

読み方： グループバイ　　

分類： 検索/行列 

GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])

行の項目ごとに集計をします。

Excel。複数回答のアンケート結果を手早く集計するには、どうしたらいいの。【questionnaire】

Excel。複数回答のアンケート結果を手早く集計するには、どうしたらいいの。

＜COUNTIF関数＞

次の表は、複数回答ありのアンケート結果です。

E2:F7までの表がアンケート結果です。

5名が回答した結果がF列に入力されています。

それぞれのA～Dまでの集計結果がC列に算出されています。

複数回答なので、Aが何件あるのか、Bが何件あるのかを集計したいわけです。

目視で確認したくありません。

そこで、効率的な集計方法はないものでしょうか。

F列が、一文字ならば、Aだったら、何件というようにすれば、数えることができます。

ただし、複数回答がOKですから、一文字の場合もあれば、最大4文字が入力されているので、単純にCOUNTIF関数やCOUNTIFS関数では、対応するのが難しいように思えます。

このような場合、実は、COUNTIF関数で対応することができます。

ただ、条件に工夫が必要になります。

工夫というのは、「Aという文字が含まれているか」とすればいいわけです。

「含む」ということで、ワイルドカードの「*」を合わせてつかうことで、対応することができます。

C3に数式を設定します。

=COUNTIF($F$3:$F$7,"*"&$A3&"*")

あとは、オートフィルで数式をコピーします。

数式を確認します。

最初の引数は、「範囲」です。回答のF3:F7を範囲選択します。

また、オートフィルで数式をコピーするので、絶対参照も忘れずに設定します。

2つ目の引数は、「検索条件」です。

ここで、「”A”」と設定してしまうと、複数回答のセルは、条件が合致しません。

そこで、「*」（ワイルドカード）で、条件を囲ってあげます。

「"*"&$A3&"*"」

これで、含むという条件にすることができます。

A3はAなので、「Aという文字を含むなら」という条件をつくることができるというわけです。

Excel。2024/3/24-3/30にMATCH関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/3/24-3/30にMATCH関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

3月24日

Excel。

MAKEARRAY関数

読み方： メイクアレイ　　

分類： 論理 

MAKEARRAY(rows,column,function)

LAMBDA関数を適用して、指定した行と列のサイズの計算配列を返します

3月25日

Excel。

MAP関数

読み方： マップ　　

分類： 論理 

MAP(array,lambda_or_array2,…)

LAMBDAを適用して新しい値を作成することにより、配列内の各値をmappingで形成された配列を新しい値に返す

3月26日

Excel。

MATCH関数

読み方： マッチ　　

分類： 検索/行列 

MATCH(検査値,検査範囲,[照合の種類])

値を検索してその相対位置を算出します　

3月27日

Excel。

MAX関数

読み方： マックス　　

分類： 統計 

MAX(数値1,[数値2],…)

最大値を算出します　

3月28日

Excel。

MAXA関数

読み方： マックスエー　　

分類： 統計 

MAXA(値1,[値2],…)

数値・文字列・論理値を含む最大値を算出します　

3月29日

Excel。

MAXIFS関数

読み方： マックスイフズ　　

読み方： マックスイフエス

分類： 統計 

MAXIFS(最大範囲,条件範囲1,条件1,…)

条件により指定した範囲内の最大値を算出します　

3月30日

Excel。

MDETERM関数

読み方： エムデターム　　

読み方： マトリック ディターミナント

分類： 数学/三角 

MDETERM(配列)

配列の行列式を算出します　

Excel。0（ゼロ）を除いた最低点を知りたいときは、MINIFS関数の出番です。【Youtube】

Excel。0（ゼロ）を除いた最低点を知りたいときは、MINIFS関数の出番です。

＜MINIFS関数＞

最低値を算出することができる、MIN関数では、0点以上の場合の最低値を算出したいなど、「条件」をつけた最低値を算出することができません。

そこで、「複数条件」をつけた、最低値を算出することができる「MINIFS関数」をつかってみると、算出することができます。

Excel。ピボットテーブル内の値を抽出できるのがGETPIVOTDATA関数です。【GETPIVOTDATA】

Excel。ピボットテーブル内の値を抽出できるのがGETPIVOTDATA関数です。

＜関数辞典：GETPIVOTDATA関数＞

GETPIVOTDATA関数

読み方： ゲットピボットデータ　　

分類： 検索/行列 

GETPIVOTDATA(データフィールド,ピボットテーブル,[フィールド1,アイテム1],…)

ピボットテーブル内の値を抽出する

Excel。2024/3/17-3/23にLOOKUP関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/3/17-3/23にLOOKUP関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

3月17日

Excel。

LOGINV関数

読み方： ログインバース　　

分類： 互換性 

LOGINV(確率,平均,標準偏差)

累積確率から対数正規分布を算出します　

3月18日

Excel。

LOGNORMDIST関数

読み方： ログノーマルディスト　　

読み方： ログノーマルディストリビューション

分類： 互換性 

LOGNORMDIST(x,平均,標準偏差)

対数正規分布の累積確率を算出します　

3月19日

Excel。

LOGNORM.DIST関数

読み方： ログノーマル・ディスト　　

読み方： ログノーマル・ディストリビューション

分類： 統計 

LOGNORM.DIST(x,平均,標準偏差,関数形式)

対数正規分布の累積確率か確率密度を算出します　

3月20日

Excel。

LOGNORM.INV関数

読み方： ログノーマル・インバース　　

分類： 統計 

LOGNORM.INV(確率,平均,標準偏差)

累積確率から対数正規分布を算出します　

3月21日

Excel。

LOOKUP関数

読み方： ルックアップ　　

分類： 検索/行列 

LOOKUP(検索値,検索範囲,対応範囲)

1行/1列のセル範囲でせるを検索し対応するセルの値を返す　　ベクトル形式　

3月22日

Excel。

LOOKUP関数

読み方： ルックアップ　　

分類： 検索/行列 

LOOKUP(検索値,配列)

縦横を指定しないでセルを検索し対応するセルの値を返す　

3月23日

Excel。

LOWER関数

読み方： ロウアー　　

分類： 文字列操作 

LOWER(文字列)

英字を小文字に変換する

Excel。PERCENTOF関数は、合計値なしで構成比を算出できます。【Composition ratio】

Excel。PERCENTOF関数は、合計値なしで構成比を算出できます。

＜PERCENTOF関数＞

2023年11月のExcel365Insider版に追加された、PERCENTOF関数は、構成比を算出するときに、便利な関数です。

次の表を用意しました。

C列の売上金額の構成比を算出します。

構成比を算出するには、対象の数値の合計値が必要になりますが、PERCENTOF関数では、不要です。

D2に設定した数式は、

=PERCENTOF(C2,$C$2:$C$6)

あとは、オートフィルで数式をコピーするだけです。

PERCENTOF関数の引数は、次のようになっています。

PERCENTOF(data_subset,data_all)

最初の引数は、data_subsetです。

構成比を求めたい数値ですね。

C2と設定します。

次の引数は、data_allです。

全体の数値です。

$C$2:$C$6　と絶対参照も忘れずに設定します。

これで完成です。

構成比を出すために、合計値を算出する必要がないのが、いい点です

ただ、このPERCENTOF関数は、Insider版にしかまだありません。

Excel。値以上かどうかを判定して集計するならGESTEP関数が便利です。【totalling】

Excel。値以上かどうかを判定して集計するならGESTEP関数が便利です。

＜GESTEP関数＞

得点が平均以上なのかを判定した上で、平均以上の件数が何件あるのかを確認したい場合には、どのようにしたら、効率的に確認・算出することができるのでしょうか。

例えば、C2には、

=B2>=$B$10

という数式を設定しました。B10には得点の平均値が算出されています。

この値以上ならば、TRUE。

そうでなければ、FALSEと算出されます。

TRUEとFALSEで、以上かそうでないかを判断することはできますが、件数を算出するのに、SUM関数はつかえません。

また、件数自体は、COUNTIF関数をつかえば、算出することはできますが、一件一件のデータが以上なのかは、わかりません。

そうなると、IF関数をつかって、以上なら「○」とか算出して、その「○」をCOUNTIF関数で算出するとなると、少し面倒です。

そこで、GESTEP関数をつかってみましょう。

C2をクリックします。

=GESTEP(B2,$B$10)

最初の引数は、「数値」なので、B2を設定します。

2番目の引数は、「しきい値」です。

平均値をしきい値としたので、B10。

オートフィルで数式をコピーするので、絶対参照も忘れずに設定します。

あとは、オートフィルで数式をコピーします。

GESTEP関数は、TRUEやFALSEで算出するのではなく、

TRUE=1

FALSE=0

と算出してくれます。

そのため、SUM関数で、何件あるかを手早く、算出することができます。

なお、以上の時の判定でしかつかえません。