エクセル

「1つの行のなかの全てのセルが同じ値」である行を見つける。

この記事は自分用の覚書です。

■MacBook Pro macOS Monterey
バージョン 12.6.5

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.72 (23040900)

T.N さん。

「1つの行のなかの全てのセルが同じ値」である行を見つけるやり方です。

■課題

アンケート結果(数字)の一覧がある。
各行を調べて、その行の全てのセルが同じ値である行を見つけたい。
結果としては、それらの行以外の行を一覧にしたい。
(全てのセルの値が異なっている行の一覧をつくる。)

ただし、「行の全てのセルが同じ値である行」は削除しないで残しておきたい。

■「行の全てのセルが同じ値である行」を見つける。

次図をやりたいということですよね。

01gyouno_naka_onajiatai

上側の図の状態を作れれば、オートフィルタを使ってF列を「空白セル」で絞り込むことで目的の一覧になりますが、以下ではFILTER関数を使ってみました。
どこかのセルにFILTER関数の数式を入れておけば、オートフィルタを使うこともないので手順を少しでも省略できますから。

あとあとデータが増えていくことを考えて、元データ全体を「テーブル」にしておくことをお勧めします。
テーブルにしてあれば、行や列の追加があっても参照している数式を書き換える必要がありません。

いくつかやり方を思いつきましたが、ここではCOLUMN関数とCOUNTIF関数をIFS関数の引数に使うやり方を採りました。

数式の可読性を上げるために、セル内改行してあります。
(引数の頭はスペースで揃えています。)

02gyouno_naka_kotonaru_itiran_table

テーブルの中を参照するやり方は「構造化参照」と呼ぶそうです。
見慣れない数式なので、ややこしく感じるかもしませんが、落ち着いて見ればわかると思います。

やっていることは従来のセル参照と同じですから。

03gyouno_naka_kotonaru_itiran_cellsansyo

COLUMN関数で「設問5」の列(E列)の列番号を取得しています。
E列のセルを参照しているので、どれも答えは「5」になります。

COUNTIF関数で、「設問1」から「設問5」(A列からE列)のセル範囲にある「設問1」の列(A列)の値の個数を取得しています。
全て同じ値なら答えは「5」個ですね。

IFS関数の引数「論理式1」は「COLUMN関数とCOUNTIF関数の答えが等しい」としています。
そして、それが成り立つ場合は、IFS関数は「同」という文字列を返します。

次に引数「論理式2」では、「COLUMN関数とCOUNTIF関数の答えが等しくない」としています。
こちらが成り立つ場合は、IFS関数は空白を返します。

なお、IFS関数は論理式とそれが成り立ったときに返す値の組み合わせを最大127個まで指定でき、最後として TRUE を論理式として指定すると、その前までのどの論理式も成り立たない場合に返す結果を指定できます。
これは、論理式とそれが成り立ったときに返す値の組み合わせが1つの場合でも同じです。なので、次のように数式を書いても上記と同じ結果になります。

02gyouno_naka_kotonaru_itiran_true

ただ、「TRUE を指定するとはどういうことなのか」を知らない場合には、何をしているかの理解のしやすさが少しとはいえ落ちると感じたので、今回は TRUE を引数に指定するやり方は避けました。

■FILTER関数で絞り込む。

ここでは別シートにFILTER関数の数式を用意しました。
テーブルの「行の中で全ての値が同じかの判定」の列(F列)が空白の行だけになるように、FILTER関数で絞り込んでいます。

04gyouno_naka_kotonaru_itiran

テーブルの見出しとデーターを参照している数式(構造化参照)は次のようです。

