エクセルでセル内の一部だけ取り消し線を入れたい

セル内の一部の文字だけボールドやイタリックにしたり赤く表示させるなんてのは初級レベルというか、誰でも知ってることですね。では取り消し線のやり方知ってますか?

 

リボンにそんなアイコンないよね。CTRL+Kやってもダメ。ああ困った。リボンに書式設定アイコン追加すりゃいいかと思ったけど、あんな複雑なダイアログは見たことないっていうくらい分かりにくいダイアログ出てきた。それでなくても反リボン派なのにマイクロソフトのやつらは何考えてるんでしょうか。

 

これとは別にクイックアクセスツールバーってのがあって、Classic Menuを入れたときに勝手に追加されてたようです。これに書式設定アイコンを追加してみました。そしたら、無事取り消し線を入れることができました。

 

でもね。よ~~~く見たら右クリックのコンテキストメニューに書式設定ってあるじゃんwww こっちの方がスマートだったよ。

 

 

 

 

 

 

 

 

 

翌月の日付を簡単に表示させるには

こんなシートを作ってる人たくさんいると思いますが、毎回手打ちですか? 月に1回入れればいいから我慢しますか? 嫌ですよね。

2021年1月
2月
3月
4月
5月
6月
7月
8月
9月

 

 

 

 

 

 

 

 

 

 

 

 

EDATE関数で簡単に月+1

セルA1に2021/1/1が入っているとしたら、セルA2に=EDATE(A1, 1)としてフィルダウンするだけです。あとは、セル書式設定で m"月"にすれば上と同じシートが完成です。上のようなシートならこれで問題ありません。

 

月初はいいけど月末だと日付がずれてくる

今度はセルA1に2021/1/31にしてフィルダウンしてみます。うるう年も含めて月末になってて欲しいですよね。でも、結果はこんな感じ。長いので途中はスキップです。厳密に月末じゃないけど、表示上は月だけの表示だからいいことにします?

2021/01/31
2021/02/28
2021/03/28
2021/04/28
2021/05/28
2021/06/28
2021/07/28
2021/08/28
2021/09/28
2021/10/28
2021/11/28
2021/12/28
2026/02/28
2026/03/28
2026/04/28
2026/05/28
2026/06/28
2026/07/28
2026/08/28
2026/09/28
2026/10/28
2026/11/28
2026/12/28

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

EOMONTH関数を使って月末表示

セルA2以下に=EOMONTH( A1, 1 )とするだけです。2020年2月を見てください。うるう年も対応していることが分かります。

2020/01/01
2020/02/29
2020/03/31
2020/04/30
2020/05/31
2020/06/30
2020/07/31
2020/08/31
2020/09/30
2020/10/31
2020/11/30
2020/12/31
2021/01/31
2021/02/28
2021/03/31
2021/04/30
2021/05/31
2021/06/30

 

 

セル書式の"aaa"は使用上の注意をよくお読みください

問題1:時系列データから土日を除外して平均を出したい

まず、このスプレッドシートの平均Aと平均Bを見て欲しい。同じ数字が表示されている。しかし、平均Aは土日を除外したセルが対象で、平均Bはすべてのセルが対象となっている。

 

フォーミュラは以下のとおり。なぜAVERAGEIFSは期待通りの結果を返さないのか分かりますか? ちょっと考えてみてください。

 

平均A: =AVERAGEIFS(C13:C999, $A13:$A999, "<>土", $A13:$A999, "<>日")

平均B: =AVERAGE(C13:C999)

 

 

f:id:gungnir46:20210624103948p:plain

 

 

回答

A列の曜日は、実はB列を参照しており、セル書式で "aaa" としているので 表示フォーマットは曜日だが、あくまでもはシリアル値となっている。AVERAGEIFSの条件式は "<>土"となっているので問題なさそうだが、あくまでも値を見に行くのでヒットするはずがない。よって、両者ともに同じ数字を返してしまう。

 

解決方法:

 A列を泣く泣く月~日の文字にするか、CHOOSE関数を使ってセルの値を文字にするしかない。なぜなら、AFERAGEIFの条件式には条件付き書式のような形式では記述できないからです。

