令和元年に対応する

年月日をエクセルで表示することが多くて、Windowsのバージョンアップで解決されるまでは、関数でも使って、「令和」を表示してきた。

他所で使用されているエクセルの行事カレンダーを手直ししてほしいと依頼され、調べていたら、もうすでにWindowsの方はバージョンアップされていた。他所のWindowsを勝手にバージョンアップはできないのでとりあえずは、表示設定を変更することで終わりにした。

そこで自分のパソコンもバージョンアップをすることにした。

Windowsをバージョンアップすると

と、2019/7/5と入力して、

セルの書式設定>表示形式>日付>カレンダーの種類>和暦>平成24年3月14日 と選んでいくと、上のように、「令和1年7月5日」と表示できる。

令和1年は通常「令和元年」と表示するようだから、そのように設定する日強がある。いろいろなサイトにこの設定については記述されているから、それを利用する。

上のように、ユーザー設定>種類の欄に、以下のように記述する。

[<43586]ggge”年”m”月”d”日”;[<43831]”令和元年”m”月”d”日”;ggge”年”m”月”d”日”

そうすると、

と、「令和元年」の表示ができるようになる。同じ表示形式を使って、令和2年は

とそのまま表示できる。

テキストエディタ秀丸での変換・削除

今まで、文字を別の文字に変換するなどは、検索・置換で行ってきた。

今回、18000件を超える大量のエクセルデータの列から、半角カタカナを全角にするに当たり、エクセルのVBAでなんとかできるかな?くらいに考えていた。ところがその列には半角カタカナにくっついて、数字と漢字まである。なかなか手ごわい。

いろいろググっていて、秀丸で不要な文字を削除、半角カナを全角に変換するというのがあり、今まで余り考えたことがなかった。今回その方法で行ってみた。何度かやり直さなくてはならないことも生じたので、注意することも含めて確認の意味でここにまとめておく。

変換したい列をコピーして、秀丸に貼り付ける。その後は置換という形て削除したい文字を空白文字に置換する。

置換で行える検索文字の設定について

アルファベットの削除なら[a-z]
数字の削除なら[0-9]
「・」「・」「,」は普通に「・」「・」「,」を検索して削除で良いが、
ピリオドの削除は注意
「.」だけを入力して置換を空白にして置換すると、行全体の文字が削除される。ピリオドだけを削除するときは「\.」(「\」は円記号ー以下同じ、にピリオド「.」)と検索文字に入力して空白文字に置換
()を削除するには「\(\)」と検索文字に入力して空白に置換
http://www.shuiren.org/chuden/teach/hidemaru/seiki/index-j.html#chuui
すべての漢字を削除 [亜-黑]
[ぁ-ん]    全角ひらがなを検索
[ァ-ヶ]    全角カタカナを検索

という設定ができるということで、今回はじめて利用した。

また半角カナを全角に変換するのは

文字を選択して編集>変換>カタカナのみ全角、と選んでクリックすれば半角カタカナが一気に全角に変換される。

秀丸のメニュースクリーンショットは、Win10に標準装備(アクセサリ内)のSnipping Toolを利用して撮る。普通にはCtrl(あるいはAlt)+PrtSCでスクリーンショットを撮影するが、メニューの場合はCtrl(あるいはAlt)を押した時点でメニューが消えてしまって写せなかったのが、Snipping Toolで撮ることができた。

 

エクセルの関数多用を簡略化して動くように

エクセル関数の票COUNTIF,ROW,OFFSET,MATCHが一つの関数として入ったものを作成していたが、動かなくなり、作業列を作ることでエクセルが楽に動くようになった。ネットを検索してみるとやはり複数の関数が入り込むと計算ができなくなり表示できないということが示されていた。そのことを覚えとして書いておく。上の表はおおよそこんな体裁ということでデータは変更してある。

エクセルに30行☓10列の表を作成。

その票は3つの分類ができるようなもので、表の右に分類ごとにその詳細を書き出すために、ネットを検索しながら次のように設定した。「型」は分類ができる範囲(D2:D30)の範囲名とする。AF1には分類名をいれている。E~P列に項目の詳細が入力されているとする。

AF3=IF(COUNTIF(型,$AF$1)<ROW(C1),””,OFFSET(D2,MATCH($AF$1,肥料型,0),1))

AG3=IF(COUNTIF(型,$AF$1)<ROW(D1),””,OFFSET(E2,MATCH($AF$1,肥料型,0),1))

AF4=IF(COUNTIF(型,$AF$1)<ROW(C2),””,OFFSET(D3,MATCH($AF$1,肥料型,0),1))

AG$=IF(COUNTIF(型,$AF$1)<ROW(D2),””,OFFSET(E3,MATCH($AF$1,肥料型,0),1))

10行くらいの票のときには確かに機能していたのだが、行数が増えると分類ができていないことに気づく。再計算をしても変わらない。

