エクセルの関数について

質問

お世話になります。エクセルのことで教えてほしいことがあります。
学校アンケートの集計についてです。保護者の人にアンケートに答えてもらいます。ですが、その家庭に複数の児童（きょうだい）がいても、アンケート用紙は1枚のため、学年とその結果をまとめて書いてもらい、入力表もそれに沿ったものになっています。
アンケートの結果は、各自で入力してもらいます。それを集計するのが自分の仕事です。
具体的に何をしたいかというと・・・。
たとえば、その家には3人のきょうだいがいるとして、D20のセルには６（年生）、この子の様子は「４」だとすると、D21に「４」と入力します。
その下に4年生の子がいるとしたら、D22に「４」と入力し、その子の様子が「５」ならば、D２３に「５」と入力します。
これを、クラス全員（だいたい35名ほど）分、入力してもらいます。

これを学年ごとにまとめたいのです。6年生の「５」は3人、6年生の「４」は2人・・・という風にです。
IF関数とか使えばいいのでしょうか？？？
どなたか、分かる方、教えてください。よろしくお願いします。

回答No.6

words_oono

No.5です。

No.5の回答後に、質問の意図とは異なる回答を投稿してしまったことに気付き運営に削除依頼をしましたが、「利用規約に反する投稿以外の削除は行っておりません」と返答がありましたので、注意のため新規投稿にてその旨を投稿しておきます。失礼しました。

回答No.5

words_oono

質問のケースだと、SUMIF関数を使って集計することもできます。

この場合、入力する表の設計をあらかじめ変更しておいた方がよいでしょう。Excelでは、ワークシート関数を使うにしろVBAでプログラムを組むにしろ、ベースとなる表を考えて設計し問題を簡単にすることが重要です。

以下は一例です（「入力表」シートと「集計」シートに分かれていると想定します）。

---
【作業1】「入力表」シートを設計する
(1) D20に「学年」という見出しを作ります
(2) E20に「様子」という見出しを作ります

質問に記載された「たとえ」の数値を見出し以下のセルに入力すると次のようなリストが完成するイメージです。
学年 ｜ 様子
6　　｜4
4　　｜5

---
【作業2】「集計」シートを設計する
(1) セルA1に「学年」という見出しを作ります
(2) セルA2からA7までに、学年に対応する「1」から「6」の数値を入力します
(3) セルB1に「様子」という見出しを作ります
(4) セルD1に「シート名」という見出しを作ります
(5) セルE1に「1」を入力、続けてF1に「2」、G1に「3」、H1に「4」、I1に「5」J1に「6」を入力します

---
【作業3】「集計」シートに関数を入力する
(1) セルE2に次の関数を入力します
=IF($D2="", "", IF(ISERROR(INDIRECT(CONCATENATE("'",$D2,"'","!A1"))), INDIRECT(CONCATENATE("'",$D2,"'","!A1")), SUMIF(INDIRECT(CONCATENATE("'",$D2,"'","!D21:D70")),E$1,INDIRECT(CONCATENATE("'",$D2,"'";"!E21:E70")))))

(2) (1)の関数を、オートフィル機能などでJ列の51行目までコピーします（行は任意に指定可能です）

(3) B列に次の関数を入力します
2行目：=SUM(E$2:E$51)
3行目：=SUM(F$2:F$51)
4行目：=SUM(G$2:G$51)
5行目：=SUM(H$2:H$51)
6行目：=SUM(I$2:I$51)
7行目：=SUM(J$2:J$51)

---
■使い方（値の入力→集計）
「入力表」シートのシート名が「入力表_A」「入力表_B」となっていると想定します。実際のシート名は任意につけることができますが重複しないようにしてください。

<値の入力>
(1)「入力表」シートのセルD21以下（D21, D22, D23...）に「学年」を入力します（保護者が入力）
(2)「入力表」シートのセルE21以下（E21, E22, E23...）に「様子」を入力します（保護者が入力）
<集計>
(3)「集計」シートのセルD2以下に（D2, D3, D4...）に集計の対象としたいシート名を入力します
(4)「集計」シートのセルB2からB7に集計結果が表示されます

