エクセルのある行や列の値を入力規則のリストに設定することができる。
そのリストを、名前を定義しておくと設定の時に楽である。
エクセル２００７で、次のようにたどる。
数式タブ ＞ 名前の管理 ＞ 選択範囲から作成 ＞
この中に、「上端行」 「左端列」 「最下行」 「右端列」 の４つが選択できる。
したがって、範囲にふさわしい名前を、上端とか左端において、範囲を選択して、チェックを入れるとその値が範囲の名前として定義されることになる。
データタブ ＞ データの入力規則 ＞ リスト ＞ 定義した名前
とすれば、ある範囲に入力できる値を設定することができる。
A1:A3とセルを結合させデータを入力すると、A1にはそのデータは入っているが、A2,A3にはデータは何も入っていない。別のセルに「=A2」「=A3」としてみると、何も入っていないことがよくわかる。
この場合、A1、A2,、A3に対応したたとえばB1、B2、B3に条件付き書式やSUMPRODUCT tなどの行列関数を使おうとすると、使えない。
そうしないために、次のように設定していく。
１．A1とA2,A3を結合させるとすると、A2に「＝A1」,A3に「＝A1」の式を入れておく
２．たとえば、F1：F3のセルを結合（どこでもよい。結合しようとするセルと同じ形状にどこかのセルを結合する。）して、コピーする
３．A1を右クリックして、「ショートカットメニュー」の「形式を選択して貼り付け」を選択して
４．「書式」にチェックし、OK
このようにして結合させている（結合に見せかけている）セルの一つ一つの値に応じて、条件書式とか、計算を行うことができる。
エクセルでその月の日数を調べたいとき
西暦A年B月の日数は
=DAY(DATE(A,B＋１,1)-1)
DATE(A,B＋１,1)はB月の翌月の初日１日のシリアル値を求める式である。
翌月の１日のシリアル値が分かれば、その数から１日引けば、求めるB月の最後の日が指定できる。
DAY(B月の最後の日)とすれば、「B月の最後の日」のシリアル値が、最後の日の数字（31,30,28など）と求めることができる。
この考え方は、月をまたがるカレンダー表示などであるデータをカウントする時に、その月の日数から差し引くなどの計算をすることに利用できる。
年間行事予定表2030年まで祝日対応（エクセル2007ファイル。サイズ126kb。）
このリンク先をクリックすると、Windows Internet Exploerだと、zipファイル形式になる。それを解凍することなく拡張子の「zip」を「 xlsx 」に変更すると、アップロードしたエクセルファイルとして開ける。
特徴２ 年度作成シートで行事予定を作成する年度を入力するだけで年度内のカレンダーを表示します。
特徴３ 授業日数、授業時数、給食回数を一枚のシートで集計します。
特徴４ 祝日の日にちは色を自動でつけます。
年間行事予定表の使い方
１．シート①｢年度設定・祝日」のG4に西暦年を入力する。
２．１．によりシート｢行事予定表｣のカレンダーができる。
３．シート｢行事予定」に予定を書き込む。
４．祝日は数字を赤く表示している。
５．日にちの右隣には、給食の有無、授業時数の入力欄がある。
６．給食欄には、授業日で給食ありは無印。授業日だが給食はない時は「s」(小文字のエス)。授業日でなく給食がなければ「x」(小文字のエックス)。
７．授業時数は1日にカウントする時数を半角数字で入力。
８．６．７の入力は予定表右欄の集計表に計算されていく。月毎の計。その月までの累計。年間の累計。
９．多くの計算式、条件付き書式が入力されているので、変更されたくない部分には保護をかけている。
１０．シートの保護を解除する時はパスワード「wagencan」。
エクセルの関数式を利用して、授業日数、給食回数、授業時数を一つのシートで集計まで行う。中学校を想定して3学年分の集計ができるようにする。
中学校年間行事予定表-2030年まで対応 授業時数-給食回数-授業日の集計を1つのシートで
集計は、月毎、学年ごと、累計を行っていく。行事予定の変更によっていちいち、日数などを計算しなおす必要はない。年度初めの年を入力するだけなので、曜日の設定も自動で表示されている。
リンクさせているエクセルファイルは2007バージョンである。行事予定で入力しているのは、想定される行事をかりに入力し、授業時数とか給食のあるなし、などを想定して入力している。
祝日や給食のない日、授業日でない日は、じょう件付書式設定で色を変えている。
仕事でエクセルを使うことがよくある。
Aのシートには、1行ずつにデータが入っている。
ところが、新たに作成したBシートには、3行を結合したセルで表を組んでいる。列によっては結合せずに1行ずつに項目を入力している。
Aシートにあるたくさんの列のデータを、Bシートに貼り付けたい。ところがセルの形が違うので一度には貼り付けができない。
そこで、そのシートのセルを結合していない列を作業列として、まず張り付ける。その隣の列に次のような関数式を入力する。３行ずつ結合したところに関数を入れればいいが、ここでは結合していない状態でしか示せない。プラグインWT-Table Reloadedを十分に使いこなせていない。
| =IF(OFFSET($AU$3,(ROW()-3)/3,0)=0,"",OFFSET($AU$3,(ROW()-3)/3,0)) | 0 |
|---|---|
| 0 | |
| 0 | |
| =IF(OFFSET($AU$3,(ROW()-3)/3,0)=0,"",OFFSET($AU$3,(ROW()-3)/3,0)) | さくら祭 |
| 昭和の日 | |
| 0 | |
| =IF(OFFSET($AU$3,(ROW()-3)/3,0)=0,"",OFFSET($AU$3,(ROW()-3)/3,0)) | 0 |
| |
|
| =IF(OFFSET($AU$3,(ROW()-3)/3,0)=0,"",OFFSET($AU$3,(ROW()-3)/3,0)) | 0 |
| 0 | |
| 0 | |
| =IF(OFFSET($AU$3,(ROW()-3)/3,0)=0,"",OFFSET($AU$3,(ROW()-3)/3,0)) | 0 |
| 0 | |
| 美作地区総体 |
こうして、３行ごとに結合されたセルに表示される値が次のようになる。
| 0 | |
|---|---|
| 0 | |
| 0 | |
| さくら祭 | |
| 昭和の日 | |
| 0 | |
| 0 | |
| |
|
| さくら祭 | 0 |
| 0 | |
| 0 | |
| 昭和の日 | 0 |
| 0 | |
| 美作地区総体 |
上のテーブルは結合された状態ではないが、エクセルでは結合して行えばよい。結合されたセルに表示された値をコピーして、値の貼り付けを作成表にしていく。たくさんある列も同様にしていけば、作業が楽である。この表は別のシートから数式の値をコピーしたため、入力のないセルが「０」と表示されている。「０」の所は、「””」で何も表示しないことにしている。
VBAを作ればもっと楽なのだろうが、作成は７列の作成だったので、私はこれで、満足だった。
表を扱っている時に、特定の条件に当てはまる個所が何か所あるか、またその条件に一致する個数なり、金額なりの合計がいくらになるのか調べたいときがある。それも年月日などのデータが複数列に作成されていて、表の並び替えも面倒、これ一回きりの集計ではないから、使いまわしのできる集計表にしたい。そんなときがある。今職場で、年間の行事予定表があり、横に曜日が表示され、1週間で次の行になる、その表の中に給食回数や授業時間数を入力する表を作って、一括して集計する。まだ完成はしていないが、ここに示すのはその前段階の小さな表である。
表の中に、学年、年月日、種類、個数というものが、2列にわたって入っている次のような表があります。このデータは「学年」などとそのデータの範囲にわかりやすく名前を定義してあります。関数の計算式を見るときはそのように見て下さい。
この表の中で、ある学年の、ある月の、ある種類の数を数え、その時の個数を集計したい。
| 学年 | 年月 | 種類 | 回数 | 個数 |
|---|---|---|---|---|
| 1 | 1104 | x | 1 | 4 |
| 2 | 1104 | o | 1 | 5 |
| 3 | 1104 | x | 1 | 4 |
こんなときに利用できるのが、SUMPRODUCT関数である。この関数を次のように使って集計する。
| 学年 | 年月 | 種類 | 回数 | 個数 |
|---|---|---|---|---|
| 1 | 1104 | x | =SUMPRODUCT((学年A=I16)*(TEXT(月日A,"yymm")=J16)*(種類A=K16)+(学年B=I16)*(TEXT(月日B,"yymm")=J16)*(種類B=K16)) | =SUMPRODUCT((学年A=I16)*(TEXT(月日A,"yymm")=J16)*(種類A=K16)*個数A+(学年B=I16)*(TEXT(月日B,"yymm")=J16)*(種類B=K16)*個数B) |
| 2 | 1104 | o | =SUMPRODUCT((学年A=I17)*(TEXT(月日A,"yymm")=J17)*(種類A=K17)+(学年B=I17)*(TEXT(月日B,"yymm")=J17)*(種類B=K17)) | =SUMPRODUCT((学年A=I17)*(TEXT(月日A,"yymm")=J17)*(種類A=K17)*個数A+(学年B=I17)*(TEXT(月日B,"yymm")=J17)*(種類B=K17)*個数B) |
| 3 | 1104 | x | =SUMPRODUCT((学年A=I18)*(TEXT(月日A,"yymm")=J18)*(種類A=K18)+(学年B=I18)*(TEXT(月日B,"yymm")=J18)*(種類B=K18)) | =SUMPRODUCT((学年A=I18)*(TEXT(月日A,"yymm")=J18)*(種類A=K18)*個数A+(学年B=I18)*(TEXT(月日B,"yymm")=J18)*(種類B=K18)*個数B) |
この結果が、次のようになる。
| 学年 | 年月 | 種類 | 回数 | 個数 |
|---|---|---|---|---|
| 1 | 1104 | x | 1 | 4 |
| 2 | 1104 | o | 1 | 5 |
| 3 | 1104 | x | 1 | 4 |
この時に注意が必要なのは、年月日の日付をTEXT関数でYY/MMと表示するのだが、集計の表の中の「1104」と入力する前に、これらの年月を入力する欄のセル範囲を
セルの書式＞表示形式＞文字列
と設定しておく必要がある。数字を入力後などに文字列にしてもエクセルは別のものと読んでしまうようである。
このことに気づかせてくれたのは、次のサイトである。感謝したい。
エクセルでその月の日数を調べたいとき
A年B月の日数は
=DAY(DATE(A,B＋１,1)-1)
DATE(A,B＋１,1)はB月の翌月の初日１日のシリアル値を求める式である。
翌月の１日のシリアル値が分かれば、その数から１日引けば、求めるB月の最後の日が指定できる。
DAY(B月の最後の日)とすれば、「B月の最後の日」のシリアル値が、最後の日の数字（31,30,28など）と求めることができる。
エクセルの複数シートでのセル集計にINDIRECTを利用（countifができない）
シート間をまたがってcountifができないので集計をするシートのあいているところに集計に必要な数値を入れるシート名：セルG1:K1集計をするセルは各シートの同一セル。集計セルの行列地番を列番号：F4同 行番号：F5～集計セルがF5からF20まで続くため行番号をF5:F20まで(INDIRECT(ADDRESS($F5,$F$4,,,G$1))
いくつかのサイトで記載されていることをURLとその表記を掲載する。下記のサイトによる=INDIRECT(G1&"!E5")では、値がとれなかった。この時シート名を各シートのA1セルに入力している名前をシート名にしていた（マクロ使用）。このA1のセルに数字を入れると、=INDIRECT(G1&"!E5")で値を取ることができた。
http://q.hatena.ne.jp/1147068497
には、集計作業をしているシートの空いている列などを作業領域にし、INDIRECT()を使って一旦各表の該当セルへの参照を集めておけば、お望みのCOUNTIFが可能です。例えば10枚あるシートのA3セルを集計したいとします。ここでは作業領域をHとIにしてみます。H1～H10にシート名を入れておき、Iには =INDIRECT(H1&"!A3")として、オートフィルでI1～I10「=INDIRECT(H10&"!A3")」までを埋めます。これで、I列に各シートのA3の参照がならびましたので、同じシートの任意のセルで=COUNTIF(I1:I10,"○")を掛けてみてください。
http://www.relief.jp/itnote/archives/001697.php
にはINDIRECT関数は、引数に指定されたセルに入力されているデータを使ってセルを参照してくれる関数です。A2セルに「4月」と入力されているときに「=INDIRECT(A2&"!B33")」は「='4月'!B33」と同じ意味になります。上記の数式でINDIRECT関数の引数のうち「A2」はセルの相対参照ですから、下方向にオートフィルすれば「=INDIRECT(A3&"!B33")」「=INDIRECT(A4&"!B33")」と変化しA3・A4セルにシート名を入力しておけば、それぞれのシートのB33セルの値を表示するようになります。
http://oshiete.goo.ne.jp/qa/3547667.html
セルＡ１に入力されたシート名の、セルＢ３を参照する場合 =INDIRECT(A1& "!B3")また上記ではセルＢ３が固定になってますが、セルＡ１ に参照したい【シート名】をいれセルＡ２ に参照したい【セル番地】まで入れておくとなお、使い勝手がよくなるかも知れませんね。 =INDIRECT(A1& "!" & A2)
http://q.hatena.ne.jp/1166863251使用する関数：ADDRESS と INDIRECT
例： Sheet1のA1にSheet2という文字列が入っているとします。 Sheet2のA2に「参照する値」とい文字列が入っているとします。式： =INDIRECT(ADDRESS(2,1,,,A1)) これで、=Sheet2!A2 と同じ結果になります。解説： =ADDRESS(2,1,,,A1) のみですと、Sheet2!$A$2という文字列になります。その後、INDIRECTを使うことにより、値を取得します。
エクセルで表を作成しているときに、場合によって表示しなくてよい行がある。しかし表示されている行だけの個数を番号表示して、ものの準備をしたいことがある。そんなとき有効なのが、この関数だ。
表示されている行数をカウント表示したい列に
=SUBTOTAL(102,$B$2:B2)
とA2セルに入力し、表のA列の最後まで数式をコピーする。
これでどのように表示・非表示をしても、表示されている行だけの個数を番号表示できる。
上の関数の括弧の中の「102」というのは、COUNT関数を示しており、表示されている行をB2からＢ○○までカウントしなさいということを意味している。
「102」以外の集計の仕方については
http://office.microsoft.com/ja-jp/excel-help/HP010062463.aspx?CTT=5&origin=HA010215738
に詳しく説明されている。このサイトでいいことを教えていただいた。
A1のセルに入力されている文字列が◆でつながれているとする。
これをB1のセルに◆の部分で改行した形にする時
=SUBSTITUTE(TRIM(A1),”◆”,CHAR(10))
と関数式を入力し、セルの書式を｢折り返して全体を表示する｣に設定する。
これで◆の部分で改行された文字列をB1に表示させることができる。