次に、同様の事ができるということで作業列を新たに設ける。A2~C2に分類名を入力D列にその分類名があればカウントするということで下記の関数をドラッグする。

=IF($D3=A$2,COUNTIF($D$3:$D3,A$2),””)

a型分類欄には

S3=IF(MAX(a型数)<ROW($A1),””,INDEX(E$3:E$100,MATCH(ROW($A1),a型数,0)))

T3=IF(MAX(a型数)<ROW($A1),””,INDEX(F$3:F$100,MATCH(ROW($A1),a型数,0)))

S4=IF(MAX(a型数)<ROW($A2),””,INDEX(E$3:E$100,MATCH(ROW($A2),a型数,0)))

T4=IF(MAX(a型数)<ROW($A2),””,INDEX(F$3:F$100,MATCH(ROW($A2),a型数,0)))

同じような計算式でも個数を分割して作業列を作ることだけで随分とエクセルソフトにとっての計算が楽になるようだ。

セル内の文字を別のセルに表示する

エクセルで作業していると、セルの中にどのくらいの文字が入っているのかわからない、あるいは多くの文字が入力されているが見えないということがある。

最近そんな経験をしてなんとかならないものかとネットを検索していたら、あった。

セルをクリックする(アクティブなセル)をある場所に表示させるというものだ。ここではA1のセルを幾つかのセルと結合して長く表示できるようにしておく。

文字を縮小して全体を表示する

A5のセルは全体を縮小して表示するにしているので、長い文字列は表示できない。直接入力したものなら数式バーに表示もできるが、関数で表示している場合は値ではなく関数の表示しかできない。

文字が隠れる

また上の例ではA4のセルの値がB4の値があるために、隠れている。一見「お」までしか入力されていないようだが、数式バーでは多くの文字が見える。これも入力されている文字なら問題ないが、関数では勘違いしてしまう。

OKWEBアクティブセルを参照したいを参考に

シート名を右クリック

シート名を右クリックして

コード記入

コード記入に下記のように入力する

$latex

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(“A1”).Value = ActiveCell.Value
End Sub

$

これでマクロ保存形式でエクセルファイルを保存すると、アクティブセル(クリックしたセル)がA1のセルに表示できるようになる。

エクセルマクロVBA「複数の文字列に均等割りルビを追加」

前回、マクロを使ってセル内の1つの文字列だけにルビを打つことができた。ところが俳句をまとめていて一つの俳句に複数の文字列にルビを打つことが必要になることがある。そこで次のようなマクロを作ってみた。
漢字と書いたが、これはひらがなに英単語をとか、どのような組み合わせでも可能です。ここではルビは「ひらがな」、文字列に対して「均等割り付け」のルビにする、指定のセルのフォントサイズの「半分のサイズ」のルビにするという設定にしています。マクロの記述については、リンク先ホームページやエクセルマクロ有効ファイルを参考にしてください。

ホームページ内の記述

http://www.can-chan.com/vba/tsuika.html#m12 です。

エクセルのサンプルは以下のようなものです。3行目に文字列としてあげたもの全てを4行目のルビをふります。

複数語句

複数語句にルビ

複数語句にルビ

に均等ルビのマクロ複数語句に均等ルビのマクロ。ファイルをダウンロードして使用するときは、マクロを有効にしてご利用ください。ファイルサイズは17.5 KBです。

マクロを使ってエクセル表の列をランダムに並び替え

エクセルで並び替えというと、行を昇順とか降順で並べるのが普通ですが、エクセルで縦書きのデータを作成してそれを並び替えるという作業が必要になった。

そこで以前にここで

エクセルでランダムに表示、条件付き書式で罫線~俳句教室

にエクセルの別シートを活用して関数で処理することをやってみた。ただこれで満足していたら、新たな問題に出くわした。つまり表の中にルビを表示しているものがあり、それを別シートに移すとそのルビのデータはなくなってしまうということだ。

やむなくエクセルのマクロを使うことにする。それで作成したのが次の 

エクセルVBA8「並び替える」

です。

行列が増えてもこのマクロで列をランダムにシャッフルすることができます。

エクセルで縦書きをすることもあまりないと思いますが、俳句教室でみなさんの俳句を集めて清句するときに、少しでも手間を省こうと思い、それ以上の手間をかけてみました。

エクセルでランダムに表示、条件付き書式で罫線~俳句教室

俳句教室では参集者が俳句を2句ずつ投句して、それを清書して(「清句」)気に入ったものに投票し感想をべるということが行われる。教室のその場で清句して清句用紙をまわしてする場合もあれば、事前に投句を集め当日までに作者名を伏せて一覧にし投票するということも行われるそうです。そのとき同一作者の作品が並ぶことを避けて順不同に書き出すということが行われます。すべての用紙が集まってから、用紙をばらばらに並び変えてから書き写せば済むことですが、エクセルを利用してランダムに表示することを考えてみました。

