エクセル

Excel で、セル結合を解除して一覧を作る。

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

■MacBook Pro macOS Ventura
バージョン 13.6.4

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

H.K さん。

なんと呼んでよいかわかりませんが、次のような作業のことですよね。

00_20240404114301

■考え方

A列のセルは、3行分のセル(次図A2からA4、A5からA7、A8からA10)ごとにセル結合しているので、「"セル3つ分の面積" のなかに文字列が入っている」ように見えます。

ですが、それはあくまで「そう見えるという "見かけ"」の話です。
エクセルとしては、「セル結合した3つのセルの一番上のセルに文字列が入っていて、あとの2つのセルは空欄」になっています。(次図D列)

01_20240404114401

そのことは、上図のG列のようにA列のセルを参照してみればわかります。

A2、A5、A8を参照している数式は文字列が返ってきています。
そのほかのすべての数式は 0 が返っています。A2、A5、A8以外は「空欄」だからです。

ということは、「セル結合した3つのセルの一番上のセルに文字列が入っている(一番上のセルが空欄ではない)」が成り立ったら、そのセルの値をそのまま表示してやればよいです。

さらに、空欄のセルについては、「セルが空欄である」が成り立ったら、1つ上の行のセルの値を表示してやればよいです。「一番上のセルが空欄ではない」が成り立ってその値が表示されているはずなので、その値が使えます。

02_20240404114401

■使っている数式について(相対参照についての理解が重要)

上記のことを数式で表すと、こうなります。
=IF(A2<>"",A2,D1)
(下図D列のセルに入っている数式です。)

ちょっと話が逸れますが、IF関数を使っている理由は、打たなければいけない引数を少なくしたかったからです。
IFS関数なら、こうなりますね。
=IFS(A2<>"",A2,A2="",D1)

03_20240404114501

このとき、重要なのは「相対参照とはどういう参照なのか」ということです。
なぜこの数式で上図D列の結果になるのかよくわからない場合、「仕事でエクセルを活用する」という観点からみて「相対参照」についての理解度が足らないといえます。

「相対参照」についての理解で大事なのは「位置関係」だといえます。
数式が入力されているセルから見て、数式内で使われているセルのアドレスがどういう位置関係にあたるかを意識できることが重要です。

たとえば、上図D2のセルに入れた数式、

=IF(A2<>"",A2,D1)

でいえば、数式が入っているセルD2と、引数に使っているA2の「位置関係」は「(D2から見て)"3つ左隣で同じ行のセル"」ということになります。

IF関数の第1引数なので、「(D2から見て)"3つ左隣で同じ行のセル" が空欄ではない」という論理式です。論理式が「真」ならば、「"3つ左隣で同じ行のセル"」 の値が返ってきます。

また、セルD1は、「(D2から見て)"同じ列で1つ上の行のセル"」ということになります。先の論理式が「偽」(空欄である)ならば、「"同じ列で1つ上の行のセル"」の値が返ります。

この数式を下に向かってコピーすれば、「(数式を入れているセルから見て、数式内で参照しているセルが)"3つ左隣で同じ行のセル" と "同じ列で1つ上の行のセル"」という「位置関係」はそのままに、A2とD1というセルのアドレスだけが変わっていきます。

その結果、「セル結合を解除して、そこに表示されていた文字列が一覧になる」状態を作ることができるわけです。

ここで「相対参照」についてよくわからなければ、私の説明の仕方が上手くないのが原因です。すみません。

とにかく、セルの参照の仕方「相対参照(絶対参照)」についての理解を深めておくことは、仕事でエクセルを使うためには必須です。
お互い励んでいきましょうね。

| | コメント (0)

Excel のIFS関数で点数による5段階評価:2通りの引数の書き方(「低」から「高」・「高」から「低」)

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

■MacBook Pro macOS Ventura
バージョン 13.6

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

K.M さん。

IFS関数で点数によって5段階評価を行う際に、引数の書き方が2通りあるという話について、ここにも残しておきます。

・点数による今回の評価段階
S:76 以上 100 以下
A:61 以上 75 以下
B:46 以上 60 以下
C:31 以上 45 以下
D:0 以上 30 以下

2通りの引数の書き方

