エクセル

Excel用2021年(令和3年)の祝日

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

■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/19 海の日
2021/8/11 山の日
2021/9/20 敬老の日
2021/9/23 秋分の日
2021/10/11 スポーツの日
2021/11/3 文化の日
2021/11/23 勤労感謝の日

| | コメント (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)

UNIQUE関数で重複する行をなくす。

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

■MacBook Pro macOS Catalina
バージョン 10.15.3

■Office365 Business サブスクリプション
Excel for Mac バージョン 16.34 (20020900)

M.H さん。

最近使えるようになった関数で、うちの仕事で役立つと感じた関数は、一つ前の記事で扱っているXLOOKUP関数の他に、FILTER関数、SORT関数、UNIQUE関数があります。どれもOffice365サブスクリプショナーだけが使える関数のようです。

検索すれば、どの関数も使い方を説明してくれているサイトはすぐに見つかります。

この記事では、UNIQUE関数が我々の仕事で役立つ点にふれたいと思います。

表から重複する行をなくしたいことがよくあります。
UNIQUE関数を使うと簡単にその状態をつくれます。

仕事で扱う「重複をなくした表」には2つの状態があります。

1. 元の表で重複している行を省いた一覧

2. 元の表のときに、そもそも重複がない行を抜き出した一覧

どちらも簡単につくれるのが、UNIQUE関数のお役立ちどころです。

Uniquekansudetukuru

また、XLOOKUP関数の記事では特にふれませんでしたが、「元のデータをいっさいさわらない」のも、これら新しい関数に共通の利点といってよい気がします。

絞り込みや並べ替え、重複をなくす作業のときに、元データをうっかり消したり書き換えたりしないようにシートをコピーしたり、並べ替えの前に戻せるように連番を振っておいたりと、ちょっと気をつかうことってありますよね。

そういう気づかいが不要になります。

UNIQUE関数の引数

数式を入力するときに表示される関数ヒントでは、UNIQUE関数の書式は次のようです。
必須な引数は「配列」だけです。

・マイクロソフトのサイトにあるUNIQUE関数のヘルプ
UNIQUE(配列,列の比較,回数指定)

引数が少し分かりにくいです。

・引数「配列」
いわゆる、セル範囲です。

・「列の比較」
「TRUE - 一意の列を返す」を指定すると、"列方向" をみていって重複をなくします。
「FALSE - 一意の行を返す」だと、"行方向" をみていって重複をなくします。

関数ヒントで「列の比較」と表示されるのがわかりづらいですね。

「行方向」と「列方向」

「行方向」と「列方向」は、ワークシートを理解していないと迷うところだと思います。

次のようです。

Gyouhoukou

Retuhoukou

行方向と列方向を逆に考えそうになるので、気をつけてください。

状態「1.」でも「2.」でも、引数「列の比較」には FALSE を指定する。

表から重複する行を探すということは、表を上下(行方向)にみるということです。
なので、上の「1.」の状態をつくるときも「2.」の状態をつくるときも引数「列の比較」は FALSE です。

引数「列の比較」に TRUE を指定することは、我々の仕事ではほとんどないでしょう。

「列の比較」という引数名がほんとにわかりづらいです。
もっとわかりやすい引数名にしてほしいと思います。

引数「回数指定」について

慣れないと、この引数もわかりにくいかもです。

関数ヒントでは、
・TRUE は「1回だけ出現するアイテムを返す」
・FALSE は「個別のアイテムをすべて返す」
となっています。

引数「配列」で指定したセル範囲のなかで、「一度だけ出現するもの」を抜き出すときは、TRUE を指定します。
上記の「2つの状態」の「2.」ですね。
「元の表のときに、そもそも重複がない行」は、選択範囲のなかで「1回だけ出現するアイテム」です。

「2つの状態」の「1.」のほう——セル範囲のなかから重複している行を省いた状態にするのが、FALSEです。
「個別のアイテムをすべて返す」というのは、「2つの状態」の「1.」のことです。 

