エクセル

Excel で複数のシートを同時に再表示する。( VBA 不使用)

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

■MacBook Pro macOS Big Sur バージョン11.12.3
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.47.1(21032301)

H.M さん。

Excel で複数のシートを同時に再表示できるようになりました。
Shift キーと command キーを使って、どのシートでも自由に選択できます。

これでもう、VBA や「ユーザー設定のビュー」を利用することはないでしょうね。

どうやら、Microsoft 365 サブスクライバーに提供されている機能のようです。
リンク先に Excel for Microsoft 365 の記載があるので。

Excel の新機能を見つける(マイクロソフトのサポートのページ)

Hukusu_sheet_saihyouji

Windows版Excel でも同じことができました。

・Windows10 Pro バージョン20H2
・Excel バージョン 2102 (ビルド 13801.20360)

Windows版Excel の場合に使うキーは、Shift キーとControl キーですね。

| | コメント (0)

Excel で 0 は表示するが単位は表示しない。

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

■MacBook Pro macOS Big Sur バージョン11.12.1
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.46(21021202)

K.M さん。

セルの値について、数字の 0 は表示するだけで、単位は付けない例を以下にまとめておきます。

課題

人数が入ったセルに単位として「名」を付ける。
ただし、0 の場合は単位は付けないで 0 だけを表示する。

0ni_tani_hyoujisinai1

を、次のようにする。

0ni_tani_hyoujisinai2

やり方

『ユーザー定義の表示形式』を使う。
(いわゆる、「ユーザー定義書式」です。)

今回の場合にユーザー定義として指定する書き方。

G/標準"名";;0;

半角の ; (セミコロン)で区切ることで、最大4つの表示形式を指定できる。

0ni_tani_hyoujisinai3

User_teigi_syosiki1

慣れないとわかりづらいですが、落ち着いて見ていけばわかると思います。

G/標準"名";;0; を、

正の値の指定;

負の値の指定;

0の場合;

文字列の場合

で分けて見ると次のようです。

User_teigi_syosiki2

 

| | コメント (0)

"Excel の表全体を対象とした重複の探し方" を見つけるのが面倒なのよ。

アクセス解析を見ると、「excel 表全体 重複」というキーワードで、うちのブログにやってくる人がぽつぽついるよう。

エクセルの表から重複データを見つけようするのは、大昔からある「エクセルあるある」だろうと思うし、値の重複を見つけるやり方は検索すればいくらでも見つかると思う。

けど、検索でも意外に見つけ出すのが難しいと感じたのが、列や行内での重複ではなくて「表全体でどこかにある重複を見つけるやり方」。

こういうやつ。

Hyou_ataityouhuku1

範囲のなかで重複している値は……

Hyou_ataityouhuku2

……です。

検索して「あっ! これかな?」なんて期待でページを開くと、「これじゃないなあ……」となったりして。

だから、前に覚書を作った。

先述の方たちがお探しなのも、たぶんこれだと思う。

ということで、

Excel の表全体でどこかにある重複する値を見つける。

リンク先は Windows での話だけど、Excel for Mac でもそんなに変わんない。

| | コメント (0)

テーブル名の先頭にアルファベットを付けておくと便利

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

■MacBook Pro macOS Big Sur バージョン11.12.1
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.46(21021202)

H.K さん。

お伝えしたエクセルのテーブル名の件ですが、下記にまとめておきます。

・テーブル名の先頭にアルファベットを付けておくと、数式のなかで呼び出しやすくて便利

特にアルファベットでなくてもよい。
テーブル名の先頭に使えるのは、文字、アンダーバー (_)、またはバックスラッシュ (\) 。

先頭以外で使えるのは、文字、数字、ピリオド、アンダーバー。(ハイホン、スペースは使えない。)

詳細は、次のリンク先(マイクロソフトのサポートページ)を参照。

Excel テーブルの名前を変更する

テーブル名の先頭にアルファベットを付けている例

数式のなかで、そのアルファベットを打つとテーブル名を候補に表示させられる。
次図では、「t_一覧」というテーブル名を付けている。

Table_name1

別のセルでテーブルの見出し部分を呼び出す例

イコールに続いて t を打つとテーブル名が表示される。

Table_name2

なお、t の次に「_(アンダーバー)」を付けているのは、Windows版Excelでの利用も考えてのこと。

