エクセル

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)

Excel のソルバーで制約条件を整数にしてあっても少数になる。

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

■macOS Mojave
バージョン 10.14.3
■ Office365 Business サブスクリプション
Excel for Mac バージョン 16.22(190211)

ソルバーを使えるようにする。

メニューの「ツール」から「Excel アドイン」を選択。

「Solver Add-in」にチェックを入れて、OKをクリック。
これでソルバーが使える。

課題

ソルバーを実行すると、変数として指定したセルの制約条件を「整数」に指定してあるのに少数が入ってしまう。(下図:B列のセルが少数になっている。)

次の制約条件を守りながら、人数を変えていって総計金額が最大になるところを探っている。

・人数のセル( B2 から B4)は整数。(人数なので)
・総人数10名以下。
・各金額ごとに1名以上。(0名は無し。)
・総計金額30万円以下。

Solver_jikkougo1

原因

ソルバーの「オプション」で「整数制約条件を無視する」にチェックが入っている。

「オプション」を変更してソルバーを実行する。

メニューの「ツール」から「ソルバー」を選択。

「ソルバーのパラーメーター」の画面になるので、「オプション」をクリック。

Solver_option1

「整数制約条件を無視する」のチェックを外してOKをクリック。

Solver_option2

「ソルバーのパラメーター」の画面に戻るので、「解決」をクリックすると計算が始まる。

「ソルバーの結果」が表示されたら、変数として指定したセルに入っているソルバーの結果を確認。

ソルバーの結果がそれでよければ、「ソルバーの解の保持」にチェックを入れてOKをクリック。
(「計算前の値に戻す」にチェックを入れると、ソルバーの結果をセルに入れない。)

Solver_kaiketu

「整数制約条件を無視する」のチェックを外したので、変数として指定したセルに整数が入力される。(B列のセルが整数になっている。)

Solver_jikkougo2

| | コメント (0) | トラックバック (0)

セル内改行ごとに文章を分ける。

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

■macOS Mojave
バージョン 10.14.3
■ Office365 Business サブスクリプション
Excel for Mac バージョン 16.22(190211)

M.K さん。

職場に Mac が入ってきましたね。慣れていないので、使っているとなんか変な気分です。
いろいろ話を聞いていると、どうも今後は Mac になっていくような……。

うちの社内でバリバリとエクセルを使うというのなら、Windows版エクセルのほうがよいと思うのですが。

社内のPCがどうなるかなんて僕らにはどうにもならない話です。不平不満は飲み込んで、粛々と従うしかないですね。

とはいえ、愚痴を言いたくなるもので——Mac版エクセルは、致命的ではないですがどこか雑な感じがします。
なにより、Windows版には実装されている「データの取得」がありません。

これには驚きました。
Excel for Mac のユーザーはずっとこの環境を強いられていたんですね。今もまだ……。

米マイクロソフトのサイトにある "excel.uservoice" の "Add PowerPivot to Mac excel" というカテゴリーには、「早く実装してください!」といった声がたくさん寄せられています。
「データの取得」が実装されるのはいつになるやら——です。

そういえば、Office365サブスクリプションですよ。お金を定期的に取っておいて今まで放置——実装されるのはいつになるかわからない——なんていうのは……マイクロソフトもなかなか太いことをするなあと思います。

大人の事情がいろいろあるんでしょうね。

とまあ、セル内改行をしている文章を、それぞれ別のセルに分ける件です。
1つの段落内に「。」が複数箇所あるので、「。」を区切り文字付きの「。/」といった感じに置換するやり方では、改行ごとに分けることはできません。

Hukusuumaru_wo_kugirimojituki_ni_ku

■考え方
Windows版でもそうですが、セル内改行は改行コードを伴っています。目には見えませんが。
だとしたら、改行コードそのものを区切り文字に置き換えてやれば、エクセルの「区切り位置」の機能を使って、セル内改行ごとに分けることができます。

■手順
ここでは、CHAR 関数と SUBSTITUE 関数を使っています。

Cellnai_kaigyouwo_tikan

1. CHAR 関数で改行コードを生成
=CHAR(10) の数式で改行コードを生成できます。
Windows版エクセルと同じです。

Excel for Mac では「CHAR(13) が改行コードになる」との記述がネットでは見当たりますが、私の手元では CHAR(10) で改行コードを生成できています。

Excel for Mac のバージョンによって違うのかもしません。そのあたりは私にはわかりません。

2. SUBSTITUE 関数でそれを区切り文字(上図では半角の / )に置換
何をしているかがより理解しやすいように、作業列で CHAR 関数と SUBSTITUE 関数を分けました。(セル B2 と C2 )

セル A2 のなかにある改行コードが、セル C2 では / になっています。

区切り文字として使える字数は1文字。
仮に、SUBSTITUE 関数で改行コードを // に置き換えても、「区切り位置」の機能では使えない。

3. SUBSTITUE 関数が返してきた結果( C2 )をコピーして、任意のセルに「値貼り付け」。
C2 のままでは「区切り位置」の機能は使えないので、隣のセル( D2 )に値貼り付けしています。

4. セル D2 を選択しておいて、「データ」タブの「区切り位置」をクリック

5. 「区切り記号付き」にチェックを入れて「次へ」

01kugiriiti

6. 「その他:」をチェックして隣の欄に区切りとして使う文字を入力し、「次へ」
区切り文字は全角と半角を区別する。
SUBSTITUE 関数で改行コードを半角の / に置換しているので、ここでは 半角の / を入力している。

02kugiriiti

7. 今回扱っているのは単なる文字列なので、列のデータ形式は「標準」のままでよい。
「表示先」で任意のセル参照を入力。(図では E2 )

03kugiriiti

8. 「完了」をクリックするとセル D2 の文字列が別々のセルに分かれる。

| | コメント (0) | トラックバック (0)

Excel for Mac の VBE で変数の宣言を強制する。

この記事は自分用の覚え書きです。
(MacBook Pro (macOS Mojave 10.14.1) + Excel for Mac 16.19(181109) Office365 Business サブスクリプション)

Excel を起動したら、option + F11 で VBE を起動する。
(Touch Bar にファンクションキーが表示されていないときは、fn キーを押しながら、option + F11 を押す。)

画面上部にあるメニューバーの「Excel」から「環境設定」を選択。

「変数宣言が必要です」にチェックを入れて、OKをクリック

S20181124_125926

これで、変数を必ず宣言して使う状態になっている。

| | コメント (0) | トラックバック (0)

より以前の記事一覧