■1. 元の表で重複している行を省いた一覧
UNIQUE関数はスピルします。次の図では、数式はセルG13にだけ入力しています。

G13の数式は、 =UNIQUE(B2:D10,FALSE,FALSE) です。

関数がスピルして埋まった範囲は青い枠線で囲まれます。

1nojoutai

■2. 元の表で、最初から重複がない行を抜き出した一覧
次の図のセルG13の数式は、 =UNIQUE(B2:D10,FALSE,TRUE) です。

2nojoutai

ひょっとしたら、この記事がよけいにわかりづらくしたかもしれませんね。
すみません。

とにかく、我々の仕事で使う頻度がより多いのは、

=UNIQUE(B2:D10,FALSE,FALSE)

のようにセル範囲に続いて両方の引数で FALSE を指定する数式でしょう。
「1. 元の表で重複している行を省いた一覧」をつくる数式です。

範囲が増えるなら「テーブル」にしておく。

上記の例ではセル参照で指定しています。
この場合、追加があって行や列が増えたら、引数「配列」の修正が必要です。

追加に対応しておくなら、元の表の範囲を「テーブル」に変換して、引数「配列」ではそのテーブル名を指定してください。

テーブル名には日本語が使えます。
テーブル名の頭に英字を付けておくと便利です。数式を入力するときにその英字を打ったら候補にテーブル名が表示されます。

日本語だけのテーブル名だと、それを全部打たないといけません。候補が表示されないんです。

並べ替えにはSORT関数

並べ替えするには、SORT関数を使ってください。

UNIQUE関数の結果は、エクセルの通常の「並べ替え」機能(「データ」タブの「昇順」・「降順」)では並べ替えできませんでした。

XLOOKUP関数も同じでした。たぶん、FILTER関数も同じだと思います。

Office365サブスクリプションのExcelでない場合は、UNIQUE関数の結果をコピーしてどこかに値貼り付けして「並べ替え」を実行するしかないですね。

| | コメント (0)

使うなら、最新関数 XLOOKUP

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

■MacBook Pro macOS Catalina
バージョン 10.15.3

■Office365 Business サブスクリプション
Excel for Mac バージョン 16.34 (20020900)

字余り……。

M.H さん。

Excel for Mac でXLOOKUP関数が使えるようになりましたね。
(さっき、Windows版 Excel も確認しました。バージョン2001(ビルド 12430.20264)です。)

すごいです。もうVLOOKUP関数で表引きしている時代ではありません。

使い慣れているからといって、いつまでもVLOOKUP関数を使うのは、うちの社内では「懐古主義に毒された老害だ」と言いたくなります。

これからはXLOOKUP関数の時代です! と、全角のびっくりマークで強調したくなります。

XLOOKUP関数の使い方は、インターネット上でいろんな人が解説をしてくれているので、探せばいくつでも見つかります。

ここでは、私が使ってみて、うちの仕事で役立つと思った点にふれておきます。

マイクロソフトのサイトのヘルプを見ると、XLOOKUP関数には「Office 365」の表示があるので、Office365サブスクリプショナーだけが使える関数のようです。

検索/行列関数 (リファレンス)

■引数「列番号」が不要

地味ですが、これがかなり便利です。

VLOOKUP関数の書式は次のようです。

=VLOOKUP(検索値,範囲,列番号,検索方法)

引数「列番号」を指定するのに、元の表の左端から地道に列の数を数えたことってありますよね。
「……9列目だから、9か……」とか。

それがもう不要です。

さらに、入力したVLOOKUP関数の数式を右にコピーするとき、「列位置」は変わりません。
「9 を 10 にして、次の 9 は 11 で、次は 12 で……」と地道な修正をしたこともありますよね?

それももう不要です。

「MATCH関数やCOLUMN関数を使えば列の位置がわかる」なんて話ではないです。
それをしなくてもよいという話ですからね。