1.投句をランダムにシャッフルする

1.投句を入力 2.ランダムに並べる

図1.投句を入力 2.ランダムに並べる

A1には西暦の数字を入れています。書式を「ユーザー設定」で「G/標準 “年”」とすることによって、4桁の数字「2015」を入れることによって「2015年」と表示できます。B1,B2は俳句教室が行われる月日の数字を入れています。これは清句用紙て利用します。

B3から下は、「=IF(C3=””,””,ROW()-2)」という数式を入力して、C3に俳句が入力されたら1から順番に番号を表示するようにしています。「ROW()」は行数を数えています。3行目から始めているので2を引けば1となります。これにより俳句がいくつ集まったかを自動で数えることができます。会員数と投句数によって考えればよいのですが、元のシートは多くせってしておけばいいです。ここではB101まで数式をコピーしています。C列に俳句を入力していますが先頭に1から順に数字を入れているのは、ランダムがどのようになっているのかを示すために入れているだけです。

ランダムにシャッフルするために、A列のA3に「=IF(B3=””,””,RAND())」という数式を入力してからA101までコピーしています。B3に番号が表示されたら、つまり俳句が入力されたら、RAND()関数の値を入れるということです。

F列からのランダムに表示された所の説明に移ります。

F列はB列と同様の関数を入れて1から順に番号を表示させています。F3=IF(G3=””,””,ROW()-2) です。

G1=IF(A3=””,””,INDEX($C$3:$C$101,MATCH(LARGE($A$3:$A$101,ROW(A1)),$A$3:$A$101,0)))

としています。RAND()関数で表示したA列の$A$3:$A$101の中で、1番大きな数字(LARGE関数)から一致する数字、つまりG3ではROW(A1)=1だから、1番大きな数値のA列に一致MATCHする俳句をINDEX関数で求めています。G4ではROW(A2)=2となるからA列で2番目に大きなものを求めるようになります。こうして投句されたものをランダム、順不同に表示することができます。これにより受け取ったもの順に入力していても表示が自動的に順不同にすることができます。

2.別のシートに縦書き表示する。右から番号を振る。

清句用紙

図2.清句用紙

図1の黄色で塗られた部分を、縦書きに表示して、俳句の表示らしくしています。O1:T1の欄はセル結合をし、「=DATE(投句!A1,投句!B1,投句!B2)」と投句シートで俳句教室の年月日を入れたものをここで表示しています。さらに年号で表示するためにセルの表示形式を「ユーザー定義」とし、「ggge”年”m”月”d”日(“aaa”)”」として、曜日まで表示するようにしています。

縦書きなので、右から1番と番号を打ちたいので、一番左端の番号は投句数によって変わってしまいます。そこで左端の数字を

A10==MAX(投句!F3:F101)

と投句シートのランダムに並べた表の中で最大の数値を求めます。A10にしているのはその上にいくつかのマスを投票した人の名前などを略記する欄としています。B10,C10・・・・・必要なだけ、投句シートでは100句まで入力できるようにしているのでそこまで

B10=IF(A10=””,””,IF(A10-1>0,A10-1,””))

として、左隣が空欄なら空欄、そうでなければ投句数から1ずつ引いていく。最後は1とする。投句の記入順に俳句の先頭に1から番号を売っていたのに、ランダム関数によってずいぶん順番が変わっているのが分かる。

投句欄A11などは

=IF(A10=””,””,VLOOKUP(A10,投句!$F$3:$H$101,2,FALSE))

VLOOKUP関数を利用して、上の番号に一致する投句を投句シートを参照して表示する。A12の欄も同様。

3.条件付き書式を利用して罫線を投句数に合わせて枠を作る

罫線について。投句数によって罫線の空欄がたくさんできるのもあまりよくないとすれば、投句数に応じてその時ごとに罫線を引くのもありだろうが、体裁が毎回変わるのもおかしいし、前回を流用して消したり増やしたりするのも面倒。一番楽なのは、投句数に応じて罫線が自動的に増減するように最初から設定しておく。それを可能にするのが、「条件付き書式」。

A2のセルで、「条件付き書式の設定」でルールを「数式を使用して、書式設定するセルを決定」を選択。ルールの内容欄で

実線で周りを囲む

図3.実線で周りを囲む

A2=A$10<>””

つまり、A10が空白でなかったら、書式「セルを実線で囲む」。B10~コピーするために、それを「A$10<>””」とAの前に$がついていないことに注意。内容編集の時にセルをクリックすると自動的に「$A$10」となるので、Aの前の$を削除しておく

適用範囲で最大範囲まで広げる

図4.適用範囲で最大範囲まで広げる

書式を設定後、適用先を「=$A$2:$CW$2」と100句でも可能なようにセル範囲を設定する。

