パソコン・インターネット

Googleスプレッドシートで西暦から和暦を求める。

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

H.K さん。

Googleスプレッドシートで、西暦から和暦を求める表を作ってみました。
今のところ、エクセルのように表示形式で和暦を表示する機能は、Googleスプレッドシートには無いようですね。

Google Apps Script でのやり方とかネット上で見かけましたが、誰もがさわれるものではありません。
誰でもさわりやすいツールにしておくには数式を使うのがよいかなと思いました。
なので、数式で和暦を求めることにしました。

使っている関数は、YEAR関数、MONTH関数、IF関数とAND関数です。

■課題

Googleスプレッドシートにもともと入っている日付で、一番古いものは2000年7月。
一番新しいものは2019年2月。2月以降の日付は今後増えていく。
西暦/月/日 の形で入力してあるセルはそのままで、和暦だと何年何月かがわかるようにしたい。日は不要。

■考え方

Googleスプレッドシート上にある一番古い日付は2000年7月ということなので、この場合は平成と令和だけを考えればよいということです。
(昭和以前は不要)

今年2019年5月1日から、元号が平成から「令和」に変わりました。
2019年は平成31年と令和1年が重なっていて、考え方が少しややこしいのでいったん横に置いておきます。

考えやすいのは2018年(平成30年)かと思います。
まずはそこから考えていきます。

2018 から平成元年である 1989 を 引いて、それに 1 を足せば2018年が平成何年なのかがわかります。
2018-1989+1=30 になるので平成30年です。

なお、一番古い日付は2000年7月なので、昭和が平成と重なっていることも考慮しなくてよいということです。
ということは、Googleスプレッドシート上で平成元年以降の各西暦から 1989 を 引いて、それに 1 を足せばそれぞれの西暦が平成何年なのかがわかります。

2000年は、2000-1989+1=12 で平成12年です。
このように、西暦 - 1989 + 1 の式で、2018年までは平成何年かを求めることができます。
そして、平成31年にあたる2019年は平成と令和が重なっているので、式の答えが31未満のうちは、その西暦は全て「平成」です。

ちょっと工夫が必要なのが今年2019年ですね。
2019年1月から4月までが平成31年で、5月以降は令和元年(令和1年)になります。
西暦 - 1989 + 1 の式の答えは 31 です。

このことから、次の2つが同時に成り立てば、2019年のある日付は「平成」ということになります。
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 4 以下。(1 から 4 までのどれか)

同じように、次の2つが同時に成り立つ場合は、2019年のその日付は「令和」ということなります。
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 5 以上。(5 から 12 までのどれか)

そして、令和元年が平成31年と重なっているので、2020年以降では西暦について次のことが成り立てば、その日付は「令和」になります。
・西暦 - 1989 + 1 の答えが、31 より大きい。

■これらに基づいて表を作りました。

これらを全部処理する数式を、1つのセルに入れてしまうと、それも何をしているのかパッと見てわかりづらい数式になります。
作業列をつかって処理をわけました。そうしておけば、全体を通して何をしているのが誰にでも理解しやすくなるかと思います。

仕事で作るファイルは誰のものでもありません。皆のものです。
なので、仕事で作るファイルは、できる限り誰が見ても理解しやすく作っておくべきです。

Seireki_wareki01 

A列がもともと入っている日付です。

数式は以下のようになっています。
何をしているかを少しでも読み取りやすくなるように、各式をセル内改行してみました。

Seireki_wareki02

■各列に入っている数式

・西暦(B列)
=if(A2="","",
if(A2<>"",YEAR(A2),
))

B列では、A列をもとにYEAR関数で「西暦」だけを抜き出しています。
A列が空欄の場合はセルに何も表示しないように、IF関数で「A列のセルが空欄ならセルに何も表示しない」ということにしてあります。
というのは、A列が空欄だとB、C、E、F、G列に無用な答えがが入ってしまうからです。

・月(C列)
=if(A2="","",
if(A2<>"",MONTH(A2)
))

なので、C列以降も同様にして、数式内で参照している列のセルが空欄なら何も表示しないということにしました。
C列では、A列の日付からMONTH関数で「月」を抜き出しています。

D列の「平成元年」の 1989 は、例では手入力しています。
実際には、3行目以降は =$D$2 とかにして、先に入力した値を参照するようにしておけば、行をコピーして増やすときに便利かと思います。

・平成(E列)
=if(B2="","",
if(B2<>"",B2-D2+1
))

E列で、西暦 - 1989 + 1 の計算をして、B列の西暦が平成でいえば何年になるかを求めています。

・和暦(F列)
=IF(A2="","",
IF(E2<31,"平成"&E2,
IF(AND(E2=31,C2<=4),"平成"&E2,
IF(AND(E2=31,C2>=5),"令和"&E2-30,
IF(E2>31,"令和"&E2-30,
)))))