XLOOKUP関数では、VLOOKUPの「列数」にあたる引数は、セル範囲で指定します。
書式は次のようです。

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合], [一致モード], [検索モード]) 

「戻り範囲」のところがそうです。

必須の引数は「検索値」「検索範囲」「戻り範囲」の3つです。
うちの仕事なら、たまに引数「見つからない場合」を使うときはあるでしょうが、残りの引数を使うことはとても稀だと思います。

■XLOOKUP関数は「スピル」します。

VLOOKUPと同じ使い方をするなら、スピルの元になるセルに数式を入れるだけで、他のセルには自動的に数式が埋まります。
数式のコピーが不要ということですね。

「スピル」を説明するのは難しいです。手元でやってみてください。
「自動的に数式を埋めてくれる」と書きましたが、実際には元になるセル以外のセルには数式は入っていません。

スピルで埋まったセルを見ると、数式がグレーになっています。"ゴースト" というそうです。
ダブルクリックすると、セルの中には何も入っていません。それでも値は表示されます。

Xlookup_vlookup

■列を抜き出したり、入れ替えたりが簡単

「列数の多い表で、特定の列だけ見えるようにしたい」ということがあります。

ちょっと前に、90列ほどある表から列をいくつか抜き出したことがありました。それも、並びを入れ替えて。
「12列目が一番左で、6列目がきて、23列目で、次が19列目で……」といった感じで。

90列もあると、飛び飛びの列だけが見えている状態にするのは、嫌になるほど面倒です。

そんなときも、XLOOKUP関数なら簡単です。

XLOOKUP関数では、引数「検索値」に表1行目の列見出しにある文字列を指定して、引数「検索範囲」では1行目全部を選ぶだけです。
(MATCH関数の要領ですね。)

で、その範囲から「検索値」が見つかったときに値を戻す「戻り範囲」には、表の2行目から下全部の範囲を選択します。
(引数「検索範囲」と「戻り範囲」は同じ大きさでないといけません。)

Xlookup_retuirekae

■引数「検索範囲」と「戻り範囲」を別々に指定できます。

XLOOKUP関数では、引数「検索範囲」と「戻り範囲」を別々のセル範囲にできます。

ということは、XLOOKUP関数では、表の列のどこであっても「検索範囲」と「戻り範囲」に指定できるということです。
右端でも左端でも途中でもどこの列でも検索値を探せて、どこの列からでも値を戻せます。
(この考え方は "行" に対しても同じです。)

なので、これまではINDEX関数とMATCH関数を組み合わせていた作業ですが、それも不要になるでしょう。
社内での使い方を考えると、全部XLOOKUP関数で対応できると思います。

「同じ結果が出るから、どちらのやり方でもよい」ということではないです。

これまでは2つの関数を組み合わせなければできなかったことが、1つの関数でできます。
効率の良さと、関数で何をしているかの読み解きやすさでいって、XLOOKUP関数を使うべきです。

うちの仕事でXLOOKUP関数が役立つと感じたところは以上のようです。
ぜひ、お手元で試してみてください。何か気づいたことがあったら私にも教えてくださいね。

| | コメント (0)

Excel用2020年(令和2年)の祝日

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

■MacBook Pro macOS Catalina
バージョン 10.15.1

■Office365 Business サブスクリプション
Excel for Mac バージョン 16.31 (19111002)

H.K さん。
2020年の祝日の一覧です。そのままエクセルのシートにコピー貼り付けできます。

NETWORKDAYS 関数などで、引数の [祭日] を指定する際に使ってください。

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

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

日付と曜日を表示したければ、セルの書式の「日付」のところを変えるか、ユーザー定義書式で、

yy年m月d日(aaa)

とか打ってください。
この場合は、20年1月1日(水) といった表示になります。

くれぐれも、セルの中に曜日を入れないように。
それをやったら、もうただの文字列です。日付はシリアル値のまま扱うのが鉄則です。

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