なお、添付画像は集計する「入力表」シートの値を以下にした際の「集計」シートをキャプチャしたものです。
●「入力表_A」シート
学年 ｜ 様子
6　　｜4
5　　｜3
●「入力表_B」シート
学年 ｜ 様子
1　　｜5
3　　｜2

回答No.4

mt2015

アンケートは
・1枚ごと別シートになっているの？
・D列、E列、……と横に続くの？
・それとも1枚ごと別ブック（シート1枚のみのブック）？

この辺が判らないと詳細な回答がつかないと思います。

回答No.3

xpz3

どのような表になっているかわかりませんが、学年と様子のセルを列で別けて「countifs」関数を用いれば簡単です。

例えば「A列は学年を入力する列」、「B列は様子を入力する列」とした場合は下記の式を用いる事で算出可能です。
下記は6年生の「５」をカウントする関数です。
=countifs(A:A,6,B:B,5)

※countifsは複数の指定した条件を満たすものをカウントする関数です。
※同じ列に「学年」と「様子」がある状態ではカウント出来ませんのでご注意ください。

以上、参考にしてみて下さい。

回答No.2

Prome_Lin

本来は、ExcelのVBAで組むべきですが、
私、VBAは不得意ですので、
「VBScript」で組んでみました。

以下のプログラムを、テキストエディタかメモ帳に貼り付け、
全角２つのスペースを１つのタブに一括置換します。

メモ帳の場合、「Ctrl＋h」で置換の窓を出します。

次に、上の窓に全角スペース２つを入力します。

テキストエディタなどですと、「タブ」を「\t」で表現できますが、
メモ帳では、直接タブを入力できないので、
メモ帳内の一番下の何もない行で１回、タブキーを押し、
そのタブを「Ctrl＋x」して、先ほどの置換の窓の下のところで
「Ctrl＋v」をして、タブを入力し、
「すべてを置換」ボタンをクリックします。

「ファイル」→「名前を付けて保存」から
適当な名前を付けますが、その際、
拡張子は必ず、「.vbs」でなければなりません。
（名前は漢字を含み、何でも構いませんがUnicode文字はダメです）

適当なフォルダを作り、目的のエクセルファイルと
「～.vbs」を放り込みます。

その状態で「～.vbs」ファイルをダブルクリックすると
結果がエクセルファイルに書き込まれ、終了し、
「Finished!」と画面に表示しますので、
「OK」を押して、エクセルファイルを見てみてください。

Option Explicit
Dim a, b, c(6, 5), d, e, i, j, r, v, w, x, y, z
Set v = CreateObject("Scripting.FileSystemObject")
Set w = v.GetFolder(".")
Set x = CreateObject("Excel.Application")
x.Application.DisplayAlerts = False
x.Visible = False
For Each a In w.Files
　　b = LCase(v.GetExtensionName(a.Name))
　　If b = "xls" or b = "xlsx" Then
　　　　Set y = x.Workbooks.Open(w & "\" & a.Name)
　　　　Set z = y.Worksheets(1)
　　　　r = z.Range("D1").End(-4121).Row
　　　　For i = 1 to 3
　　　　　　For j = 2 to r
　　　　　　　　d = z.Cells(j, i * 2 + 2).Value
　　　　　　　　If d <> "" Then
　　　　　　　　　　e = z.Cells(j, i * 2 + 3).Value
　　　　　　　　　　c(d, e) = c(d, e) + 1
　　　　　　　　End IF
　　　　　　Next
　　　　Next
　　　　For i = 1 to 6
　　　　　　For j = 1 to 5
　　　　　　　　z.Cells(i + 35, j * 4 - 3).Value = CStr(i) & "年生"
　　　　　　　　z.Cells(i + 35, j * 4 - 2).Value = "様子" & Cstr(j)
　　　　　　　　z.Cells(i + 35, j * 4 - 1).Value = c(i, j)
　　　　　　Next
　　　　Next
　　　　y.SaveAs(w & "\" & a.Name)
　　　　y.Close
　　　　Set z = Nothing
　　　　Set y = Nothing
　　End If
Next
x.Quit
Set v = Nothing
Set w = Nothing
Set x = Nothing
MsgBox("Finished!")

回答No.1

angel2015

countif関数を使用すれば
指定したセルに特定の数字が入っている場合のみ、カウントして合計を出すことが出来ます