F列が平成か令和を判定している列です。
まずE列の「平成」の値が、31 より小さければ「平成」という文字列とE列のセルの値を結合して表示します。

次にIF関数とAND関数を使って、
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 4 以下。
が同時に成り立てば、「平成」という文字列とE列のセルの値を結合して表示するようにしてます。

細かいことをいえば、「"月" が 1 から 4 までのどれか」ですが、この場合は 1 から 4 までの4つの数字しかありえないので、「4以下」という数式にしてあります。

それから「令和」の年数を表示するために、
A. 2019-1989+1 の答えが 31 と等しい。
B. 2019年のある日付の「月」が 5 以上。
が同時に成り立つ場合は、文字列「令和」とE列のセルの値から 30 を引いたものを結合して表示するようにしています。
(ここも先と同じで、5 から 12 までの8つの数字しかないので、「5以上」という数式にしました。)

ここでE列の値から 30 を引いているのは、2018年が平成30年だからです。
30 を引いておかないと、令和1年になりません。

さらに、同じ考え方でE列の値が 31 より大きい場合も、令和2年以降を表示するために文字列「令和」と 30 を引いたものを結合しています。

・月(G列)
=if(C2="","",
if(C2<>"",C2,
))

最後に、G列ではC列の値を単純に参照しているだけです。「月」なので。

ここでC列と同じことをするのだから、先にC列で「月」を抜き出しておくのは不要な気もしましたが、「元の日付から西暦と月を抜き出している」ことがより見た目でわかりやすくなるかと思って、C列で「月」を抜き出して、それをG列で参照するようにしました。

このように、あることを「なぜそうするのか」あるいは「そうしないのか」について、具体的な理由があることもエクセルでファイルを作る際には大事です。たいそうな理由でなくてもよいです。先に挙げたように、「より理解しやすくするためにそうした(そうしなかった)」という程度で。

具体的な理由がないと、あとあとどこかを少し変えたいとなったときに、そもそも変えてよいのか、変えるには何に留意しておくべきかということがわかりません。
(だから、ファイルを引き継ぐ際には、その具体的な理由も引き継いでおく必要があります。)

長くなりましたが、処理の過程を作業列で分けてあるので、各列で何をしているのかも、全体を通して何をしているのかも理解しやすいと思います。

| | コメント (0)

macOSの「プレビュー」でPDFを編集する。

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

■macOS Mojave バージョン10.14.4

macOSに標準でインストールされている「プレビュー」だけで、PDFを編集できる。
次のようなことが可能
(編集制限がかかっていないPDFに限る。)

1.PDFにページを追加する。(複数のPDFを1つにまとめる。)
2.PDFからページを削除する。
3.PDF内でページを移動する。
4.PDFからページをコピーして、別のPDFとして保存する。(元のページは無くならない。)

1から3の操作は、command + Z で元に戻せる。
ただし、操作後に「プレビュー」を閉じると、即上書きになる。
上書きの確認はされない。

元のPDFを変えたくない場合は、先にコピーしてそれを使う。

Mac の「プレビュー」で PDF のページを追加する/削除する/移動する
リンク先にある「PDF サイドバーからデスクトップにサムネールをドラッグすることで、新しい PDF を作成することもできます。」の操作が、上記 4. にあたる。
デスクトップに限らず、自分のMac上ならどこにドラッグしてもよい。

また、「あるPDFの任意のページを、別のPDFに挿入する」といった操作も「プレビュー」で可能。
Mac の「プレビュー」で PDF を結合する

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

Googleスプレッドシート上で文字列を翻訳する関数

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

T.M さん。

Googleスプレッドシートのセルに入っている文字列を、Google Apps Script を使って翻訳するカスタム関数です。

function から この記事の一番下にある } までコピーして、Googleスプレッドシートのスクリプトエディタに貼り付けてください。

詳細はコメントに書いてあります。

------------------------------------------------