テーブル名の先頭が t だけだと、Windows版Excelの場合は、テーブル名があるのは表示される候補の下の方になる。
スクロールバーを下に動かしてそこまで見ないとテーブル名が出てこない。

Windows版Excelでは、t に続いてアンダーバーを打つと、テーブル名が上の方に表示されてくるので選択しやすくなる。

テーブル名が選ばれていることを確認して、Tabキーを押すとテーブル名が数式のなかに挿入される。

続いて [ (ブラケットまたは角括弧)を打つと、入力候補が表示されるので、↓キーで選んでTabキーを押す。
次図では、 #見出し を選んでいる。

Table_name3

Tabキーを押すと数式のなかに挿入される。

Table_name4

右端に ] を打って、Enterキーで確定したところ。

テーブルの「見出し」が入力される。

Table_name5

このように、入力していないセルまで数式が自動的に入力されていくのは、Microsoft365サブスクライバーのエクセルでだけ。

「Spill(スピル)」という仕組み。

スピルで埋まった隣のセルを見てみると、セルの中で数式がうっすらとグレーになって表示されている。
これを「ゴースト」という。

ゴーストのセルをダブルクリックするとわかるが、セルのなかには実態としては何も入っていない。
(スピールは、コピーするのではなくて結果を表示している。)

Table_name6

関数のなかでテーブル名を呼び出している例

XLOOKUP関数のなかでテーブル名を呼び出しているところ。

t を入力するとテーブル名が表示されるので選択しやすい。
(XLOOKUP関数は、永続ライセンス版のエクセルでは使えない関数)

Table_name7

先と同様に [ を打って、↓キーで選んでTabキーを押すと候補を挿入していける。

Table_name8

] を打って、Enterキーを押して確定したところ。

Table_name9

「名前」でも同じことができる。

セルやセル範囲に付ける「名前」についても、同じ使い方ができる。

名前の先頭にアルファベットを付けておいて、それを打つことで数式のなかで「名前」を簡単に呼び出せる。

| | コメント (0)

Excel で同じ値を続けて入れる。

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

■macOS Catalina バージョン10.15.7
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.45(21011103)

H.K さん。

「エクセルで同じ値を続けて入れる」件について、お伝えした考え方を下記にまとめておきます。

課題

こういうやつから、

01onajiatai_20210210132701

こういうのを作りたい。

02onajiatai

A
A
B
B
とセルに入力してドラッグしても、狙い通りにならない。

03onajiatai

04onajiatai

考え方

作業列を使う。

隣の列に連番を入れておいて、それも含めてコピーして増やす。
セル B1 から B5 に連番を。A1 から B5 をコピーして、 A6 を選択して貼り付ける。

05onajiatai

全体を選択して、セル B1 をアクティブにする。
(Tabキーでアクティブセルを変えられる。行きすぎたら shift+Tabで戻る。)

06onajiatai

B1 がアクティブになっていることを確認して、「昇順」で並べ替える。
これで同じ値が連続する状態になっている。

07onajiatai

B列の値を削除する。

08onajiatai

これで完了。

ちなみに、同様の考え方で1行ごとに空白行を入れたりもできます。

ようするに、発想しだいというわけですね。

09onajiatai

10onajiatai

| | コメント (0)

Excel の「参照元」と「参照先」の違い

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

■macOS Catalina バージョン10.15.7
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.45(21011103)

参照元とは

数式の中でセル参照によって指し示されているセル。

参照先とは

数式が書き込まれているセル。

Sansyoumoto_sannsyousaki_tigai_20210125114101

参照元を選択するショートカットキー

参照先を選択して、control + [ を押す。
参照元が別シートにある場合も選択できる。

Sannsyoumoto_sentaku

Sannsyoumoto_sentaku_spill

参照先を選択するショートカットキー

参照元のセルを選択して、control + ] を押す。
参照先が別シートにある場合は、このショートカットは効かない。

| | コメント (0)

Excel で土日、祝日のセルに色を付ける。(TEXT関数を使用)

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

■macOS Catalina バージョン10.15.7
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.44(20121301)

S.R さん。

Excel で土日と祝日のセルに色を付けるやり方ですが、TEXT関数を使うやり方を教わったので共有です。

ネット上では、条件付き書式とWEKKDAY関数を組みわせる例をよく見ます。