A3~A8は下線が点線、両サイドの縦が実線としている。A3のセルで条件付き書式、図3と同様に「A$10<>””」で、書式欄を上空線、両サイド縦実践、下線を破選とする。そして適用範囲は「=$A$3:$CW$8」とする。

A9は「A$10<>””」で書式は両サイド実線、下線実線。範囲は「=$A$9:$CW$9」。

A10~A12はA2と同様に「A$10<>””」で書式をすべて実線で囲むとする。適用範囲は「=$A$10:$CW$12」とすればよい。これで投句数に応じて自動で表もできる。

エクセルで他シートのデータを検索し表示する

商品のデータ表が商品シートに分類もなされずに一覧表になっている。検索シートをつくって、分類名をダウンリストから選べば、その分類に属する商品をダウンリストから選択でき、その価格や在庫の有無などを表示できるようにしたい。

商品シートを次のようにつくる。

図1.商品シート

図1.商品シート

検索シートは次のようにする。

図2.検索シート

図2.検索シート

商品シートでは関数式を見やすくするために、次のように範囲に名前を定義する。

図3.名前定義

図3.名前定義

範囲に名前を定義したのは赤く囲まれたA~Eに設定した。A.「分類」、B.「商品表」、C.「くだもの数」、D.「野菜数」、E.「魚数」とした。

商品表の中の「分類欄」は表記のミスを防ぐために、入力規則を設定し、リスト>元の値に「=分類」と設定する。

図4.商品表分類入力規則

図4.商品表分類入力規則

商品表は追加をするかどうか、する場合どの程度の量になるかによって商品表の名前定義の範囲を設定する必要がある。品名欄は「りんご」と「リンゴ」では違うものとして扱われるので注意が必要です。

分類で分けた表示になっていないために、分類ごとに品名を表示するための作業に移ります。

作業列1はそれぞれの品がその分類の中で何番目に記載されたかをカウントする欄です。

図5.作業列1-分類ごとのカウント

図5.作業列1-分類ごとのカウント

「くだもの」欄の赤で囲んだセルには「=IF($A6=E$5,COUNTIF($A$6:A6,E$5),””)」と入力してあります。商品表のA列の「くだもの」の表示が何個目かをカウントしています。E5~E7の項目名は分類項目をそのまま表示することに寄って、赤で囲んだE6のセルをコピーして、「E6:G20」の範囲に「形式を選択して貼り付け」>「数式」とすれば、作業列1(黄色の範囲)がすべて表示できる。品目が増えてもよい。

つづいて、作業列2は作業列1で番号付けした品目を上から順番に記述する部分です。表が小さいときは一つ一つやってもよいのですが、いくら増えるか分からないときにはこうしておくと楽です。

図6.作業列2ー分類ごとに品名を並べる

図6.作業列2ー分類ごとに品名を並べる

作業列2のくだもの欄の赤で囲んだリンゴのところには数式で「=IF(MAX(くだもの数)<ROW($A1),””,INDEX($B$6:$B$20,MATCH(ROW($A1),くだもの数,0)))」と記述しています。「くだもの数」は図3で示した通り名前を定義したものです。数式をコピーするときにこの「くだもの数」のところが分類により異なるので、とりあえず、この数式をコピーし、I6:J6に形式を選択して数式を貼り付けます。そのあと、「くだもの数」のところを野菜欄のところは「野菜数」に魚欄は「魚数」という定義した名前に変更します。そしてH6:J6をコピーして表の下まで選択し、「形式を選択して貼り付け>数式」で図6のような結果が表示できるようになります。

ここまで出来たら、検索シートの設定を行います。

図7.検索シートの分類の入力規則

図7.検索シートの分類の入力規則

商品検索シートの分類欄の入力規則を設定します。商品シートで名前を定義した「分類」を使います。入力規則の設定で入力値の種類を「リスト」とし、元の値を「=分類」と設定してOKとします。そのセルをクリックすると三角の表示が示されそれをクリックすると、

図8.分類ダウンリスト

図8.分類ダウンリスト

ダウンリストが「くだもの、野菜、魚」と表示されるので、表示させたいものを選びます。

続いて品名の欄に行くと

図9.品名入力規則

図9.品名入力規則

同様に入力規則を設定する。ここは分類欄に応じた品名の表示をする必要があるので、元の値を「=INDIRECT($A$3)」と設定します。これによりダウンリストが下のように表示できるようになります。

図10.品名ダウンリスト

図10.品名ダウンリスト

ここでは、イワシを選択してみました。さらにそのイワシのデータを表示させるために、価格と在庫の欄に次のように数式を設定します。

図11.商品データの表示

図11.商品データの表示

価格欄は「=VLOOKUP(B3,商品表,2,FALSE)」、在庫欄は「=VLOOKUP(B3,商品表,3,FALSE)」。この中でB3のデータを商品シートの「商品表」の中から検索し、その2列目、3列目を表示しなさいという数式です。「FALSE」とすることにより完全一致のデータを表示することになります。