IFS関数で点数によって5段階評価を行う際に、引数の書き方は次の 2 つ。

  • 「評価が低い」方から「評価が高い」方へ書く。
    ( D C B A S )
  • 「評価が高い」方から「評価が低い」方へ書く。
    ( S A B C D )

引数を指定する際の留意点

  • 「評価が低い」方から「評価が高い」方へ書く。
    ( D C B A S )
    「各点数幅の上限値以下」という指定の仕方をする。
  • 「評価が高い」方から「評価が低い」方へ書く。
    ( S A B C D )
    「各点数幅の下限値以上」という指定の仕方をする。

どちらを選ぶか。

どちらを選んでも結果は同じだが、「どちらでもよい」という考え方はしない。
関わるメンバー間で書き方を統一しておく。

01ifs_tei_kara_kou

02ifs_kou_kara_tei

選択の際の観点

日常の言い方に合わせておく。
そうすることで数式の自然な理解に役立つ。

会議などで「S,A,B,C,D」と口にしているなら、引数もそれに合わせて「評価が高い」方から「評価が低い」方へ書いておく。
逆に日頃から「D,C,B,A,S」と言っているなら、「評価が低い」方から「評価が高い」方へ書く。

まとめると……

03ifs_2toorino_hikisu

数式についてその他あれこれ

「0 未満の値」と「100 を超える値」の処理について

「0 未満の値」と「100 を超える値」が入力された際の処理については先の数式では考慮していない。
点数は、0 から 100 までなので、「0 未満の値」と「100 を超える値」はそもそもありえない。

こういう仕事の場合、点数を基準にして並び替える作業が必ずある。「0 未満の値」と「100 を超える値」が入っていても、その際に気づける。
したがって、それらの値についての処理をあらかじめ入れ込んでおく必要はないと判断した。
(必要性があるなら入れておく。)

IFS関数の引数で「TRUE」の未指定について

IFS関数では、引数の最後に「TRUE」とその「結果」を記述しておくと、「"左側のいずれもが 偽 である" が成り立つ場合」の処理を指定しておくことができる。

それを使って、「"セルが未入力" が成り立つ("左側のいずれもが 偽 である" が成り立つ)場合の処理」を指定できるが、先述の数式ではわざとそれをしていない。

論理式「TRUE」で「左側の論理式のいずれもが 偽 である場合の処理を指定する」と知っていなければ、数式の意味が理解しづらくなると考えたため。
「A1="",""」と書いてあれば、「セルが未入力の場合の処理を指定している」と一目でわかりやすい。

数式はセル内改行できる。

数式をセル内改行しておくことで、何をしているかがより理解しやすくできる。
(各引数の左端はスペースで揃えている。)

必ずセル内改行しておくべきものではない。場合によって活用する。

04ifs_cellnai_kaigyou

| | コメント (0)

数字のみ対象に掛け算する「PRODUCT関数」覚書

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

■MacBook Pro macOS Ventura
バージョン 13.6

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

エクセルで「掛け算」をするのに * (アスタリスク)を使う場合が多いが、デメリットがある。

アスタリスクを使う掛け算のデメリット

数式内で参照しているセルに「空欄セル」があると、計算結果が 0 になる。
「文字列が入ったセル」があると、#VALUE のエラーが起きる。

PRODUCT関数

掛け算の答えを返す関数。

引数で参照しているセル範囲にある「数字のみ」を対象に計算する。
空欄セル、論理値、および文字列はすべて無視する。
引数のなかに「空欄セル」や「文字列が入ったセル」があっても、計算結果が 0 になったり、エラーが起きたりしない。

セル範囲を引数にできるので、複数のセルを掛け算する場合にも便利。
=PRODUCT(A2:C2,E2:G2) は =A2*B2*C2*E2*F2*G2 の結果と同じになる。
=PRODUCT(A2:C2,2) は =A2*B2*C2*2 の結果と同じになる。

「全てのセルに数字が必ず入るとは限らない。」あるいは「"空欄セル" や "文字列が入るセル" があっても計算結果が得られるようにしておきたい」なら、PRODUCT関数を使っておく。

Puroduct

| | コメント (0)

「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)

より以前の記事一覧