=CHOOSE(WEEKDAY(B13), "日", "月", "火", "水", "木","金", "土")

 

 

 

問題2: 条件付き書式で土日を赤く表示

ルールの種類を「数式を使用して、書式設定するセルを決定」にして、指定日付セルが 土日の場合は赤くしたい場合もセル書式の "aaa"は注意が必要です。

 

単純に次のように書いてしまうと、赤く表示されません。もちろん、①文字で月~日、または②CHOOSE関数で曜日列を設定すればこの書き方で問題ありません。

=OR(A18="土", A18 = "日")

 

じゃあ、どうすればいいかというと・・・TEXT関数をかませてやればいいわけです。

=OR(TEXT(A18, "aaa")="土", TEXT(A18, "aaa") = "日")

 

 

応用編 

週末だけではなく国民祝日も赤くしたい場合はどうすればいいでしょうか? 簡単な方法はVBAで関数を書いて、その中で国民祝日もチェックすればいいでしょう。

=IsHoliday(A18)

 

当然ですが、国民祝日を管理するシートも必要になります。 

 

 

Public Function IsHoliday(ParamArray pParameter() As Variant) As Boolean

    Dim iDay    As Long
    Dim sDate   As String
 
    IsHoliday = True
    sDate = pParameter(0)
    iDay = Weekday(sDate)
    If iDay = vbSunday Or iDay = vbSaturday Then Exit Function

    Dim vMatch As Variant

    On Error GoTo Unmatch
    vMatch = Application.WorksheetFunction.Match(CLng(CDate(sDate)), wksCalendar.Range("K:K"), 0)
    IsHoliday = (vMatch > 0)
    Exit Function

Unmatch:
    IsHoliday = False

End Function

 

 

 

 

 

 

 

余談:
サンプルに使ったシートは、実はあるパチンコホールでの時間帯別大当たり回数を集計したものです。14:30から16:00までの時間帯が一番出しており、夜の部はそれほどでもないということが分かります。条件書式で平均の-10%、-5%、+5%、+10%で背景色を設定すればどの曜日で回収しているのか、7が付く日は出ているのかどうなのか、新台導入した週はどうなっているのか、なんてことが分かります。

コメントのディフォルト フォントサイズを大きくしたい

エクセルのコメントを追加するたびにイライラしますよね? というのもフォントサイズが小さくて読みにくいからです。オプション画面には設定できる項目がありません。なんと、エクセルはWindowsの個人設定フォント情報を使っています。そして、2021/6/10 現在のパッチでは設定画面からはこのフォントを変更することができなくなっています。

 

 

 

1.レジストリエディターを開く

WINDOWS+R で regeditと入力してENTERキー

すると、こんな画面が出てくるので \HKEY_CURRENT_USER\Control Panel\Desktop\WindowMetricsを開きます。

f:id:gungnir46:20210610024438p:plain


2.フォントサイズを変える

StatusFontをダブルクリックすると、このようなダイアログが出てきます。

f:id:gungnir46:20210610024636p:plain

 

3.先頭バイトを以下の表に参考にして変更する

上のサンプル画面はすでに変更済みで11ポイントにしています。

9ポイント f4
10ポイント f3
11ポイント f1
12ポイント f0
13ポイント ef
14ポイント ed

 

 

フォントサイズだけではなくフォント名も変えたい場合は、16進で変更するのは大変なのでMeiryo UIも大っきらい!! というツールで変更できるらしいです。自己責任でどうぞ。

 

ところで、SHIFT+F2でコメントをさくっと編集できるようになるって知ってました? 

 

表示されているセルだけで平均が計算できる?

AVERAGE関数は非表示のセルも計算対象なのは知ってますよね。SUM関数も同じく非表示セルも計算対象です。だから、SUBTOTAL使えばいいんでしょ? そんなの100年前から知ってたよ。だから?  っていう人。はい、残念でした。

 

SUBTOTALはオートフィルターにしか対応してません

列幅=0または行の高さ=0にしたセルには対応してません。

 

f:id:gungnir46:20210606172215p:plain