図11.項目の多い商品データの時

図12.項目の多い商品データの時

項目の多い商品データの時は、一項目ずつ変更するのも面倒なので、「=VLOOKUP($B$10,商品表,COLUMN(C10)-COLUMN($B$10)+1,FALSE)」のようにして、列番号を「COLUMN(C10)-COLUMN($B$10)」として品名の列から何列離れているかという形にして、価格欄の数式を、横に必要なだけコピーすれば簡単に表示させることができる。

 

エクセルで他シートのデータを分類のダウンリストで検索しその分類内の商品をダウンリスト表示する

エクセルのシートを検索表示シート、商品分類商品名シートとし次のような形とする。

分類検索

図1

 

上にあるように、複数の分類の中から1つ選んだら、その分類内の商品名が表示でき、価格や在庫の様子を示したい。そこで

1_名前の定義

図2

 

商品名のシートが上のようにつくられている。簡単にするために分類ごとに商品名も並んでいる。このシートの中を後の関数式をわかりやすくするために名前を定義する。

2_名前の定義a

図3

 

枠で囲んだところをそれぞれ選んでは、右クリックしてメニューから「範囲に名前をつける」をクリックし、aは「分類」、bは「くだもの」,cは「野菜」,dは「魚」と定義する。

図1の検索シートに戻り、分類の欄(A2)に入力規則を設定する。

分類入力規則

図4

 

図4の「元の値」の「分類」はシート「商品」で定義した「分類」を示している。

続いてその分類に応じた商品を選択する欄には

品名の入力規則

図5

図5のA2の分類欄で選んだものに該当する商品だけを表示するために、入力規則をリストを選択し、元の値を「=INDIRECT($A$2) 」と設定する。これで分類欄で「くだもの」をダウンリストから選択すると「くだもの」に分類された商品だけがダウンリストで表示できる。

ここでは1つだけのものを表示することを考えているので、商品シートに作業のための行を設定した。

作業欄の設定

図6

商品シートの色を塗ったところが作業のための欄です。「分類」と「商品名」は検索シートから値をとるので、「=検索!A2」「=検索!B2」とします。さらに価格と在庫の欄についてはこの商品シートないですからVLOOKUPの関数が使用できます。「価格」は「=VLOOKUP($B18,$B$6:$D$14,COLUMN()-COLUMN($B18)+1,FALSE)」、在庫は「=VLOOKUP($B18,$B$6:$D$14,COLUMN()-COLUMN($B18)+1,FALSE)」と関数式を入力することで表示できます。

最後に検索シートに戻り

品名検索

図7

価格と在庫の欄は商品シートの作業欄のデータをとるということで、「=商品!C18」,「=商品!D18」とすれば表示させることができます。

図5と図7でデータが異なりますが、ダウンリストに応じて選択できるというのは、いろいろに使えそうです。今回やってみて、他シートにデータが数多くあり、別シートにそのデータを表示できるのです。

 

 

 

エクセルで分類と品名を名前定義を利用し連動させてドロップダウン表示させる

エクセルで分類欄と品名欄、それぞれの品に多くの特別のデータがあり、それを操作するシートに検索して表示させたいことが生じた。そこでここでは簡単な例をつくり覚えのために記録する。

名前の定義a

 

このような分類と品名、それぞれのデータのシートがある。枠で囲ったa~dの範囲をそれぞれ選択しメニューから(あるいは右クリックで)「範囲に名前をつける」をクリックし、a:「分類」、b:「くだもの」、c:「野菜」、d:「魚」と定義する。

検索シートの分類の表示欄に

分類入力規則

 

と名前を定義した「分類」を元の値の欄に記入し入力規則を設定する。続いて品名入力規則を

品名の入力規則

として元の値の欄に「=INDIRECT($A$2)」と入力し、A2の欄の分類名に応じて品名が表示できるようにする。こうすることによって

品名検索

 

上のように分類に応じた品名だけが表示できる。

続いて価格や在庫の様子について表示するために、商品シートに作業欄を設ける。

作業欄の設定

 

色で表示している部分が作業欄である。分類や品名は、検索シートのデータを参照する。そして価格や在庫の欄は上の俵の中からVLOOKUP関数を用いて検索し値を表示させている。列の表示は2とか3とかと入力してもよいのだが、たくさんのデータがあることを想定して

=VLOOKUP($B18,$B$6:$D$14,COLUMN()-COLUMN($B18)+1,FALSE)

として、COLUMN()-COLUMN($B18)+1 で基準列からの列番号を取得する。これによって右にデータを「コピー」>「数式の貼り付け」によって簡単に同様の作業をできるようにする。

この作業欄をつくることによって、検索シートの品名を選択したらこれらのデータ表示が楽になる。