私もこれまでWEEKDAY関数を使っていましたが、わかってみると、TEXT関数を使ったほうがよりよいと感じました。
というのは、条件付き書式のなかで数式を見て何をしているかを理解しやすいのは、TEXT関数のほうだからです。

仕事でつくるファイルは、誰のものでもなく皆のものです。
誰がいつ見ても理解しやすくつくっておくほうがよいと、私は思います。

■使用する機能
名前の定義
条件付き書式

■使用する関数
TEXT関数
COUNTIF関数

祝日の一覧をつくっておく。

祝日のセルに色を付けるには、元になる祝日の一覧を用意しておかなければなりません。
これは地道に手作業ですね。エクセルには、日本の祝日を一覧で出力してくれる機能や関数はありません。

祝日の一覧ができたら、それらのセル範囲に名前を付けておいてください。
下記では、セル A1 から A17 までを選択して、「祝日一覧」という名前を付けています。

B列にある祝日の呼称は、あったほうが見やすいかなと思って添えただけです。
このあとの作業ではB列は使わないので、無くてもかまいません。

01namae_syukujituitiran

土日の日付が入ったセルに色を付ける。

次図は色を付けたいセル範囲( A1 から A24 )です。
曜日も見えたほうがわかりやすいと思ったので、そうしてあります。
曜日はユーザー定義書式を使って表示しています。

02hiduke

ユーザー定義書式では、 yyyy/m/d(aaa) と指定しています。
これで日付の右隣に、カッコで囲んだ曜日が表示されます。

aaa で 土、日といった漢字一文字での表示になります。

"土曜日" というように "曜日" まで表示したければ、aaaa と a を4つ打ってください。

03cell_userteigi_youbi

小文字の a を使った曜日の表示の指定は、TEXT関数の引数でも使います。
TEXT関数の書式は、 TEXT(値,表示形式) です。

図では、TEXT(A1,"aaa") の式で「金」という文字列が返っています。
TEXT関数が返す答えはすべて文字列です。

04text_kansu

このTEXT関数を使って、「TEXT関数の答えが "土" なら、セルに色を付ける」、「"日" ならセルに色を付ける」という条件を、条件付き書式で設定します。

次図での数式は、 =TEXT(A1,"aaa")="土" となっています。
「土曜日を条件にしている」と、ぱっと見て理解しやすいと思います。

色は適当に選びました。

Joukentukisyosiki_text_kansu_doyou

さらに、右端の "" のなかを 日 に変えた条件を追加します。

Joukentukisyosiki_text_kansu_nitiyou

これで土日の色付けはできました。次は祝日です。

なお、上記では条件付き書式を設定している列が一列だけなので、数式のなかのセル参照は相対参照のままにしています。
複数列に対して条件付き書式を設定するなら、列を絶対参照にしてください。

たとえば、上記の数式では、=TEXT($A1,"aaa")="土" のようにA列を絶対参照にします。
A列にある日付の曜日が条件なので、A列を絶対参照にしておかないといけません。

あるいは、1行目に日付が入力されていて、複数行に条件付き書式を設定するなら、=TEXT(A$1,"aaa")="土" ですね。

祝日の日付が入ったセルに色を付ける。

ここで COUNTIF関数を使います。
指定した日付が、祝日の一覧のなかに「いくつあるか」を調べます。

当然ですが、答えは常に「1」です。
ある日付を祝日の一覧のなかで探すのですから。

ということで、「COUNTIF関数の答えが 1 である」という数式を、条件付き書式で設定します。
条件付き書式で設定する数式は次のようです。

Joukentukisyosiki_counif_iro

COUNTIF関数の書式は、COUNTIF(範囲,検索条件) です。

引数「範囲」には、さきに名前を付けた「祝日一覧」を入力します。
(Excel for Mac には Windows版Excel のように F3 キーで名前を呼び出す機能は今のところありません。)

引数「検索条件」には、色付けしたい日付が入ったセルを指定します。

これで、土日と祝日のセルに色をつけることができます。

数式内のセル参照を相対参照にするか、絶対参照にするかはTEXT関数と同じです。

複数列に条件付き書式を設定するなら、COUNTIF(祝日一覧,$A1)=1 になります。
1行目に日付を入力している場合は、COUNTIF(祝日一覧,A$1)=1 になります。

祝日以外の休日への対応。

