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

エクセル関数の票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)))

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

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

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

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

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

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

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

たとえば、

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

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

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

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

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

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

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

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

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

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

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

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

今日の日今日の日
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(文字列,検索文字列,置換文字列,置換対象)という置換の働きをします。したがって、◆を検索して、それを改行マークに変えます、ということになる。

 

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

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

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

人数出席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)

年間行事予定表 エクセルファイル(再掲)

年間行事予定表2030年まで祝日対応(エクセル2007ファイル。サイズ126kb。)

http://www.can-chan.com/wordpress/wp-content/uploads/2012/01/nenkangyojiyoteihyo-2030taio-kyusyoku-jugyosu1.xlsx

このリンク先をクリックすると、Windows Internet Exploerだと、zipファイル形式になる。それを解凍することなく拡張子の「zip」を「 xlsx 」に変更すると、アップロードしたエクセルファイルとして開ける。

特徴1 祝日は2030年までの春分の日と秋分の日は対応。

特徴2 年度作成シートで行事予定を作成する年度を入力するだけで年度内のカレンダーを表示します。
特徴3 授業日数、授業時数、給食回数を一枚のシートで集計します。
特徴4 祝日の日にちは色を自動でつけます。

年間行事予定表の使い方 
1.シート①「年度設定・祝日」のG4に西暦年を入力する。
2.1.によりシート「行事予定表」のカレンダーができる。
3.シート「行事予定」に予定を書き込む。
4.祝日は数字を赤く表示している。
5.日にちの右隣には、給食の有無、授業時数の入力欄がある。
6.給食欄には、授業日で給食ありは無印。授業日だが給食はない時は「s」(小文字のエス)。授業日でなく給食がなければ「x」(小文字のエックス)。
7.授業時数は1日にカウントする時数を半角数字で入力。
8.6.7の入力は予定表右欄の集計表に計算されていく。月毎の計。その月までの累計。年間の累計。
9.多くの計算式、条件付き書式が入力されているので、変更されたくない部分には保護をかけている。
10.シートの保護を解除する時はパスワード「wagencan」。