| | コメント (0)

Excel for macでセルの値を削除できなくして、オートフィルタは使えるようにしておく。

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

■MacBook Pro macOS Catalina
バージョン 10.15.1

■Office365 Business サブスクリプション
Excel for Mac バージョン 16.31 (19111002)

K.Y さん。

Excel for macでセルに入っている大事な値を消せなくして、オートフィルタは使えるようにしておく手順です。

■課題
オートフィルタで値を絞り込んで平均を求めるなどの作業をする。その途中でセルの値を消せないようにしておく。

■課題の実現のためにすることについて
一言でいえば、「シートの保護」です。
「シートの保護」で次の2つを行います。

・「ロックされたセルの選択」を禁止。
・「フィルター」の実行を許可。

「"ロックされたセルの選択を禁止" ってなんだ? セルがロックされていたら、もう何もすることはないだろう。」と感じるかもしれませんが、そこは慣れてください。
文字でうまく説明できません。ごめんなさい。

「シートの保護」という表現に、「シートが変更されないように守る」という印象をもつかもしれませんが、少し違います。
「"シート上で、できることとできないこと" を選択する」のが「シートの保護」機能だと思ってください。

課題を実現するために、「シートの保護」を実行する前に「コピーしたり、フィルタをかけたりできるようにしておくセルを決める」操作をします。

コピーしたり、フィルタをかけたりできるようにしておくセルを決める。

1.「シートの保護」の実行後に選択できるようにしておきたいセルを選択する。
コピーしたいセル、フィルタをかけるために使う列の1行目のセルなど。

2.「セルの書式設定」で「保護」タブをクリック

3.「ロック」のチェックを外してOKをクリック
これで、コピーしたり、フィルタをかけたりできるようにしておくセルを決めたことになります。

Cell_syosiki_hogo

「保護」タブの「ロック」にチェックが入っているのは、セルの標準の状態です。

「ロックにチェックが入っているのに、いままで入力したり削除できていたのか?」と感じるかもしれませんが、今はそのあたりも深く考えないで「そういうものだ」と思ってください。うまく説明できないです。ほんと、ごめんなさい。
どうしても気になるようでしたら、今度会ったときに聞いてください。口頭でなら説明できそうな気がします。

大事な値が入ったセルを削除できなくして、オートフィルタは使えるようにする。

1.表にオートフィルタを表示させておく。

2.メニューの「ツール」の「保護」から「シートの保護」を選択。

3.「ロックされたセルの選択」のチェックを外す。
「保護」タブでロックにチェックが入っているのは、セルの標準の状態です。
なので、ここのチェクを外すのは、それらのセルを選択できなくするということです。

そもそも選択できなければ、削除できないということですね。

4.「ロックされていないセルの選択」と「フィルタ」にチェックが入った状態にする。
先に、ロックのチェックを外したセルを選択できるようにするためと、オートフィルターを使えるようにするためです。

5.「OK」をクリック。
これで、そのシートでコピーしたり、フィルタをかけたりしたいセルは選択できるようになっています。
それら以外の大事な値が入ったセルは選択できなくなったので、削除できなくなっています。

Sheet_no_hogo

なお、「シートの保護」が実行してあるシートには、シート名の横に錠前のマークが表示されます。

Sheet_no_hogo2

| | コメント (0)

Excel で「上位○位」「下位○位」「それら以外」をわかるようにする。

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

■MacBook Pro macOS Mojave
バージョン 10.14.6

■ Office365 Business サブスクリプション
Excel for Mac バージョン 16.29(19090802)

I.E さん。

Excel で「上位○位」「下位○位」「それら以外の点数」がわかるようにするやり方を考えてみました。
あれこれと関数を使わないで、誰でもさわれるようにしておきたい。——ということだったので、関数1つとあとはオートフィルタで対応するやり方です。

手順さへわかれば「エクセルを使うのは初めて」という人にもやっていただけると思います。