function translation(originalText, translateFrom, translateInto) {

  if ( originalText.length > 0 ) {
    return LanguageApp.translate(originalText, translateFrom, translateInto);
  } else {
    return '';
  }
 
 
/*ここからコメント
このコードを使うことで、
=translation(A2,"en","ja")
といったカスタム関数で、Googleスプレッドシートのセルに入っている文字列を指定した言語に翻訳できる。
(上記の数式は、「セル A2 に入っている "英文" を "和文" に訳する」数式)

裏でGoogle翻訳のサービスを呼び出して翻訳する。
GOOGLETRANSLATE関数を使うよりも、翻訳の精度が高い。


■コード内で使っている変数について
originalText, translateFrom, translateInto の3つは変数。
translateメソッドの引数としては、
var originalText
などといった変数の宣言は不要。

・originalText
カスタム関数の引数のうち、セル参照を受け取るための変数。
・translateFrom
翻訳したい原文が何語なのかの明示を受け取るための変数。
・translateInto
原文を何語に翻訳するかの指定を受け取るための変数。


■このコードを使う準備。
1.このコードをコピーする。
一行目の function から一番下の行の } まで。

2.翻訳したい文字列が入っているGoggleスプレッドシートを開く。

3.メニューの「ツール」から「スクリプトエディタ」を選択。

4.スクリプトエディタが別タブで開く。
あらかじめ書かれているコードは削除する。

5.このコードを貼り付ける。

6.「無題のプロジェクト」と書かれてある左上の欄に、任意の名前をつける。
全角日本語や半角の英数、アンダーバーが使える。

7.コードを保存する。
スクリプトは、保存操作をしないと保存されない。

8.いったんスクリプトエディタのタブを閉じる。
これで準備完了。

このカスタム関数が使えるのは、そのGoogleスプレッドシートでだけ。
別のスプレッドシートで使うには、同じように準備することが必要。


■カスタム関数の使い方
例:GoggleスプレッドシートのセルA2に入っている英文を和訳して、セルB2に和文を表示する。
セルB2に次の式を入力する。

=translation(A2,"en","ja")

英数半角の入力モードにして、 = に続いてカスタム関数名を記述する。ここでは、 translation 。

次に、 ( ) で囲んだ引数を記述する。引数は , (カンマ)で区切る。
1つ目の引数は英文が入っているセルをセル参照の形式で指定。ここでは、 A2 。
2つ目の引数で、セルに入っている文字列の言語を指定。 "en" は「英語」ということ。 en は文字列なので " " で必ず囲む。
3つ目の引数で、翻訳結果を何語で表示するかを指定。 "ja" は「日本語」ということ。 こちらも " " で囲む。

translateメソッドの構文は、
translate(text, sourceLanguage, targetLanguage)
になる。
sourceLanguage(ソース言語)とtargetLanguage(ターゲット言語)は、iso-639-1 コードを使用して指定する。

・language codeについて
https://cloud.google.com/translate/docs/translating-text#language-params

List of ISO 639-1 codes
https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes

・if...else文について
条件として、
originalText.length > 0
を判定している。意味は、「変数 originalText が 0 ではない。」ということ。
=translation(A2,"en","ja") の数式によって、
変数 originalText は、セル A2 に入っている値を受け取る。
A2 に文字列が入っていれば、「セル A2 は 0 ではない」ということになる。
翻訳したい文字列がセルに入っている場合にのみ、翻訳結果が表示されるようにするため。

セルに文字列が入っている場合は、条件が成立するので、 LanguageAppクラスのtranslateメソッドが実行されて、その結果をreturn文が返してくる。
(数式を入れたセルに訳文が表示される。)

セルが空の場合は、 条件が成立しないので、translateメソッドが実行されず、else以下のreturn文が実行されて空欄が返される。
(数式を入れたセルには何も表示されない。)

もし、和文を英訳したい場合は、
和文が入ったセルを、1つ目の引数で指定して、上記の2つ目と3つ目の引数を入れ替える。
セルA2に和文が入っていて、英訳したい場合の数式は、

=translation(A2,"ja","en")

になる。

ここまでコメント*/
}

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

Excel で第二月曜日は何日かを求める。