品名検索

検索シートの価格、在庫などの欄は、商品シートの価格、在庫欄のデータを表示するように「=商品!C18」などと入力するだけで終わる。横にどれだけの欄があっても楽である。

今回は、非常に単純な商品シートを使い、分類や品名の名前定義が簡単にできた。分類が入り組み品が次々に増えていく場合には、分類名だけを集約する欄、それに応じる品名を集約する欄を作成しておく必要がある。

エクセルのセルにチェックボックスを作成し、条件付き書式で利用

[開発]タブが表示されていないときは、エクセルのリボンの[エクセルオプション]>[基本設定]>

エクセル開発にチェック

 

「[開発]タブをリボンに表示する」にチェックを入れる。

開発コントロール

 

[ 開発 ]タブの[ コントロール ]メニューの[ 挿入 ]から「チェックボタン」をクリックして、必要なセルでクリックする。

チェックボックス チェックボックス2

チェックボックスの後ろの「チェック・・」は不要なので、削除する。

チェックボックス書式設定

 

チェックボックスを右クリックし、プロパティから「コントロールの書式設定」をクリックし、

コントロールのリンク

 

リンク先のセルを設定しておく。これにより、チェックされると、「TRUE」、そうでなければ「FALSE」と表示され、集計や書式設定にも利用できる。

チェックボックスリンク

 

チェックで書式設定

 

こうして、書式設定しておけば、チェックに合わせてその行全体を把握しやすくなる。

チェックボックスをコピーして貼り付けや、セルのドラッグでコピーしても、リンク先を一つ一つ設定しなおさないといけないのは、ちょっと厄介。マクロでの設定方法もあるようなので、またいつか、学ばせてもらおう。

エクセルの表で1行おきに色を変える

項目や行数が多くなると、1行おきに色を変えたくなる。

そんな時に便利なのが、条件付き書式で、色を設定することだ。

「条件付き書式」>「新しいルール」>「数式を使用して、書式設定するセルを決定」と選んでいって、

「ルールの内容」>「次の数式を満たす場合に値を書式設定」の欄に次のように入力する。1行ごとに色の条件付き書式

「 =MOD(ROW(),2)=0 」という数式を入力し、書式で「塗りつぶし」タブのところで、適当な色を選択する。

列の方も1列ごとに色を変えたい場合は、同様にして

「 =MOD(COLUMN(),2)=0 」という数式を入力して、同様に書式を設定する。

1行1列ごとの色分けルール

「適用先」を表の範囲として例えば「 $A$1:$R$50 」というふうに設定すれば、1行ごと、1列ごとに色が塗り分けられていく。

数式で設定しておくと便利なのが、途中から行や列を増やしたり、逆に減らしたりしても、「1行ごとに色を変える」ということには変わりがないということだ。数式を使わずに色を変えることはできるが、その時は、行列の増減で同じ色が並んでしまって、また最初から設定という面倒なことが生じてしまう。また、表を並び変えたりしても、1行列ごとの色に変わりがないということも数式で設定のメリットがある。

エクセルでフラッシュカード用のファイルを作ってみました。

電子黒板やスマートボードに映し出して使用できるように、エクセルでフラッシュカードを作りました。使いたい(1)用語の登録、(2)利用のしかた(順番に表示する、ランダムに表示する、設定した秒数によって自動的に切り替わるようにする)を選択する、(3)用語は1000項目まで登録できる(たぶん別ファイルに分ける方がよいかもしれないが)、登録した用語の何番から何番までの用語を表示させるのかの設定、などを行う。これにより、1時間単位、あるいは数週間単位で利用できるので一度登録したデータを繰り返し使うことができる。用語の登録も表面と裏面の登録及び表示ができるので、小テストなどに利用するという使い道もできるかもしれない。

 ここではその使用法のファイルと、実際のエクセルファイルをリンクしている。エクセルファイルで、ランダムに表示させたり、タイマーによりランダム表示などをさせるために、マクロを設定しています。マクロを有効にして利用下さい。  

使用方法

エクセルでフラッシュカードの使用法

エクセルでフラッシュカードをつくってみました&使用法

エクセルのシートに必要語句を入力するだけでフラッシュカードができます。

フラッシュカード エクセル作成 昇降順 ランダム タイマー自動

  • ファイルを開いたら、ファイル名を変えて作成して下さい。
  • ①にデータの登録。出題の範囲は表示するシートの方で設定するので、1000件は表示可能にしている。必要があれば設定し直すことも可能だが、多すぎても使いづらいかも。
  • フラッシュカードにしたい用語を登録したら、その時間に使用したいカードの最初と最後の番号を控えておく。
  • フラッシュカード用のシートは「手動 昇順・降順」「自動タイマー」「手動ランダム表示」のシートがあるので必要に応じて選択する。
  • 「手動 昇順・降順」
  • その他

 電子黒板などでの画面表示がどうなるのか分からないので、ご意見下さい。