■課題
データ数は1万2千件数百。
氏名の「ヨミ」で「昇順」に並び替えてあるので、「点数」の列は何の順にもなっていなくて、大小の点数が散らばっている。
そのままの状態で、「上位2千人」「下位2千人」「それら以外の点数」がわかるようにしておきたい。

■手順

以下の例では、わかりやすくするためにデータ数を15件にしてあります。
抜き出すのは、上位5位と下位5位にしてみました。
実際のデータ数は1万2件越えだそうですが、何件あってもやることは同じです。

こういう表ですよね。「ヨミ」の昇順で並べてあるので、点数は何の順にもなっていません。
(ネット上にある人名を生成してくれるサービスで、氏名をつくっています。)

00yomi_syoujun

やることは大きく3つです。

  1. 作業列を2つ追加して、点数について「高い方から数えた場合の順位」と「低い方から数えた場合の順位」を求める。
    使う関数は、RANK.EQ 関数だけです。
  2. 「高い方から数えた場合の順位」について、オートフィルタで1位から5位までを抜き出してセルに色をつける。
  3. 「低い方から数えた場合の順位」について、同じく1位から5位までを抜き出して、別の色をセルにつける。

「上位」と「下位」のセルに色をつけておけば、「それら以外」も見た目でわかります。

では、手順の詳細です。

1.「高い方から数えた場合の順位」と「低い方から数えた場合の順位」を求める。

上位○位と下位○位を見つけるために、RANK.EQ 関数を使って点数の順位をまず求めます。

順位を求めるために昔からあるのは RANK 関数ですが、現在は RANK.EQ 関数の利用をマイクロソフトは推奨しています。
互換のために RANK 関数は残っていますが、将来的にいつ廃止されてもおかしくありません。 ヘルプの RANK 関数の説明にもそんな風に書いてますから、これからは RANK.EQ 関数を使っておくのがよいと思います。

元の表に作業列を追加して、各人の点数について「高い方から数えた場合の順位」と「低い方から数えた場合の順位」を求めます。
D列とE列が作業列です。それらのセルに数式を入れています。

01koujunn_syoujun

RANK.EQ 関数の書式は次のようです。

RANK.EQ(数値,範囲,[順序])

引数「順序」に 0 (ゼロ) を指定するか、順序を省略すると、引数「範囲」のなか「数値」について、高い方から数えた順位が表示されます。
上図では、D列の数式内で 0 を指定しています。

セル D2 の数式は、=RANK.EQ(C2,$C$2:$C$16,0) になります。
引数「順序」は省略してもよいのですが、引数があることを一応示しておきたくて 0 を入れました。

D2 を下にコピーするので、「点数」の範囲が変わらないように絶対参照にしておくのが大事です。
セル C6 の 100 が、C列の中では一番大きいので、D6 に 1 が表示されています。

次は、E列のセルの数式です。セル E2 の数式は、=RANK.EQ(C2,$C$2:$C$16,1) です。
「順序」に 0 以外の数値(ここでは 1にしています。0 に対して 1 としておくのが考えやすいと思ったからです。 )を指定すると、「範囲」のなかの「数値」について、 低い方から数えた順位が表示されます。
上の図ではセル C15 の 39 が、C列の中で一番小さいので、E15 に 1 が表示されています。

なお、1万2千を超える行数だと数式をコピーするのは大変です。
図のような表だと、D2 に数式を入れたら、セルの右下角にカーソルを合わせて、パッドを2本指で2回タップすると、D列の一番最後の行まで数式が入力されます。
D列を入力したあとならE列でも同じことができます。

Fillhandl02

2.「高い方から数えた場合の順位」について、オートフィルタで1位から5位までを抜き出す。

順位を求めたら、「高い方から数えた順位」について上位5位を抜き出します。
D列でオートフィルタの「指定の範囲内」を選んで、「1以上5以下」を指定します。
(実際には、ここで「1以上2000以下」を指定ですね。)