有給休暇とか祝日以外の休日にも色を付けたい場合は、祝日の一覧のなかにその日付を追加して、名前を付けたセル範囲を変えてください。
「数式」タブの「名前の定義」からできます。

WEEKDAY関数について。

WEEKDAY関数が返す答えは、常に数字です。

たとえば、引数「種類」で 2 を指定すると、指定した日付が月曜なら 1 を返します。火曜日なら 2 返します。
土曜日なら 6 で、日曜日なら 7 が返ります。

05weekday_kansu

下図では、「WEEKDAY関数の答えは 6 である(答えが土曜日である)」という条件を指定しています。

Joukentukisyosiki_weekday_kansu_doyou

引数「種類」で 2 を指定しているので、日付が土曜日なら 6 が返ってきます。
"2 を指定すると土曜日なら 6 が返る" と記憶していなければ、数式を見て何をしているかを理解できません。

それだけではありません。そのことを記憶していないと、そもそも数式を入力することもできません。
条件付き書式の入力欄では、関数ヒントが表示されないですから。

TEXT関数でも書式を記憶しておくことは変わりませんが、記憶にかかる負担が少ないです。
「第二引数を "aaa" とすれば漢字一文字の曜日が返る」と覚えておくだけです。

さらに、"2 を指定すると土曜日なら 6 が返る" というのは、WEEKDAY関数の引数「種類」で 2 を指定した場合です。

他の数字を指定したら、同じ土曜日でも返ってくる数字が異なります。
「種類」のところで 1 を指定したら、土曜日の場合は 7 が返ってきます。

こう考えると、TEXT関数を使うほうが誰がいつ見ても何をしているかを理解しやすので、この件ではTEXT関数を使うべきだと思いました。

| | コメント (0)

Excel用2021年(令和3年)の祝日(2021年の祝日移動対応済)

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

■MacBook Pro macOS Catalina
バージョン 10.15.6

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

下記のままエクセルのシートにコピー貼り付けする。
(NETWORKDAYS 関数などで、引数の [祭日] を指定する際に使う。)

・NETWORKDAYS 関数の書式(Excel for Mac ヘルプ より)
NETWORKDAYS(開始日, 終了日, [祭日])

NETWORKDAYS 関数は、引数「開始日」から「終了日」までの期間で、土曜、日曜、「祭日」を除く日が何日あるかを返す。「何営業日あるか」ということ。

日付と曜日を表示する場合、セルの書式の「日付」のところを変えるか、ユーザー定義書式で、

yy年m月d日(aaa)

といったように打つ。
この場合は、21年1月1日(金) といった表示になる。

a を4個打つと、

21年1月1日(金曜日)

となる。

日付のセルの中に曜日を入れてはいけない。
ただの文字列になってしまうので、日付を使った計算ができなくなる。日付はシリアル値のまま扱う。

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

2021年の祝日移動

2021年のみの特例措置。

・「海の日」
2021/7/22へ。
例年は、7月の第3月曜日。

・「スポーツの日」
2021/7/23へ。
例年は、10月の第2月曜日。

・「山の日」
2021/8/8へ。
例年は、8月11日。

| | コメント (0)

Excel で、整数Xと整数Yの比を求める。

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

■MacBook Pro macOS Catalina
バージョン 10.15.6

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

整数Xと整数Yの比は、整数Xと整数Yの最大公約数でそれぞれを割ると求めることができる。

整数Xと整数Yの最大公約数は、GCD関数で求めることができる。

X_y_no_hi_20200831163801

| | コメント (0)

Excel で、Zoom のチャットから発言者とコメントの一覧を作る。

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

■MacBook Pro macOS Catalina
バージョン 10.15.6

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

K.M さん。K.K さん。

テキスト保存した Zoom のチャットから、発言者名と正味のチャット部分というか、コメントというかの一覧を、Excel で作るやり方です。

■課題

保存した Zoom のチャットを開くと次のようになっていることが多いですよね。
macOS 用のテキストエディタ「mi」(えむあい)の画面です。
(通常は「テキストエディット」で開かれます。)

01zoom_chat

これをエクセルにコピー貼り付けすると、こうなります。

02_1zoom_chat

結果として、こうしたいということですよね。

02_2zoom_chat

■考え方