これをAVERAGE, SUBTOTAL, AVERAGEIFで計算させてみると。

f:id:gungnir46:20210606175608p:plain

  

当然ですが、オートフィルターを使ってAだけを表示させると、SUBTOTALの結果は1,217となります。Bの行を書式>行の高さ=0にして非表示にするとダメなわけです。

 

参考までにAGGREGATE関数を使うと、19行目を行の高さ=0で非表示にしてもAVERAGEIFと同じ結果を計算してくれます。

 

ケース2:列の非表示

じゃあ、列に設定したタイトルで同じものだけを対象にして平均を取りたいときはどうすればいいのか。次のサンプルをみてください。列のタイトルが回収となっているセルだけでそれぞれの台の平均を取るにはどうしたらいいでしょうか? ちなみに数字は適当ですw オートフィルターは使えないので、書式>列の非表示で"投資"の列を隠します。

f:id:gungnir46:20210606180041p:plain

  

AGGREGATEを使えばよさそうなんですが、非表示のにしか対応していないので今回のように"投資"の列を非表示にした場合はAVERAGEやSUBTOTALと同じ結果を返してしまいます。そこで今回のケースでは、セルタイトルが"回収"だけの列で計算すればいいというポイントに着目して、AVERAGEIFSを使います。

 

AVERAGEIFS(平均対象範囲、条件範囲1,条件1、[条件範囲2],[条件2]、.....)

平均対象範囲は横1行、条件範囲1にはタイトル行、条件1は"回収"とすればいいわけです。

=AVERAGEIFS(J14:P14, J$13:P$13, "回収")

 

結論:

行に対する非表示対応ならSUBTOTALよりもAGGREGATEを使う方がよい。

シート構成にもよるが列の非表示に関してはAVERAGEIFSをうまく使うしかない。

 

 

 

 

 

 

 

曜日を表示する

書式設定で"YYYY/MM/DD (aaa)"にすると曜日も表示できるという記事があった。へぇ、それは知らなかったな。昔のエクセル4の時代はユーザー設定書式にたくさんサンプルがあったが、今のバージョンには "aaa"なんてサンプルがないから知らない人はたくさんいると思う。

 

Weekday関数とChoose関数を組み合わせて曜日を表示させてたのは無駄な努力だったわけねwww なるほど

 

応用編: 土日の日付を赤く表示する

条件付き書式で次のように記述すればよい。I6の部分は自分のファイルに合わせて変えること。

=OR(TEXT(I6, "aaa") = "土", TEXT(I6, "aaa") = "日")

 

この記事を読む人の中には最前線の開発プロジェクトに携わっている人もいると思うが、技術的な知識やスキルがあればあるほど、難易度の高いソリューションに飛びつきがちで開発コストが増大してしまうというピットフォールが待っているから注意した方がよい。

 

メモ:Android版のYoutubeがインストールで失敗する

Youtubeのアンインストだめ絶対

Androidスマホには最初からYoutubeが入っていると思いますが、これをアンインストールすると再インストールできないという落とし穴が待っています。問題なく再インストールできるデバイスもあるでしょうが、アンインストールはしない方がいいです。私がアンインストールした理由は、動画再生した際に関連動画が出てこなくなったからでした。もしかしたらバグなのかと思ってアンインストールしたらとんでもない地雷を踏むことになってしまったわけです。

 

Chromeがあるさ

PCでYoutubeを見るときはChromeを使ってますよね? スマホでも別に専用アプリがなくてもChrome(m.youtube.com)で見ればいいんです。画面が2個でスワイプとかできないのがつらいですけどね。でも見ることはできます。

 

対処法

1.スマホを再起動してみる。

2.ストアアプリのキャッシュをクリアしてみる。

3.アップデートをアンインストールする。設定アプリ>アプリ>ストア

 

私の場合は3で解決しました。ストアアプリ自体もバージョンアップしているわけですが、どうやら新しくなりすぎるとインストーラーに不具合が発生するようです。これはもう欠陥商品としか言いようがないのですが、古いスマホを使っているのが悪いんでしょうね。買い替えろということか・・・