データ件数がいくつかあるか(この場合は5つに決まってますが)は、ステータスバーに表示されます。
(図ではステータスバーは載せていません。)

02joui5nin01

上位5位が表示されます。

「点数」の列のセルに色をつけておきます。ここでは、薄い青にしてあります。

実際は2000件ですから、control+shift+↓で、一番下のセルまで一気に選択できます。
下図でいえば C4 をクリックしてから control+shift+↓ です。

03joui5nin02

RANK.EQ 関数は、同点の場合は同じ順位をちゃんとふってくれます。
上図では、セル D4 と D16 のどちらも 90点 で2位なので、2 が表示されています。

3.「低い方から数えた場合の順位」について、1位から5位までを抜き出す。

オートフィルタをクリアして、今度は「低い方から数えた順位」——下位5位までのデータを抜き出します。
することはD列のときと同じです。
E列でオートフィルタの「指定の範囲内」を選んで、「1以上5以下」を指定します。
(実際は、ここでも「1以上2000以下」を指定)

04kai5nin01

下位5位が表示されるので、「点数」の列のセルに先とは異なる色をつけます。
ここでは、薄いオレンジにしてあります。

05kai5nin02

これで「上位5位」と「下位5位」、「それら以外の点数」がわかるようになっています。

オートフィルタをクリアして全体を見てます。
下図のC列で薄い青がついているセルが上位5位、薄いオレンジがついているセルが下位5位です。
色がついていないセルが、それら以外の点数です。

06tensu_irodukego01

| | コメント (0)

「アクティブ列との相違」を使って、他のセルとは異なっている数式を見つける。

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

■macOS Mojave
バージョン 10.14.6

■ Office365 Business サブスクリプション
Excel for Mac バージョン 16.28(19081202)

「アクティブ列との相違」について。

「アクティブ列との相違」の実行にはショートカットキーが用意されている。
数式が入っているセルを選択しておいて、control + shift + ¥ を押す。

Active_retutonosoui01

他のセルと違っている数式が入っているセルが選択される。

Active_retutonosoui02

セルを選択する際に、どのセルがアクティブになっているかが大事。
上記の図では、E1 がアクティブになっている。
そのセルと比較して異なっているセルを選択することになるので。

範囲選択してからアクティブなセルを変更するには、Tab(shift + Tab)を押す。

上図のような表の場合、列の中の他のセルと違った数式が入っているセルがあると、そのセルには「矛盾した数式」のエラー表示がされるので、見た目で何か違っていることがわかる。

行数の少ない表ならそれで間に合うが、行数が多くなってくると、その中からエラー表示されているセルを視認するのは難しくなる。
そんなときに、このやり方が便利。

選択されたセルの数を確認する。

選択されているセルの個数は、ステータスバーに表示される。

Sentaku_cellnokazu

選択されたセルの数式を修正する。

セルが選択されている状態で、 F2 キーを押す。

一番上にあるセルが編集モードになるので、数式を修正する。

Cell_syuusei

修正が終わったら、command キーを押しながら Enter キーを押す。

修正した内容が他のセルにも適用される。

| | コメント (0)

Excel for Mac で、ふりがなが入力できない現象の回避

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

■macOS Mojave
バージョン 10.14.3
標準の日本語入力。

■ Office365 Business サブスクリプション
Excel for Mac バージョン 16.23(190309)

■起きている現象
option + shift + ↑ で、ふりがな編集の状態にできるが、ふりがなの入力ができない。
ひらがなを入力する状態にしてあるのに、英字しか入力できない。
ふりがなの領域をクリックしても、カーソルがそこにいかない。

Hurigana1

■回避策
option + shift + ↑ で、ふりがな編集の状態にしたら、なんでもいいのでいったん英字を打つ。
そうすると、ふりがなの領域をクリックできるようになり、ひらがなも打てるようになる。

Hurigana3

Hurigana4

Hurigana5

| | コメント (0)

より以前の記事一覧