使ってみてエラーなどが出たら、ご指摘ください。

エクセルのセル内の文章の中に桁区切りの数値を表示する。

エクセルのあるセルに文を書き、その文中にエクセルの表の中で計算した合計数値などを入力したいことがある。

たとえば、金額の合計数値が、「 F8 」のセルに表示されるとする。ここにはセルの書式設定で、桁表示を設定したり、表示形式>会計から「 ¥ 」記号をつけることもできる。

あるいは、ユーザー定義で「 #,##0 "円" 」などと設定しておくと、見かけは「123,456 円」と表示されていても、普通に計算もできる。これを、セルの中に「 123,456 円」と書いてしまうと、その先その値は計算できなくなってしまうから、セルの表示で見せかけをしておくのが便利である。

さて、一つのセルの中に数字のみ、あるいは数字の後に文字列なら、上のようなユーザー定義で済ませることもできるが、文中に入れようとすると、桁区切りまではできなくなる。

列を細かく設定して、文章と文字表記のセルをうまく組み合わせる方法もあるが、もうすでに表ができているような場合は、途中から列を増やすのは手間がかかってしまう。

たとえば、

「今年の集計結果は123,456円となり、これを次年度に繰り越します。」

などと書きたい場合は、セルに(いくつかのセルを結合してもよいが)計算式を

="今年の集計結果は"&F8&"円となり、これを次年度に繰り越します。"

と書けば、ほぼ満足できる。ところがこのセルに桁区切り設定をしても、そのようには表示してくれなくて

「今年の集計結果は123456円となり、これを次年度に繰り越します。」

と桁区切りができない。そこで次のようにすると、文中でも桁区切りができる。

="今年の集計結果は"&FIXED(F8,0)&"円となり、これを次年度に繰り越します。"

「 ,0 」は、小数点以下を表示しないことを示している。これにより

今年の集計結果は123,456円となり、これを次年度に繰り越します。

と表示することができる。

エクセルでセルの絶対参照 $のつけ方。F4キー

セルを絶対参照するのに、「=」を入力して、絶対参照するセルをクリック、そのあとに「F4」キーを押すだけとは、簡単なものだ。

今まで無駄に、英数モードにして、「SHIFTキー」+「$/4のキー」を列を示すアルファベットの前、行を示す数字の前の2か所に入れていた。
セルをクリックしてF4キーだけで一度に2つの$マークがつくなんて、知らないということは、どれだけ多くの時間を無駄にしてしまってたことか。時間を返して!と、自分に言うしかないか?

エクセルの表の中で特定の文字列を●で挟む。そのセルに色をつける。

エクセルの表の中にたくさんの文字列があると、必要な文字列が見つけにくくなってしまう。そんなときに関数を使って、見つけ、さらにそのセルごと色を塗って際立たせる。

検索したい文字を赤色などで表示できればよいのだろうが、マクロでも使用しないと無理なようです。ここでは、簡単に関数で検索文字を含むセルを色を塗って見やすくします。

特定の文字列を●で挟む

検索文字そろばん
今日の日今日の日
88そろばんの日◆ひげの日◆親孝行の日●そろばん●の日
ひげの日
親孝行の日
89長崎原爆記念日◆ムーミンの日◆はり・きゅう・マッサージの日長崎原爆記念日
ムーミンの日
はり・きゅう・マッサージの日
関数SUBSTITUTEを利用して、特定の文字列を●で挟む

 

で検索文字の所に入力した文字があれば、セルの中で検索し、その文字を●で挟みます。その関数式は次のようになります。

TRIM関数で文字列から余分なスペースを取り除きます。

CHAR(10)は、セル内改行のAlt+Enterを意味します。

SUBSTITUTE関数は、=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)という置換の働きをします。したがって、◆を検索して、それを改行マークに変えます、ということになる。
「$D$1,"●"&$D$1&"●"」は、その値をさらに●で挟んだ形の文字にしますということになる。

 

特定の文字を●で挟む関数

検索文字そろばん
今日の日今日の日
88そろばんの日◆ひげの日◆親孝行の日=SUBSTITUTE(SUBSTITUTE(TRIM(C10),"◆",CHAR(10)),$D$1,"●"&$D$1&"●")
89長崎原爆記念日◆ムーミンの日◆はり・きゅう・マッサージの日=SUBSTITUTE(SUBSTITUTE(TRIM(C11),"◆",CHAR(10)),$D$1,"●"&$D$1&"●")
関数のSUBSTITUTEの使用方法

 

●で検索文字がわかりやすくなったが、さらに際立たせるために、その文字を含んだセルを色でぬる。これは条件式書式を利用する。表の範囲を選択し条件付き書式を設定します。