この記事は自分用の覚書です。
(Windows 10 Pro 64ビット + Excel2016 Office365 Business サブスクリプション  バージョン1810(ビルド11001.20074)

H.K さん。

ある月の第二月曜日は、何日になるかを求めるやり方です。
月曜日を対象にしていますが、何曜日であっても基本的な考え方、やり方は同じです。

日数や曜日を扱う計算については、インターネット上でたくさんの方々がいろいろな解説をしてくれています。

もっとわかりやすい説明があるかと思います。見つけたら私にも教えてくださいね。

考え方

以下、当たり前の話ですが大事なところなのであらためてふれておきます。

第二月曜日というのは、その月で二度目の月曜日です。
「ある日が二度目の月曜日」なのは、一度目の月曜日が過ぎてから初めておとずれた月曜日だからです。

一週間は七日あります。

ということは、「第一月曜日の日付(シリアル値)」がわかれば、それに 7 を足すと第二月曜日の日付を求めることができます。

たとえば、2018年11月なら、第一月曜日が 11月5日 だとわかれば、2018/11/5 + 7 の計算で、第二月曜日である 2018/11/12 を求めることができます。

そして、「ある日付が第一月曜日である」とわかるには、その月の「一日は何曜日か」がわからなければなりません。

一日が日曜日なら第一月曜日は翌日の2日だとわかります。
一日が火曜日なら第一月曜日は7日です。

「その月の一日の曜日」を求めることが最初にすることです。

「すること」を順番で並べると――

1.「その月の一日は何曜日か」を調べる。

2.1 の答えをもとに、「第一月曜日の日付(シリアル値)」を求める。

3.2 の答えである日付に 7 を足して「第二月曜日」を求める。

となります。

ちょっと話がそれますが、曜日を考えるとき、横軸上に左から右に順に並んでいるものとしてイメージしませんか? 
日・月・火・水・木・金・土――このように曜日を捉えると、今回の処理は考えづらいかと思います。

曜日が円形につながっているものとして捉えると、今回の処理は考えやすいと私には思えました。

週の始めを日曜日からとしたほうが考えやすい人もいるでしょうし、月曜日からにしたほうが考えやすい人もいるでしょう。
一週間の曜日を円形に並べて捉えれば、どちらの考え方をする人でも、今回の処理は少しでも考えやすくなると思うのですが、どうでしょうか。

Shitiyou_2

その月の一日の曜日を 1 として時計回りに数えてください。

一日が日曜なら、日曜を 1 として時計回りに数えると、第一月曜日は2日です。一日が火曜なら、火曜を 1 として 2,3,4,5,6,7 と数えて第一月曜日は7日……というふうに。

一つひとつの処理を作業列に分けて行う。

話を元に戻します。上記の3つを一つのセルでやろうとすると、数式がネストされたややこしいものになってしまいます。

仕事でつくるファイルは、できるかぎり、誰が見てもわかりやすくつくるべきです。
そういう意味で、作業列を使って処理の過程を分けるのが望ましいです。作業列で過程を分ければ全体として何をしているかを理解しやすいです。

仕事でつくるファイルでは、ネストしなければならない具体的な理由がない場合は、数式のネストは避けるのが望ましいと私は思っています。

1.「その月の一日は何曜日か」を調べる。

全体としては、次の図のようにしてみました。

01dainangetuyoubi

A列からD列で、「その月の一日は何曜日か」を調べています。

C列では、DATE 関数で一日の日付をつくっています。
もともとは年と月しか入っていない表だということでしたので、そこからそれぞれの月の一日を求めています。

また、C列のセルの書式を yyyy/m/d(aaa) にして、何曜日であるかが見た目でもわかりやすいようにしてみました。

一日の日付をつくったら、何曜日かを求めます。それがD列です。

C列はセルの書式で曜日を表示していますが、書式は計算につかえる値にはなりません。
曜日を、計算で使える値にしてやる必要があります。

WEEKDAY 関数でそれができます。書式は次のようです。

WEEKDAY(シリアル値,[週の基準])

D列で使っている WEEKDAY 関数では、引数「週の基準」を省略しているので、日付が日曜だったら 1 を返します。

月曜なら 2 を、火曜は 3 というように返してきます。土曜なら 7 が返ってきます。
一週間の曜日を表す数字なので、返ってくる値は 1 から 7 までです。

・引数「週の基準」を省略した際に WEEKDAY 関数が返す値
日曜日→1
月曜日→2
火曜日→3
水曜日→4
木曜日→5
金曜日→6
土曜日→7

2.「第一月曜日の日付(シリアル値)」を求める。

一日が何曜日かを表す数字ができたので、それをもとに第一月曜日が何日かを求めます。
E列とF列でそれをやっています。

なお、図では、少しでもわかりやすくなるかと思って、E列にはセルの書式設定で「日」を表示するようにしてあります。

ここで、冒頭の円の図をもう一度見てみてください。
一日が何曜日かによって変わってくる「第一月曜日が何日か」の確認です。

一日が日曜だったら、時計回りに 1,2 と数えて第一月曜日は2日です。
一日が月曜なら第一月曜日は当日です。
一日が火曜なら第一月曜日は7日になります。
水曜なら6日です。
木曜なら5日です。
金曜なら4日です。
土曜なら3日です。

――というように、一日の曜日によって、第一月曜日が何日なのかが変わってきます。

どんな数式をつくればよいかというと、

WEEKDAY 関数が返す値が、

・ 1 (一日が日曜)なら 2 を返す。
・ 2 (一日が月曜)なら 1 を返す。
・ 3 (一日が火曜)なら 7 を返す。
・ 4 (一日が水曜)なら 6 を返す。
・ 5 (一日が木曜)なら 5 を返す。
・ 6 (一日が金曜)なら 4 を返す。
・ 7 (一日が土曜)なら 3 を返す。

ができればいいわけです。
返ってきた値が、第一月曜日は何日かを示しています。

02dainangetuyoubi

Office365サブスクリプションの Excel2016 には、こういうときに最適な関数が実装されています。 SWITCH 関数です。

円の次の図では、セル E2 にはこういう数式が入っています。

=SWITCH(D2,1,2,2,1,3,7,4,6,5,5,6,4,7,3)

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

SWITCH(式, value1, result1, [既定値または value2, result2],…[既定値または value3, result3])

SWITCH 関数は、(式と呼ばれる) 1 つの値に対して値の一覧を評価し、最初に一致する値に対応する結果を返します。
(出典:Excel「ヘルプ」)

上記の式でいうと、( )のなかで最初は D2,1,2, になっています。
D2 の値が 1 の場合は、2 を返すという指定です。

その月の一日が日曜(D2 が 1)なら、第一月曜日は2日ということです。

その次を見ると、D2,1,2,2,1, になっていますから、一日が月曜(D2 が 2)なら、返す値は 1 です。当日が月曜なので。

その次は、3,7 が続きます。一日が火曜(D2 が 3)なら 7 を返します。第一月曜日は7日です。

そうやって残りも、4 なら 6、5 なら 5 、6 なら 4 、7 なら 3 を返すというように指定しています。

実際には、E列のセルは次のように入力しています。
2行目以降ではスペースを入れて頭をそろえています。

=SWITCH(D2,1,2,
                   2,1,
                   3,7,
                   4,6,
                   5,5,
                   6,4,
                   7,3)

こうしておけば、「(D2が)1 なら 2 を返す、2 なら 1 を返す、3 なら 7 を返す……」といった組み合わせが理解しやすいですよね。

03dainangetuyoubi_switch

なお、Office365サブスクリプションではない Excel では、CHOOSE 関数で対応できます。

CHOOSE 関数の数式は、

=CHOOSE(D2,2,1,7,6,5,4,3)

となります。

D2 に入るのは、1 から 7 です。
2,1,7,6,5,4,3 の部分は、D2 の値によって返す順番に並べています。

D2 が 1 なら、2 を返します。(並びの一番目を返す)
D2 が 2 なら、1 を返します。(並びの二番目を返す)
D2 が 3 なら、7 を返します。(並びの三番目を返す)……

書き方が違うだけで、 SWITCH 関数も CHOOSE 関数も結果は同じです。

IF 関数でも可能ですがお勧めしません。その理由は想像すればわかりますよね。
「IF 関数ならこうなります」という例を作ろうとしたくもありません。かなり面倒です。

=IF(D2=1,2,IF(D2=2,1,""))

2つだけやってみました。2つ目のIFを入力したときにもうやりたくなくなりました。

E列で第一月曜日は何日なのかがわかったので、それをもとにF列では、 DATE 関数で第一月曜日の日付をつくっています。

3.第一月曜日の日付に 7 を足して「第二月曜日」を求める。

G列が第二月曜日の日付です。
F列の第一月曜日の日付に 7 を足しています。

F列の時点で、 =DATE(A2,B2,E2)+7 というように 7 を足せば、F列で第二月曜日を求められますが、ここでは、「第一月曜日の日付(シリアル値)がわかれば、それに 7 を足すことで第二月曜日を求めることができる」という思考の過程がよりわかりやすいように分けてみました。

これで作業終了です。
こうやって作業列で処理を分けると全体で何をやっているかが理解しやすくないですか。

仕事でつくるファイルでは、作業列を積極的に使ってください。

別の曜日について

なお、別の曜日でも考え方は同じです。
違ってくるのは、この例でいうなら SWITCH 関数の引数です。

また、第三○曜や第四○曜日を求めるには、第一月曜日の日付に 7 を足して第二月曜日を求めたように、処理を分けて 7 を足していけばいいです。

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

Excel for Mac でも VBA で日本語が使える。

この記事は自分用の覚書です。
・Windows 10 Pro 64ビット バージョン:1803 OSビルド:17134.345
Excel2016 Office365 Business サブスクリプション  バージョン1809(ビルド10827.20150)
・MacBook Pro (High Sierra 10.13.6)
Excel for Mac 16.18(181014) Office365 Business サブスクリプション

N.T さん。

Windows 側でつくったマクロですが、H.K さんに手伝ってもらって Excel for Mac で正常に動くことを確認できました。

マクロ名や変数名が日本語でも問題なく動きました。
少なくとも、試した環境においては日本語を含むという理由で正常に動かないことはないといえます。
標準モジュール上での日本語の入力も問題なくできましたよ。

試した流れは次のようです。

1. Windows 版 Excel のほうでコードを書く。

2. 拡張子xlsm で保存してからファイルを Mac 側に渡す。

3. Excel for Mac でファイルを開いてもらってマクロを実行

テストに使ったプロシージャは以下のようです。
ちょっとだけひねくれた書き方をしてみました。この程度で不具合が起きるなんてありえんだろうと思いましたが、テストなのであまりしない書き方を。

このプロシージャなら、普通なら For の行を For i = 2 To 11 にして、 Cells プロパティでは i に 1 は足さないですよね。

■試したプロシージャ

Sub サンプル()
Dim i As Long
Dim 連番用足し込み As Long

For i = 1 To 10
    連番用足し込み = 連番用足し込み + 1
    Cells(i + 1, "A").Value = 連番用足し込み
Next i
'
'セル A2 から A11 に、1 から 10 までの連番を入力する。
'
End Sub

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

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

この記事は自分用の覚書です。
(Windows 10 Pro 64ビット + Excel2016 Office365 Business サブスクリプション  バージョン1809(ビルド10827.20138)

H.N さん

Excel で、表全体を対象にしてどこかにあるかもしれない重複する値を見つける―― VBA でコードを書いたり、関数で数式をつくったりしないで――のやり方です。

難しく考える必要はありません。
「表のどこかのセルが同じ値であることがわかる」というのでよければ、条件付き書式を使って「同じ値ならセルを塗りつぶす」などとするのが一番簡単―― Excel がもっている機能だけで、いくつかの手順をたどれば求める結果が得られる――でしょう。
(もっと手軽なやり方がわかったら私にも教えてくださいね。)

条件付き書式では、条件に「重複する値」あるいは「一意(重複しない)の値」を選ぶことができます。
次の図では A2 の「菊池寛」は C2 と C9 にもあるので、それらが色付けされています。

Hyouzentai_tyouhukusuruatai00

1.色付けしたい範囲を選択。
上図では、A2 から C10。

2.「ホーム」タブの「条件付き書式」の「セルの強調表示ルール」から「重複する値」を選ぶ。

Hyouzentai_tyouhukusuruatai01

3.「重複する値」のダイアログボックスが表示されて、表の中で重複している値のセルに、指定している色付けがされる。
「値」の欄で「一意」を選ぶと、重複していない値を条件にしたことになる。

Hyouzentai_tyouhukusuruatai02

4.そのままでよければ、これで完了。
「書式」の欄で、どのような色付け方をするかを選択できる。
標準で入っている色付け方以外の指定をする場合は、「ユーザー定義の書式」を選択。

Hyouzentai_tyouhukusuruatai03

5.OK をクリックして画面を閉じる。

これで、表のなかで重複している値を見分けることができます。

書式を意識しなくてよいので、上記の手順が楽だと私は感じています。

「組み込みの書式以外でなければならない具体的な理由」なんてそうはないですし。
自分の好みの色付け方でなくても、目的が達成できているならそこはもうそれでよしと私はしています。

重複している値の一覧を作る。

重複している値の一覧についても「一番簡単」に済ませるやり方を考えました。
今回の場合は、行や列が何十、何百あるわけではないですし、手作業で抜き出してもそんなに負担はないでしょうから。

1.表内のどれかのセルをクリックしてからオートフィルターを有効にする。

2.A列で「色フィルター」を実行して、セルの色かフォントの色を条件に絞り込む。

Hyouzentai_tyouhukusuruatai04

3.絞り込んだA列の値をコピーして、別の場所へ貼り付ける。
下図では E12 を選択して貼り付けている。

Hyouzentai_tyouhukusuruatai05

4.A列のフィルターで「すべて選択」を選んで「色フィルター」を解除する。

5.B列で「色フィルター」を実行。

6.絞り込んだ結果をコピーして、先に貼り付けた値の下へ貼り付ける。
図では、絞り込んだB列の値を、E17 から貼り付けている。

Hyouzentai_tyouhukusuruatai06

7.B列で「すべて選択」を選んで「色フィルター」を解除する。

8.残りの列すべてで 5.から 7.と同様の操作を行う。

9.貼り付けた範囲を選択して、「データ」タブの「重複の削除」をクリック。
図では E12 から下を選択。

Hyouzentai_tyouhukusuruatai07

10.「重複の削除」画面が表示されたら OK をクリック。
この場合は、E12 から選択したので、「先頭行をデータの見出しとして使用する」のチェックは入れない。

Hyouzentai_tyouhukusuruatai08

11.「重複する〇〇個の値が見つかり、削除されました。一意の値が〇個残っています。」の表示が出たら OK をクリック。

Hyouzentai_tyouhukusuruatai09

12.重複している値の一覧ができあがる。

Hyouzentai_tyouhukusuruatai10

手順3.で貼り付ける場所はどこでもいいです。たとえば、新しいシートを挿入して貼り付けるとかでも。
この場合は、例なので全体が見えたほうがよいかなと思って、同じシートを使っています。

なお、表の行単位で全く同じ値がある場合も、前述の「重複の削除」で重複している行を削除して1つの行だけ残すことができます。

Hyouzentai_tyouhukusuruatai14

A2 から C10 を選択して「重複の削除」を実行します。
残るのは選択した範囲内で一番上にある行で、下のほうにある行が削除されます。

上の表なら、2行目が残って5行目が削除されます。

Hyouzentai_tyouhukusuruatai15

また、ちょっとしたことですが、表の近くに別のセル範囲を作るときは、先にある表と接しないように作るのがコツです。
ここでは、それでもなるべく近くにしたかったので、表から1列空けて1行下から始まるようにしました。

Hyouzentai_tyouhukusuruatai11

というのは、ショートカットキーを使いやすくしておくためです。
たとえば、表全体を選択する際によく使うショートカットキー( Ctrl + Shift + : )では、セル範囲が接していると、その範囲まで含んで選択されてしまいます。

Hyouzentai_tyouhukusuruatai12

また、1行下から始めておかないと、表のオートフィルターの条件によっては、1行目のセルが非表示になってしまいます。

Hyouzentai_tyouhukusuruatai13

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

Excel2016 で、あるセルの内容によって別のセルに色を付ける。

この記事は自分用の覚書です。
(Windows 10 Pro 64ビット + Excel2016 Office365 Business サブスクリプション  バージョン1808(ビルド10730.20102)

H.M さん。

Excel2016 で、あるセルの内容によって別のセルに色を付けるやり方です。

ココログのアクセス解析を見ると、この類の情報を求めてうちのブログがぽろぽろと見られています。

「条件付き書式 別のセル」などのキーワードの組み合わせで。

そう思いながら、あらためて自分のブログを見返してみると、Excel2016 で「あるセルの内容によって別のセルに色を付ける」については書いてないのでまとめてみました。

といっても、Excel2016 になっても特別なことはなくて、旧バージョンと同じなんですけどね。条件付き書式というものです。

↓こういうことをしようというわけで……。

00joukentukisyosiki01

C列が「合格」ならA列のセルに色を付けています。

条件付き書式を設定する。

画像の場合は次のような手順になります。

1. 条件付き書式を設定したい範囲――図では A2 から A5 まで――を選択
2. 「ホーム」タブの「条件付き書式」から「新しいルール」を選択
3. 「ルールの種類を選択してください」」の欄で、「数式を使用して、書式設定するセルを決定」を選択
4.「次の数式を満たす場合に値を書式設定」の欄に以下の数式を入力
= $C2="合格"

01joukentukisyosiki_susiki

5. 「書式」ボタンをクリック
6. 「セルの書式設定」の画面が表示されるので、「塗りつぶし」タブをクリック
7. 任意の背景色を選択して、OK をクリック

これで、C列の値が「合格」の場合、A列のセルが選択した色で塗りつぶされます。

なお、行を塗りつぶすには、手順1. で A2 から C5 までを選択しておきます。
そうすると、C列のセルが「合格」のときには、その行に色が付きます。

02joukentukisyosiki_gyounuritubusi

これまでと同じことを書いても面白くないですし、せっかくなのでちょっと違うことも書いてみます。

以下、いろいろと――。

条件付き書式を設定する前に明らかにすること。

4つあります。

  1. 目的
    何のためにつくるファイルなのか。
  2. 条件付き書式を適用する場所
    どのセル、どの範囲に適用するのか。
  3. 条件は何か。
    セルの値が50以上である――など。
  4. どんな書式にするのか。
    セルを赤く塗りつぶす――とか。

「そんなの当たり前でしょう」という気がしますよね。私もそう思います。でも、これがなかなか……。

私が経験してきたなかでは、特に1番の「目的」がはっきりしていないことってあるんですよね。

たとえば、「セルの値が5未満だったら、その値を赤色の太字にしたい」って相談を受けたことがあります。
何に使うのかを尋ねたら、そうやって、5未満の値を目立たせた資料を作って、社内のミーティングのときに見やすくしたいという。

そういうことなら、文字を赤くするよりはセルを塗りつぶすか、網かけしたほうがよいと話したらそうすることになりました。

赤い文字は画面では目立ちますが、モノクロ印刷したら区別がつきません。
セルに色を付けておけば、モノクロ印刷のときでもそこがわかります。

「ミーティングのときに見やすくしたい」と思ってはいても、それが目的としてしっかり意識されていなかったんですね。
目的がはっきりしたら、設定するべき書式が変更になったわけです。

何のためにつくるファイルなのかをはっきりさせておけば、そのために何をどうすればよいのかを考えやすくなります。

行は相対参照にする。

上の図の場合は――ということです。

手順1.で セル A2 から A5 までを選択していますから、行のほうは相対参照にしておかないと、行の指定がずれていきません。

= $C$2="合格"

にすると、A2 から A5 までのセルの塗りつぶしを決める条件は、セル C2 の値のみにしたことになります。
もし、C2 が「合格」だったら、C列の他のセルに関係なく、A2 から A5 の全部が色付けされてしまいます。

列と行で指定をずらしていかなければいけないほうは、相対参照にする――です。

絶対参照と相対参照の切り替え

F4 キーを使います。

「次の数式を満たす場合に値を書式設定」の欄を入力する際に、欄内をクリックしたあとで、セル C2 をクリックすると、C2 が絶対参照になります。

=$C$2

と入ります。

03joukentukisyosiki_zettaisansyou

あるいは、条件のもとになるセルや範囲が、別のシートにあったりすると、手入力するよりはそのシートを開いてそこを選択したほうが手っ取り早いですよね。その場合もセル参照が絶対参照になります。

セル参照が必要な参照の状態になるまで F4 キーを押してください。
押すたびに、=$C$2 → =C$2 → =$C2 → =C2 → =$C$2 とセル参照が変わっていきます。

基本的なことですが、絶対参照を意味する「$」は、「列を参照する英字」と「行を参照する数字」の左側に添えます。
(列か行のどちらかを絶対参照にする場合は、その左側にだけ。)

既定では、Excel は 「A1 参照スタイル」と呼ばれるセル参照を使います。
(出典:マイクロソフトのサイトにある「Excel の数式の概要-Excel の数式にセル参照を使用する」)

セル参照の並びは、C2 のように「列を参照する英字」と「行を参照する数字」になりますから、列も行も絶対参照にする場合は、 $C と $2 で $C$2 です。
列 C と 行 2 が交差するセルを絶対参照する指定です。

「$ で挟んでいるから――」と考える人がいました。
$C$ というイメージをしたのでしょうね。そう考えては、セル参照はどういうものかがわからなくなります。

「次の数式を満たす場合に値を書式設定」の欄を編集するには。

F2 キーを使います。

冒頭の手順4. で「次の数式を満たす場合に値を書式設定」の欄内にカーソルがあるときに矢印キーを押してしまうと、わけのわからない表示になる場合があります。

たとえば、=$C$2="合格" と入力してしまってから、$2 になっていることを気付いて、2 の左側の $ だけを消したくて矢印キーでカーソルを $ の前に動かそうとすると、=$C$2="合格"+$A$2 となったり……。

04joukentukisyosiki_sansyou

エクセルのステータスバーを見てください。シートより左下のスミのあたりです。 「参照」とか「入力」となっているはずです。(通常では「準備完了」)

05joukentukisyosiki_nyuryoku

ここが「入力」となっているときに、「次の数式を満たす場合に値を書式設定」の欄のなかでカーソルを動かそうとすると、さきほどのようなわけのわからない表示になります。

この欄を編集したいときは、カーソルが欄内にある状態で F2 キーを押します。

ステータスバーに「編集」が表示されるまで F2 キーを押していってください。
「編集」が表示されれば、左右の矢印キーを使って、欄内で自由にカーソルを動かすことができます。
これは「編集モード」と呼ばれる状態です。

06joukentukisyosiki_hensyu

話は少し変わりますが、編集モード( F2 キーでステイタスバーに「編集」を表示)は、条件付き書式以外にも役に立ちます。

たとえば、「データの入力規則」を使って、セルに入れる値をリスト表示させているとき。
リスト表示させる値を「元の値」の欄に直に入力していて、リストの値を編集しようとして欄をクリック後に矢印キーを押すと、ここでもわけのわからないセル参照が入ってしまいます。

07f2_list_hensyuu

そういうときも、「元の値」の欄をクリックしてから、F2 キーを押して、編集モードにすれば、リストの元の値を自由に書き換えることができます。

08f2_list_hensyuumode

あるいは、セルやセル範囲に名前を付ける際に、参照範囲を編集するのにも使えます。

09f2_namae_hensyuumode

ほかにも、セルの数式を書き換える際にも F2 キーを押すとセルが編集モードになります。
(セルをダブルクリックするのと同じです。)

Windows 上でファイル名やフォルダ名を変えたいときにも使えますよ。

話を元へ戻します。

条件付き書式の数式を編集する際には、「次の数式を満たす場合に値を書式設定」の欄をクリックしてカーソルを置いたら、F2 キーを押してステータスバーに「編集」と表示されるようにする――これを忘れなければ、変なセル参照が表示されて困惑することはなくなります。

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

より以前の記事一覧