次図のB列の文字列で、
・" 開始 "("開始" の左右に半角スペースがある。)
・" : " (半角コロンの左右に半角スペースがある。)
が不要なわけですが、発言者名とコメントの境目にある ":" (半角コロン)は左右を分ける目印として生かしたいと思います。

02_1zoom_chat

そこで、
・" 開始 " をスペースごと取り除く。
・" : " からは、左右のスペースを取り除く作業をまずします。スペースを無くしておいた方が、このあと関数の数式を作りやすいので。

SUBSTITUE関数で、" 開始 " の部分を無くす置換をします。
また、 " : " は左右のスペースがない ":" (半角コロンのみ)に置換します。

そうすると、

Mitsuhide Akechi:6月2日の本能寺の件、よろしくお願いします。

ということになります。

これができれば、FIND関数・LEN関数・LEFT関数・MID関数を使って、":" の左右を分けて抜き出すことができます。

・FIND関数
元の文字列の中で ":" が左から何文字目にあるかを調べます。

・LEN関数
元の文字列の全文字数を調べます。

・LEFT関数
FIND関数の結果を使って、元の文字列の左端から ":" の1文字前まで(つまり、発言者名)を抜き出します。

・MID関数
FIND関数の結果を使って、":" の1文字右隣からコメントを抜き出します。
何文字抜き出すかの指定には、LEN関数の結果を使います。
LEN関数の結果を使うことで、コメントが何文字あっても全部抜き出すことができます。
(32,767文字まで抜き出せます。セルに入れられるこの文字数は Excel の仕様です。)

■実際の作業

次のようにしてみました。
図のH列とI列で、発言者名とコメントが分かれています。
(1行目の列見出しは適当につけてます。)

03zoom_chat

作業列を使っています。

先の処理を1つのセルでやってしまうと、何をしているのかを理解しづらい数式になってしまいます。
作業列を使って、処理の過程を複数に分けておくと、うちの社内では誰がいつ見ても理解しやすくしておけます。

詳しくは解説しませんが、何をしているかは見ればわかると思います。

関数の中に関数を挟んだ数式(「関数のネスト」といいます。)を、作ってはいけないということではないです。
ネストするしかない場合もありますから。

仕事で作るファイルは誰のものでもありません。職場の全員のものです。
引き継ぐかもしれませんし、流用するかもしれません。そんなときに、何をしているのかを理解しづらいファイルを渡されても、渡された方が困ります。

■チャットが改行されているとき。

次のようにチャットが改行されているときもありますよね。

04zoom_chat

これをエクセルにコピー貼り付けするとこうなります。

05zoom_chat

本来だと、A列の3行目から5行目までの文字列は、B列に入っていてほしいものです。

幸いなことに、このパターンには法則性があります。
B列のセルが空である場合は、A列に文字列が入っているという法則性です。

こういうときは、条件によって処理を分けるやり方が適用できます。

C列のセルに、「B列のセルが空欄ではないなら、そのセルの値を表示する。B列のセルが空欄ならA列のセルの値を表示する」という式を入れてやればいいんです。

図では、IFS関数を使っています。
(Excel2016以前だと、IF関数になりますね。)

06zoom_chat

そして、D列以降で上記と同様のやり方をします。

ただし、今回は、":" がなくて、「・」(中黒)があります。
また、この場合は「・」も含めて抜き出したいので、列「発言者とコメントの間の値の位置」は 0 になっていることが必要です。
さらに、列「発言者」では、発言者名がそもそも無いのでエラーが起きます。

このあたりも条件分岐で処理できます。

行頭に中黒を付けるときは、たいていそれも含めて抜き出したいでしょうから、下図のG列では ":" ならその位置を、中黒なら 0 を返すようにしてみました。

中黒以外が行頭に付くことは、そんなにないでしょうから、そこはもう手入力で対応してください。(下図F列)
行頭文字も含めて抜き出すなら、G列のセルに 0 を入力します。

列「発言者」(I列)のセルは、エラーの場合は非表示にしてみました。
エラーが見えたままよりも非表示にしておいた方が、一覧表としてより見やすくなるかなと思ったので。
IFERROR関数の引数として、LEFT関数を使っています。「関数のネスト」ですね。

最終的には、非表示のままにしておくのではなく、発言者名を入れておいた方がよいと思います。
発言者名で絞り込むこともできますから。

07zoom_chat

| | コメント (0)

より以前の記事一覧