検索文字列には必ず●が入っているから、特定の文字が入っているセルを、ここでは黄色にマークするように設定した。

エクセルの表の中で●で挟むとしたが、検索文字列がわかりやすいもので、表の中に使われていないものなら何でもよい。

エクセルのセル内の文字列を特定の文字で改行する

月の行事予定表などを作るのに、一つのセルにいくつかの項目を◆などの区切り文字を入れて作成することがある。もちろんスペースでも構わないのだが、それを別のシートなどで見やするするために、◆で改行させる方法を覚えのために書きます。

今日の日今日の日
88そろばんの日◆ひげの日◆親孝行の日そろばんの日
ひげの日
親孝行の日
89長崎原爆記念日◆ムーミンの日◆はり・きゅう・マッサージの日長崎原爆記念日
ムーミンの日
はり・きゅう・マッサージの日

 

C列が行の高さを節約するために、◆によっていくつかの項目が続けて入力されている。
D列はそのC列を関数を使って改行した状態。

その関数は次のように入力されている。

今日の日今日の日
88そろばんの日◆ひげの日◆親孝行の日=SUBSTITUTE(TRIM(C4),"◆",CHAR(10))
89長崎原爆記念日◆ムーミンの日◆はり・きゅう・マッサージの日=SUBSTITUTE(TRIM(C5),"◆",CHAR(10))

 

改行を行うD列の表示設定は、「折り返して全体を表示する」に設定しておくこと。

TRIM関数で文字列から余分なスペースを取り除きます。

CHAR(10)は、セル内改行のAlt+Enterを意味します。

SUBSTITUTE関数は、=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)という置換の働きをします。したがって、◆を検索して、それを改行マークに変えます、ということになる。

 

エクセルで複数シートの値を参照する。INDIRECTとADDRESS関数

毎月決まったシートに同じようなデータを入力し、それを月毎に集計するといったことがある。集計欄をまとめて表示できると、毎月の変化を比較しやすい。ここに取り上げたのはとても、簡単なシートである。各シートには2つのデータしか入っていないが、それは合計欄の数字であったり、あることを示した文字列であったりする。非常に簡単なエクセルのシートとデータを利用して、他のシートを参照するしかたをまとめてみる。

シート2からシート4には次のようにデータが入っている。

 

 

シート2から4に入っているデータをシート1にまとめて表示する。

セルに入力されている関数式は次のようになっている。

B3の値B4の値
sheet2=INDIRECT(ADDRESS(3,2,,,B4))=INDIRECT(ADDRESS(4,2,,,B4))
sheet3=INDIRECT(ADDRESS(3,2,,,B5))=INDIRECT(ADDRESS(4,2,,,B5))
sheet4=INDIRECT(ADDRESS(3,2,,,B6))=INDIRECT(ADDRESS(4,2,,,B6))

 

エクセルで表示されている行だけに番号を打つ

受付名簿などを作成し、最初の欄に人数確認の意味で番号をふりたい。
ただ、数が多くなると、いったん番号を振って、途中から参加する人が出たりすると、番号を書きなおすのは大変面倒な仕事となる。

そんな時にあらかじめ次のような計算式を入れておけば、出席の丸のついた行だけを表示し、それ以外を非表示にしておけば、よい。途中で参加が確認された場合でも、その人の所に丸をつけるなどして表示すれば、カウントは式によって増えることになる。

人数出席A列の関数式
1=SUBTOTAL(103,$B$2:B2)
1=SUBTOTAL(103,$B$2:B3)
2=SUBTOTAL(103,$B$2:B4)
3=SUBTOTAL(103,$B$2:B5)
3=SUBTOTAL(103,$B$2:B6)
4=SUBTOTAL(103,$B$2:B7)

セルの値に語句を追加してをシート名に

毎月作るエクセルのシート。
いちいちシート名を変えるのは面倒なこと。
そんなときに、セルの値を利用して自動的にシート名をつけることができれば、同じシートをコピーして増やしていっても、楽である。

シート名のタブの所で、右クリックして表示されるメニューから「コードの表示」をクリック。
表示されるファイル名sheet(番号)(コード)という白紙の窓の中に以下の記述をコピーして貼り付ける。

D2には月を示す数字を入力している。D2はセルの書式設定で、ユーザー定義>「G/標準"月"」、として「6」と数字を入れるだけだが、「6月」と表示されることになる。

シート名は「○月」という表示にするために、D2の値に「月」をプラスしている。下のコードの「&"月"」の部分である。
 

///////////

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR_HANDLER
If Target.Address(False, False) = "D2" Then
ActiveSheet.Name = Range("D2").Value & "月"
End If
Exit Sub
ERR_HANDLER:
MsgBox "現在のD2セルの値はシート名にできません。"
End Sub

///////////////

実際に、D2の値あるいは文字を変更すると、シートの名前が変わっていくことを確認することができる。