=a_テーブル1[[#見出し],[設問1]:[設問5]]

=FILTER(a_テーブル1[[設問1]:[設問5]],a_テーブル1[行の中で全ての値が同じかの判定]="")

05gyouno_naka_kotonaru_itiran

テーブル名は「a_テーブル1」にしました。
テーブル名の頭にアルファベットを付けておくことで、数式のなかでそのアルファベットを打つだけでテーブル名を呼び出せます。

なお、 _ (アンダーバー)は、Windows版Excel対応として付けています。

テーブル名の頭にアルファベットを付けただけだと、Windows版Excelでは数式オートコンプリートの表示で、同じアルファベットで始まる関数名のなかにそのテーブル名が混ざって表示されてしまい、それらの中からさらにテーブル名を探さないといけなくなります。
(Excel for Mac では、アルファベットを付けておくだけでテーブル名と関数名は分けて表示され、テーブル名のほうが上に表示されます。)

テーブル名にアンダーバーも添えておくことで、Windows版Excelでは数式の入力の際に頭のアルファベットを打つと、数式オートコンプリートの表示のなかで関数名より上にテーブル名を表示できます。

■他のやり方について

今回扱うのは「数字」なので、標準偏差を使うやり方も思い付きました。
標準偏差が 0 なら全ての値は等しいということになります。

06gyouno_naka_kotonaru_itiran

このやり方を採用しなかったのには、次の2つの理由があります。

  1. 「標準偏差が 0 なら全ての値は等しい」と知らなければ、何をやっているかを理解しづらい。
  2. 文字列が対象になった場合には使えない。

職場で作るファイルは誰のものでもなく皆のものです。誰かに引き継ぐかもしれませんし、他の人が流用するかもしれません。それを想定して「いつ誰が見ても理解しやすく作っておく」ことが大事です。

その理由で、うちの職場ならたぶん困る人は誰もいないだろうとは思いましたが、「"標準偏差が 0 なら全ての値は等しい" ことを知っていないと何をやっているのかわからない」状態にはしておかないほうがよいと判断しました。

また、COLUMN関数とCOUNTIF関数を使ったやり方なら、対象が「数字」でも「文字列」でも対応できます。汎用性がわずかでも高いので、そちらを採りました。

あるいは、AND関数とEXACT関数を使うやり方も思い付きました。
ただし、私ならそのやり方は採用しません。エクセルのヘルプに載っているように、EXACT関数は文字列を比較するための関数だからです。

07gyouno_naka_kotonaru_itiran

ヘルプには「文字列を比較する」と書いてあるだけなので、引数に数字を指定するのは間違った使い方です。
それでも TRUE と FALSE を正しく判定してくれますが、それはいわばエクセルが気をつかって例外的に扱ってくれているからといえます。

でも間違った使い方なんです。
今後、マイクロソフトの方針というか事情というかで、いつ正しい答えが得られなくなってもおかしくないんです。そうなったら、それまで作ってきたファイルが全て無駄になります。

正直いって、そんなことが本当に起こるとは私も思っていません。
ですが、起こってもおかしくはないんです。

EXACT関数を使うやり方しか思いつけなかったならしかたがないでしょうが、他のやり方も思いついたのにわざわざEXACT関数を使うべき理由がありません。
「数字を正しく判定できなくなることが、いつ起こってもおかしくない」という潜在的な危険性を無くしておいたほうが、ビジネスではよいと私は判断します。その理由で、この件でEXACT関数を使うことは私はお勧めしません。

| | コメント (0)

Excel のTEXTSPLIT関数で文章を別々のセルに入れる。

この記事は自分用の覚書です。

■MacBook Pro macOS Monterey
バージョン 12.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.69.1 (22011600)

M.K さん

昨年の8月でしたか新関数が追加されました。そのなかの一つ「TEXTSPLIT関数」を使っていますか。我々の仕事ではとても役に立ちますよ。
今のところ、Microsoft365サブスクライバーなら使える関数です。

「区切り位置」の機能を関数で実現したのがTEXTSPLIT関数です。
指定した目印でテキストを区切ってくれます。

この記事この記事のように「区切り位置」機能を使わなくてもよくなりました。
使ってはいけないということではないですが、我々の仕事で今後は「区切り位置」の機能の利用は減るでしょうね。

ネット上ではたくさんの人がTEXTSPLIT関数の使い方を解説してくれていますから、詳しいことはご自分で調べてください。

ここでは、我々の仕事上でたまに遭遇する、だからこそ都度やるのは面倒な「セル内にある文章を区切って別々のセルに入れる」例を取り上げます。

■「区切り位置」にはない利点

「区切り位置」にはないTEXTSPLIT関数の利点は次の3点だと私は思います。

  1. 行方向に分割できる。
    「区切り位置」では列方向にしか区切れません。
    TEXTSPLIT関数では行方向にも区切れます。
  2. 元の文字列が無くならない。
    「区切り位置」を実行すると元の文字列が消えてしまいます。
    1つのセルに入っている状態を残しておきたければ、どこかにコピー貼り付けしておくしかありません。
    TEXTSPLIT関数では元の文字列は無くなりません。
  3. 元の文字列が変わったら連動する。
    なんらかの理由で元の文字列が変わった場合、「区切り位置」ではやり直しが必要です。
    TEXTSPLIT関数ではそれが必要ありません。数式のなかで元の文字列のセルを参照しているだけなので。

■TEXTSPLIT関数の構文

TEXTSPLIT関数の構文は次のようです。
引数がまだ英語圏用の表示のままなのがちょっと残念ですが、そのうち日本語表示になるのではないかと思っています。

00textsplit

我々の仕事でなら、左から4番目の引数まで知っておけば大抵の用途は満たせると思います。

  • text
    「元の文字列」を指定します。
    当たり前ですが省略できません。
  • col_delimiter
    「列に区切るときの目印」を指定します。省略可能です。
  • row_delimiter
    「行に区切るときの目印」を指定します。省略可能です。
  • ignore_empty
    「空欄を無視するか、しないか」を指定します。省略可能です。
    省略すると「FALSE」(空欄を無視しない)を指定したことになります。

引数「ignore_empty」について少しふれておきます。

たとえば、

A,B,,C

となっている文字列があって , を目印に区切ると、B の右側に , が2つあるので次のようになります。

05textsplit_kuran

「正味の文字はそこにないのだからカンマで区切ったら、A B C でしょう」と考えることができるのは人間だからです。

エクセルは、人間が指定した目印で元のデータを区切るので、目印があるということは隣に値があってもなかってもそこで区切って値を入れるためのセルを確保します。

だから、A,B,,C は A B □ C になります。

こうなった場合に「空のセル」を無視しないで残すか、無視して詰めるかを指定するのが引数「ignore_empty」です。

目印で区切ったときにできる空欄を無視しないのは、「区切り位置」機能と同じです。
「区切り位置」機能がそうなっているため、TEXTSPLIT関数の標準をそちらに合わせたのでしょうね。

■セルに入っている文章を区切る。

考え方は「区切り位置」機能と同じです。
上記のリンク先にもあるように、どこで区切るかの位置として指定した "もの" は消えてしまいます。
だとしたら、「。」ごとに文章を区切る場合は「。」を「。と区切りのための目印」に置換してから、その目印をTEXTSPLIT関数の引数に指定すれば「。」を残して区切れます。

次図では、「。」を「。_」(。とアンダーバー)に置換してから「_」をTEXTSPLIT関数の引数に指定しています。

引数のなかでの「_」の位置に注目してください。

引数「col_delimiter」に「_」を指定した場合は「3列」に区切られています。
「row_delimiter」に指定した場合は「3行」になっています。上記の利点の 1 番、「区切り位置」機能ではできない区切り方です。

引数「ignore_empty」は TRUE (空欄は無視する)です。

以下では和文を例にしていますが英文でもやり方は変わりません。
英文ならば、「.」(ピリオド)を「.と目印」に置換することになります。

「スピル」については大丈夫ですか。TEXTSPLIT関数はスピルします。
これからのエクセルは「スピル」についての理解が必須です。

図では「作業列」を使って処理の過程を分けています。こうすることで、全体を通して何をどうやっているのかが誰にでも理解しやすくなります。

職場で作るファイルは誰のものでもありません。皆のものです。
他の人が引き継ぐこともありますし、流用することもあります。

そんなときに、ぱっと見て何をしているのかわかりづらいファイルは迷惑でしかありません。

仕事で作るファイルは、いつ誰が見ても理解しやすく作っておくべきです。
他社さんではどういう方針でやっているかは知りませんが、我々の職場ではそういう考え方をしてください。

誤解がないようにいっておきますが、「関数をネストとして長い数式を1つのセルに入れてはいけない」わけではないです。
そうするしかない場合だってありますから。

ただ、どんな場合も「いつ誰が見ても理解しやすく作っておく」ことが大事だということです。

01textsplit

ひょっとして、上図の数式を見て「1つの文章の終わりに「。」が複数あることはないから、空欄はできないので "TRUE" (空欄は無視する)は不要だろう」と感じていますか。

だとしたら、ちょっとした勘違いです。さっきの A,B,,C の例と同じです。
「アンダーバーで区切ったら最後の文章の次には何もないのだから空欄はできない」と考えることができるのは人間だからです。

引数「ignore_empty」を省略する( FALSE を指定したことになる)と次のようになります。

06textsplit_kuran2

最後の「……である。_」にもアンダーバーが付いていますからエクセルはそこで区切って、隣にあるであろう値を入れるためのセルを確保します。

だから、

 ……です。………ます。………である。[空のセル]

になります。

その「空のセル」はこの事例の場合は不要なので、引数「ignore_empty」を "TRUE" (空欄は無視する)にしています。

なお、上図では「_」を使いましたが、区切るための目印にする "もの" は「記号」でなくてもかまいません。
数字も英字でも漢字でもひらがなでもカタカナでもなんでもよいです。

元データのなかにあるものは目印にはできません。そこで区切られてしまうので。
「"。" で区切るために "。" を目印付きの "。" に置き換えるときは、元のデータのなかに絶対に存在しないものを目印にする」という注意点も「区切り位置」機能と同じです。

02textsplt

もっと言うと、「目印」は「1文字」である必要はないです。わざわざこんなことする人はいないでしょうけどね。

03textsplit

■セル内改行ごとに区切る。

セル内改行で区切る場合も「区切り位置」と考え方は同じです。

CHAR関数(キャラクター関数)が「改行コード」を返すように指定してそれを引数に使います。
改行コードを返す構文は、CHAR(10)です。

04textsplit

これらをテンプレートにしておけば、テキストを貼り付けるだけで済みます。

TEXTSPLIT関数の登場のおかげで「セルの中の文章を区切って別々のセルに入れる」作業はずいぶん楽になりました。
積極的に使って仕事の効率を上げていきたいですね。

| | コメント (0)

Excel for Mac で「列のピボット解除」が可能に。

この記事は自分用の覚書です。

■MacBook Pro macOS Monterey
バージョン 12.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.69.1 (22011600)

H.K さん。

Excel for Mac の Power Query が機能アップされましたね。

Windows版Excel にしかなかった「列のピボット解除」が、Excel for Mac でもできるようになりました。
細かい話をすると、Windows版Excelにはある「テーブルまたは範囲から」コマンドがないです。これからに期待しましょう。

Windows版Excel の「列のピボット解除」と操作はほとんど変わりません。

ただし、上記でふれたように「テーブルまたは範囲から」がないので、ピボット解除したいファイル自体を開いての操作はできません。
いったん、別のファイルを用意してそこに「ピボット解除したいファイル」を読み込むことになります。

こういう形の表があるとします。

01retu_pivot_kaijo

これではほぼ何もできません。

・最小値・最大値を見つける。
・最大値(最小値)の拠点を見つける。
・何日がどんな値なのかを調べる。
などなど。

つまり、こういう形の表だとエクセルがもつポテンシャルを十分に生かせないわけですね。
これをポテンシャルを生かせる形にするのが「列のピボット解除」です。

先の形の表をこのような形にしてくれます。
こうなっていれば、エクセルの関数やさまざまな機能を使って、なんとでもできます。

14retu_pivot_kaijo

「列のピボット解除」の操作 1

冒頭の形の表を開いていてはピボット解除ができないので閉じておきます。
別のファイルで「データ」タブの「データファイル指定(Power Query)」を選びます。

02retu_pivot_kaijo

「列のピボット解除」の操作 2

「データソースの選択」の画面になります。
ここではエクセルのブックを例として使うので、「Excelブック」を選択しています。

03retu_pivot_kaijo

「データソースへの接続」の画面になるので「参照」から該当するブックを選びます。

ブックを選択したら「データ取り出し」をクリックします。

04retu_pivot_kaijo

「データソースへの接続」の画面に戻るので、「次へ」をクリックします。

「列のピボット解除」の操作 3

「データの選択」画面になるので、目的のシートにチェックを入れます。(例では Sheet1 だけしかありません。)
ここで慌てないように!
「読み込む」をクリックしてはいけません。「データの変換」のほうをクリックします。
「読み込む」をクリックすると、ワークシート上にこのまま読み込まれてしまいます。

07retu_pivot_kaijo

「列のピボット解除」の操作 4

「データの変換」をクリックすると、Powewr Query エディターにデータが読み込まれます。
ここで、すでに1列目が選択されていることを意識してください。

08retu_pivot_kaijo

「変換」タブを開いて「1行目をヘッダーとして使用」をクリックします。

09retu_pivot_kaijo

1行目がヘッダーになります。
さっきまでは、Column1 や Column2 になっていた部分です。

10retu_pivot_kaijo

「変換」タブの「列のピボットの解除」から「その他の列のピボット解除」を選びます。

11retu_pivot_kaijo

さきほど、「1列目が選択されていることを意識しておく」といいました。
"1列目が選択されている" ので、 「その他の列のピボット解除」の "その他" というのは、2列目以降のことになります。

1列目はすでにヘッダー(列見出し)があって、2行目から下に値が入っている状態になっています。

そうなっていないのは、2列目と3列目です。
2列目と3列目のピボット状態を解除したいわけです。

なので、ここでは「その他の列のピボットの解除」を選びます。

2列目と3列目のピボットが解除されると、こうなります。

12retu_pivot_kaijo

「列のピボット解除」の操作 5

これで完成ではありません。これをワークシートに読み込む必要があります。
「ホーム」タブの「閉じて読み込む」をクリックします。

13retu_pivot_kaijo

ワークシートにデータが読み込まれます。
これで「列のピボット解除」は完了です。
14retu_pivot_kaijo

どんな場合も「列のピボット解除」が役立つわけではありませんが、かなり助かるのは確かです。

Power Query でデータをワークシートに読み込むと、元のブックとの関係が維持されます。
元のブックで値を追加すると、こちらのファイルを開いてその値を表示させることができます。

「茨城県」を追加してみました。

15retu_pivot_kaijo

Power Query を実行した側のファイルを開くと、「セキュリティの警告」が表示されます。「コンテンツの有効化」をクリックしてください。

「データ」タブの「すべて更新」を実行すると「茨城県」が追加されます。

16retu_pivot_kaijo

なお、「セキュリティの警告」の表示を消す手順がネットで見つかりますが、それらはたいていの場合、「元データとの接続を解除する」やり方が書いてあります。

当たり前ですが、さきほどのように「元データに追加があったら Power Query を実行した側のファイルにも自動的に追加される」状態を維持しておくのであれば、解除はしないでください。

| | コメント (0)

Excel で年齢を計算する。(DATEDIF関数は使わない。)

この記事は自分用の覚書です。

■MacBook Pro macOS Monterey
バージョン 12.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.67 (22111300)

H.K さん。
Excel で年齢を計算するやり方をまとめました。
「ある "年(西暦)" でいくつになるか」はYEAR関数を使って求めることができます。

なお、「年齢を求める計算をいつするのか」を固定しておかないとややこしくなりますから、ここでは「今年(2022年)」ということにしました。

年齢はYEAR関数で求めることができる。

・今年でいくつになるか。
・5年前にいくつだったか。
・7年後にいくつになるか。
など。

これらは全てYEAR関数を使って求めることができます。
「年齢を求めたい年の西暦(今年だったら 2022)」から「生年月日の西暦」を引けば「満年齢」が得られます。それに 1 を足せば、「数え年」が求められます。
あるいは、下図のB列に入れる日付が同じ年の誕生日より前なら、満年齢から 1 を引くことで「(誕生日がまだ来ていない)いま、いくつなのか」を知ることができます。

社内ではたいていの場合は、これで用が足りると思います。

図では、全体で何をしているかが理解しやすいように作業列を使って計算の過程を分けています。

01excel_nenrei

ちなみに、例で使っている生年月日2000年2月2日は、今年話題になった村上宗隆選手(ヤクルトスワローズ)の生年月日です。
2000年生まれを例として使ってあれば、暗算でも考えやすいですし。

満年齢と数え年

「満年齢」と「数え年」の違いは大丈夫ですか。
当たり前の話ですが、大事なところなので念のためふれておきますね。

・満年齢
生まれた日から1年間を0歳として、誕生日が来るたびに 1 ずつ加えていく。
2000年2月2日生まれなら、2022年2月2日で 22 歳になる。

・数え年
生まれた日を1歳として、元旦が来るたびに 1 つずつ加えていく。
2000年2月2日生まれは、2022年2月2日では 23 歳。
(2022年1月1日に 23 歳になっている。)

・「満年齢」を求める数式
今年の西暦-生年月日の西暦

・「数え年」を求める数式
今年の西暦-生年月日の西暦+1

02excle_nenrei

DATEDIF関数は避けたほうがよい

他社さんでは、どういう扱いにしているかは知りません。
少なくとも我々の仕事では「絶対に使ってはいけない」とまでは言いませんが、「使うべき理由がない関数」です。

excel 年齢 とかで検索すると、DATEDIF関数を使ったやり方がたくさん見つかりますが、Excelで数式を入力しても、DATEDIF関数の関数ヒントはちゃんと表示されません。

DATEDIF関数は、大昔に『Lotus 1-2-3』との互換のために用意された関数です。
Lotus 1-2-3 は2008年に販売が終了しているので、関数ヒントが表示されないのは、販売終了から14年も経った今ではほとんど使う機会がないからでしょう。

そんな関数を、ビジネスで常用するのは避けたほうがよいと私は思います。
今後何が起こるかわかりません。ひょっとしたら、マイクロソフトが廃止するかもしれませんし。
廃止されたら、それまでDATEDIF関数を使ってきたファイルが全て無駄になります。

ビジネスにおいて、何かを「する」あるいは「しない」の決定には、「なぜそうしたのか」という具体的な理由・根拠が必要です。
DATEDIF関数の使用には、それがありません。

これが、DATEDIF関数は我々の仕事では避けたほうがよいと、私が考える理由です。

西暦があれば年齢は計算できる

上図では、誕生日より前なのか過ぎているのかをわかりやすくするために、B列に「日付」を入れています。
ようするに、「西暦」があれば計算はできるので、B列のセルに「生年月日以降の西暦」が4桁で入っていれば年齢を求めることができます。

そのときは、「満年齢」と「数え年」の数式を書き換えてください。

03excel_nenrei_20221206110201

| | コメント (0)

Excel用2023年(令和5年)の祝日

この記事は自分用の覚書です。

■MacBook Pro macOS Monterey
バージョン 12.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.65 (22091101)

下記の表をコピーして、ワークシートのセル A1 を選択して貼り付ける。

全体をテーブルに変換してから、日付が入っているセル範囲( A2 から A18 )に「名前」を付ける。

日付 祝日名
2023/1/1 元日
2023/1/2 振替休日
2023/1/9 成人の日
2023/2/11 建国記念の日
2023/2/23 天皇誕生日
2023/3/21 春分の日
2023/4/29 昭和の日
2023/5/3 憲法記念日
2023/5/4 みどりの日
2023/5/5 こどもの日
2023/7/17 海の日
2023/8/11 山の日
2023/9/18 敬老の日
2023/9/23 秋分の日
2023/10/9 スポーツの日
2023/11/3 文化の日
2023/11/23 勤労感謝の日

■土日、祝日のセルに色を付ける例(条件付き書式で使う数式)

色を付けたい日付の範囲を選んでおいて条件付き書式では次の数式を使う。

・祝日に対応
=COUNTIF(n_祝日,$A1)=1
テーブルの日付が入った範囲(セル A2 から A18 )の範囲に「n_祝日」という名前を付けている場合。
また、色を付けたい日付はセル A1 から入っている場合。

休暇など祝日以外の日付に色を付ける場合は、一覧にその日を追加する。
元の表をテーブルに変換してあれば数式を修正しなくてもよい。

・土曜日に対応
=TEXT(A$1,"aaa")="土"
1行目に(横に)日付が入っている場合。
A列に(縦に)日付が入っている場合は、第一引数を $A1 にする。

・日曜日に対応
=TEXT(A$1,"aaa")="日"

| | コメント (0)

Excel用2023年(令和5年)の祝日

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.62 (22061100)

下記の表をエクセルのシートにコピー貼り付けする。

祝日 名称
2023/1/1 元日
2023/1/2 元日の振替休日
2023/1/9 成人の日
2023/2/11 建国記念の日
2023/2/23 天皇誕生日
2023/3/21 春分の日
2023/4/29 昭和の日
2023/5/3 憲法記念日
2023/5/4 みどりの日
2023/5/5 こどもの日
2023/7/17 海の日
2023/8/11 山の日
2023/9/18 敬老の日
2023/9/23 秋分の日
2023/10/9 スポーツの日
2023/11/3 文化の日
2023/11/23 勤労感謝の日

■土日、祝日のセルに色を付ける例(条件付き書式で使う数式)

・祝日に対応
=COUNTIF(祝日,$A1)=1
上記の表をテーブルに変換して、日付が入っているセル範囲には「祝日」という名前を付けている。
色を付けたい日付は、セル A1 から下に入っていると想定。

条件付き書式で数式を使う場合、テーブル名を関数の引数として使うとエラーが起きるので、セル範囲に名前を付けてある。

有給休暇など祝日以外の日付にも色を付ける場合は、一覧にその日を追加する。
(元の表をテーブルに変換してあれば、追加された日付に自動的に対応できる。)

・土曜日に対応
=TEXT(A$1,"aaa")="土"
1行目に(横に)日付が入っている場合。
A列に(縦に)日付が入っている場合は、第一引数を $A1 にする。

・日曜日に対応
=TEXT(A$1,"aaa")="日"

| | コメント (0)

Excel の数式内で全角のテーブル名を呼び出す。

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.61.1 (22052000)

H.K さん。

テーブル名を「すべて全角日本語」で付けている場合に、数式内でそのテーブル名を呼び出すやり方を見つけました。

全角日本語のテーブル名は呼び出せないと私は思っていましたが、やりようはあったので一応ここに載せておきます。

でも、最初に言っておくと「ちょっとめんどくさい」です。
私ならテーブル名の先頭に英字を添えるやり方をします。

■課題

テーブル名を「すべて全角」で付けていると、数式内でそのテーブル名の一部を入力しても関数リストのような候補が表示されない。

次図では「テーブル1」というテーブル名が付いている。

01_20220610210601

数式内でテーブル名の一部を入力しても、テーブル名の候補は表示されない。

02_20220610210601

■対処

全角のテーブル名を候補に表示させるには、いったんそのテーブル名の頭2文字を入力してから右端の一文字を消してやる。

「テーブル1」なら「テー」と打ってから「ー」を消すと、テーブル名の候補が表示される。

03_20220610210801

表示された候補は、Tab キーで数式内に入力できる。

04_20220610210901

続けて [ (ブラケット)を打つと、テーブルの「見出し」が候補に表示される。
(ブラケットは「角括弧(かくかっこ)」ともいう。)

↓キーで目的の見出しを選んだら、Tab キーを押す。
選んだ見出しのデータがスピルされる。

05_20220610211101

テーブル名を入力しただけだと、テーブルのすべてのデータ(見出し以外)がスピルされる。

セル D12 には =テーブル1 という式が入っている。

06_20220610211501

なお、「全角で付いている名前の頭2文字を入力してから右端の一文字を消せば、候補を呼び出せる」やり方は、テーブルだけでなく、セルやセル範囲に付けた「名前」についても使えます。

| | コメント (0)

Excel for Mac の「条件付き書式」の入力欄で、セルを「編集」モードに変える F2 キーを使う。

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.59 (22031300)

H.K さん。

Excel for Mac の「条件付き書式」の入力欄で F2 キーを使ってセルのモードを変えるやり方です。
ここにも書いておきますね。

■課題

「条件付き書式」で「スタイル」を「クラッシック」に、「数式を使用して、書式設定するセルを決定」を選択して、入力欄に数式を入力するとき、F2キーで「編集」モードに変更できない。
(カーソルキーを動かすと、不要なセル参照が入ってしまう。)

入力欄に手入力(コピー貼り付けを含む)で数式を入力すると、F2キーで「編集」モードに変更できない。

■最初に入力欄に数式を入れるとき

入力欄にカーソルを置いて矢印キーを一度動かすか、どこかのセルをクリックするかして、いったんセル参照を入力欄に入れたあとなら、F2キーで「参照」と「編集」のモードを切り替えられる。

■既存の数式を編集するとき

「条件付き書式」-「ルールの管理」-「ルールの編集」を選んだ直後(入力欄内の数式が選択されている状態)でカーソルを動かすと入っていた数式が消えてしまう。

Syosiki_rule_hensyu

数式が選択されている状態で command + C でコピーを実行する。
そのあとで入力欄内にカーソルを置くと、カーソルキーを動かしても数式は消えない。

この場合も、カーソルキーを動かすかしてどこかのセル参照が入ったあとなら、F2キーで「参照」と「編集」のモードを切り替えられる。

「いったんなにかしらのセル参照を入れる」ことが、F2キーでモードを変えるためのミソですね。

ミソといっていいのでしょか。
「無用なセル参照を一度入力する」という無駄なことをしてますからね。

まあ、今のところ Excel for Mac ではそうなってると思って使うしかないですね

改善要望を送っておきました。

| | コメント (0)

Excel で " (ダブルコーテーション)を文字列として扱う。

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.57 (22011101)

H.M さん。

この前に話した「エクセルで、ダブルコーテーションを文字列として扱うやり方」をまとめてみました。
関数に限らず、VBAでも同じ考え方が使えます。

「まとめた」なんて偉そうなことを言っていますが、私もひとから教えてもらったことをそのままお伝えしているだけなんですけどね。

■『文字列として扱う値は、" (ダブルコーテーション)で囲む』が、大原則。

  • " は、エクセルのなかで「ある値を文字列として認識させるために、その値の両端を囲う」という特別な役割をもった記号。
  • " 自体を文字列として認識させるときは、その「特別な役割」からまず解放してやらないといけない。
    そのためには「エスケープ文字」というものを " に添える。
  • そのエスケープ文字は、エクセルでは、" の左側に同じ " を1つ添えることになっている。
  • ややこしいが慣れるしかない。マイクロソフトがそう決めたから。

なお、エクセルではエスケープ文字として使うときも、そうでないときも同じ " を使うので「エスケープ文字としての "」を、 " の左側に付けても、右側に付けても起こることは同じです。

同じですが、エスケープ文字は普通は左側に付けるので、ここでも「エスケープ文字としての " は、 " の左側に付ける」ことにしています。

ちなみに、Wikipedia の「エスケープ文字」の項には、「それに続く文字について別の解釈をすることを示す文字」とあります。
「エスケープ文字としての " 」を添えることで、「この " は特別な役割をもたない単なる文字列として扱うことにする」という解釈をエクセルにさせるようにしたとも言えますね。

■1つだけの " を文字列として扱うには。

1つだけの " を文字列としてエクセルに認識させるには、次のように書く。

01_20220212212101

エクセルが文字列として認識しているのは、左から3つ目の " のみ。

■ " が4つ並ぶ考え方の詳細

まず、 " の左側に「エスケープ文字としての " 」を1つ付ける。
左側にある赤色の " が「エスケープ文字としての " 」。

02_20220212212101

これで、右側にある " (青色の " )から「特別な役割」を無くしたことになる。

次に、「文字列として扱う値は、ダブルコーテーションで囲む。」のが大原則なので、エスケープ文字も込みで両端を " で囲む。

03_20220212212101

これで、1つの " (青色の " )を、エクセルに文字列として認識させる指定をしたことになる。

「エスケープ文字としての " 」と「それが添えられている " 」を一つの組み合わせとして捉えると、ややこしさが少しでも減るかと思います。

09

■ " 込みで値を文字列として扱うには。

たとえば、「ダブルコーテーションで囲った北海道」

04_20220212212301

を、文字列として認識させる書き方は、

05

になる。

「(1つの " を文字列として扱うには) " を4つ並べる」と思い込み過ぎていると、気持ち的には、次のように「北海道の左右に4つの " を付ける」としたくなるかもしれないが違う。

・誤った書き方
06

この場合は、

"北海道"

を一塊の文字列として認識させたいので、

まずは "北海道" という文字の両側にある " の左に「エスケープ文字としての " 」を付ける。

""北海道""

次に、 ""北海道"" の両端を " で囲む。

"""北海道"""

これで、ダブルコーテーション込みの

"北海道"

を文字列としてエクセルに認識させることができる。

10_20220213090101

■使用例

関数での例

値のなかに " で囲った部分がある。
元の文字列は、 123:"北海道"JKL というもの。
(上記の説明と見比べながら下図を見ていくと、関数の引数での " の扱い方について理解が深まりやすいと思います。)

FIND関数の構文
FIND(検索文字列,対象,[開始位置])

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

IFS関数の構文(Microsoft365サブスクライバーなら使える。)
IFS(論理式1,値が真の場合1,[論理式2,値が真の場合2],[論理式3,値が真の場合3],…,[TRUE,どの論理式にも当てはまらない場合])

・「北海道」を抜き出す。( " の内側だけ抜き出す。)

11

・「"北海道"」を抜き出す。( " も込みで抜き出す。)

12

VBAでの例

・「文字列として認識させたダブルコーテーション」を、「北海道」の両側に結合。

13excel_vba_double_quotation

・「ダブルコーテーションで囲った北海道」を一塊の文字列として認識させる。

14excel_vba_double_quotation2

| | コメント (0)

Excel で、文字列の中に在るか無いかわからない特定の文字の位置を求める。

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.55 (21111400)

H.K さん。

セルに入っている文字列の中に、ある文字が在るのか無いのかわからないけれど、在るならその特定の文字の位置を在るだけ求めるやり方です。

■使っている関数について

難しく考えなくてもよいです。FIND関数のみでやっています。
FIND関数の書式は次のようです。

FIND(検索文字列,対象,[開始位置])

通常、FIND関数で特定の文字列の位置を求めるときは、「検索文字列」が「対象」の中に在ることがわかっている場合が多いですよね。

今回の場合は、「検索文字列」が「対象」の中に在るかどうかはわからない。
でも、在るなら(複数在るならそれぞれ)その位置を知りたい。ということですよね。

使い慣れたFIND関数を使っていますし、作業列を用いて処理の過程を分けているので、何をどうしているかが理解しやすいと思います。

仕事で作るファイルは、いつ誰が見ても理解しやすく作っておくべきだと私は思っています。
H.K さんや周囲の人たちにとって、他にもっと理解しやすいやり方があったら私にも教えてくださいね。

■処理

図の A 列が「検索文字列」で、ここでは大文字の A です。
B 列が「対象」です。

FIND関数の書式と見比べて何をどうしているかをより理解しやすくするために、書式に列の並びを合わせてみました。

「大文字の A」が B 列の文字列の中にあれば、その位置を C 列以降で求めています。
1つ目の A から 4つ目の A までを探しています。

「検索文字列」と「対象」の値や C 列以降の列数は、実務に合わせて変えてください。

図では「検索文字列」が複数個続いて並ぶ場合は想定していません。
実際には、もう少し列数が必要になることがあるでしょう。

6列ぐらい作っておけば、うちの業務ではたいてい対応できるのではないでしょか。

Find_20211118130501

C 列がエラーなら「検索文字列」は無い。

上図の2行目では「検索文字列」が「対象」の中に3つ在るのがわかりますし、3行目では「検索文字列」は無いことがわかります。

「1つ目の検索文字列の位置」( C 列)でエラーが起きていると、「検索文字列」は「対象」の中に在りません。

「検索文字列」を「対象」の1文字目から探していって最初に出現する位置が、 C 列に表示されるからです。
何文字目であっても、そこが「最初に現れる(1つ目の)位置」です。

C 列がエラーを起こしたら、D列以降も全部エラーになります。
「検索文字列」が「対象」の中にどこにも無いということです。

C 列に値が入って、D 列以降全部がエラーになることはあります。
ようするに、「検索文字列」が「対象」の中に現れるのは1箇所だけということです。

C 列以降でエラーを起こす列が飛び飛びになることはありません。
たとえば、「1つ目の検索文字列の位置」( C 列)と「3つ目の検索文字列の位置」( E 列)に値が入って、その他二つの列共にエラーを起こすなどは起きません。

「1つ目の検索文字列の位置」に値が入って、次に値が入るとしたら必ず「2つ目の検索文字列の位置」( D 列)に入ります。
E 列も F 列も同じです。

当たり前ですが、すべて数式が正しければです。念のため。

FIND関数は、大文字と小文字を区別する。

図からわかりますよね。
「小文字の a」は無視して「大文字の A」の位置を返しています。

FIND関数の引数「開始位置」について

C 列のセルに入れている数式では、FIND関数の引数「開始位置」を省略しています。
この引数を省略すると、 1 (「対象」の1文字目から数える)ことを指定したのと同じ意味になります。

2つ目以降の A の位置を求めるには、引数「開始位置」に同じ行の左隣のセルを指定して 1 を足せばよいです。
1つ目の A が 5 文字目だとすると、5 + 1 で 6 文字目が「開始位置」になります。

6文字目から数えていって見つかる A の位置が、「対象」の中にある2つ目の A の位置です。

このやり方は、MID関数とFIND関数を組み合わせて文字列を抜き出すときに使う指定の仕方と同じです。

エラー以外のセルの数を数える。

「検索文字列」が「対象」の中にいくつ在るかが一眼でわかるようにしたいときもありますよね。

例のようにセル範囲にエラーを含む表なら、Excel 2010以降ではAGGREGATE関数が使えます。
( Microsoft 365 サブスクライバーでなくても使える関数です。この関数は私も最近知りました。)

AGGREGATE関数は「エラーのセルを無視して計算」します。

書式は次のようです。
詳しくは、Excel のヘルプを見てください。

AGGREGATE(集計方法,オプション,参照)

次図( G 列)では、引数「集計方法」に 2 、「オプション」に 6 を選んでいます。

「集計方法」の 2 はCOUNT関数を指定したことになります。
COUNT関数で、数値が入っているセルの個数を数えます。

「オプション」の 6 が「エラー値を無視」する指定です。

Find_

列見出しは「絞り込み用」としてみました。

オートフィルターで絞り込めば、「検索文字列」が「対象」の中に在る個数ごとの一覧を得られます。

| | コメント (0)

より以前の記事一覧