はじめての親子ハイキングに挑戦!! >>

自分では何をしてもお手上げなので詳しい方に教えていただきたいです。
画像の様な表(①)があったとして、表(②)の条件を満たす場所ごとに人数を数えたいです。表(②)は上から順に優先順位が高い順です。
しかしその時に条件があり、1つの条件に当てはまった人は他の条件には当てはめてはならないというものです。
例えば優先順位が最も高いA31の場所一はAさん、Hさん、Oさんの3人が当てはまります。
そうなると場所一の残りはVさん1人ということになり、次に優先順位が高いA32には当てはまらないのでA33に入って残りは0ということになります。

上記の例えのように集計するにはどうすれば良いでしょうか。毎日変動する何百とあるデータを集計しなければならないため、知恵をお貸しいただきたいです。

拙い文章で伝わりにくいかもしれませんが、回答よろしくお願いします。

「エクセルについて」の質問画像

A 回答 (5件)

◆概要


I列~L列を作業列として、31~34行目の条件および重複防止から抽出します。
作業列に抽出した表からピボットテーブルを使って、②の表にする。
添付図参照です。データの配置も添付図のとおりです。

◆手順
1.I1~L1セルに条件の見出しを記入する。
2.I2セルに『=IF(AND(H2>=3,E2>=3),1,0)』を記入する。→31行目の条件
3.J2セルに『=IF(AND(G2>=2,E2>=3,I2=0),1,0)』を記入する。→32行目の条件と重複防止
4.K2セルに『=IF(AND(F2>=4,C2>=3,I2=0,J2=0),1,0)』を記入する。
5.L2セルに『=IF(AND(C2>=1,I2=0,J2=0,K2=0),1,0)』を記入する。
6.I2~L2を下方向にコピペする。
7.メニュー→挿入→ピボットテーブル とする。
8.データ範囲にB1~L27を選択して、OK。
9.『ピボットテーブルのフィールドリスト』のウインドウが表示される。
10.大きい箱にある『場所』『31』『32』『33』『34』にチェックを入れる。
11.行ラベルにある『場所』を列ラベルにドラッグして移動させる。
12.Σ値にある31~34をクリックして『値フィールドの設定』→『合計』に変更する。
13.ピボットテーブルで作った表を必要な場所にコピペして完成。


※当方が試したところ、手順13の表の『場所』(一~七)の並び順がずれました。
 同じことが起これば、必要に応じて並べ替えください。

※添付図のデータは、場所 一と二のみサンプルどおりに入力。三以降はでたらめです。
「エクセルについて」の回答画像4
    • good
    • 0
この回答へのお礼

googoo900さんがおっしゃられたように試してみると、無事できました!
ありがとうございました。

お礼日時:2019/04/22 16:19

もう少し 開けてられますか?

    • good
    • 0

いっぱつで算出できる夢のような(悪夢?)数式をご希望なら読み捨てて下さい。


①表のI列を作業列にして、次のような式を設定します。②の表では作業列の値を使用して、COUNTIFSで集計します。

=IF(AND(H2>=3,E2>=3),"甲",IF(AND(G2>=2,E2>=3),"乙","・・・"))
※2つ目の条件までしか実装していませんので、続きはご自分・・・。
    • good
    • 0
この回答へのお礼

申し訳ありません。
私が無知なばかりに、甲、乙が何なのか分かりませんでした;;
回答してくださってありがとうございました!

お礼日時:2019/04/22 16:21

横槍コメントで失礼します。



angkor_hさんへお願い、
》 …これらの関数が使えます。
そう仰るなら、場所が一の場合だけ(つまり、範囲 B31:B34 だけ)でも、当該関数を用いた式を是非示してみてください、参考までに。
    • good
    • 0

SUMIFS、SUMPRODUCT、これらの関数が使えます。

    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qエクセルの関数について

例年この時期に必ず苦労して困っています。質問させてください…
毎年毎年、販売個数を出すのに列を削除する方がいて、ミスが起きたり作り直しとか大変でとても困ってます。

列を削除してもSUMproductの関数がつかいたいのですが、

=SUMPRODUCT($C$3:(ADDRESS(3,(MATCH("枚数"),0)-1),1),C4:(ADDRESS(3,(MATCH("枚数"),0)-1),4)))

みたいな感じで考えて見たのですがエラーメッセージでお手上げです…

どうにか個数と書いたの手前の行までの掛け算をする数式ができないでしょうか

よろしくお願いします!

Aベストアンサー

又 訂正です、

=SUMPRODUCT(INDIRECT("C3:"&ADDRESS(3,MATCH("枚数",INDIRECT("C3:Z3"),0)+2)))

何度も 申し訳ない、
こっちですね。


そして応用編が、
=SUMPRODUCT(INDIRECT("C3:"&ADDRESS(3,IFERROR(MATCH("枚数",INDIRECT("C3:Z3"),0),COUNTA(INDIRECT("C3:Z3")))+2)))

此方は、
もし "枚数"記載列をも、
削除してしまっても、

まあ 取り敢えずは、
エラーには ならない、
式です。


確認くださいね。


後、
解説を ご所望に、
なられる場合は、

そう、
お申し付けくださいね。


追記、
いゃ〜、
楽しませて 頂きました、

久々に、
頭が 回る、
噛み応えの ある、
課題でした、

いい課題を 有り難うです。

QExcelです。 関数など、やり方を教えてください。 集会出来なくて困っています。

Excelです。
関数など、やり方を教えてください。
集会出来なくて困っています。

Aベストアンサー

あ、
済みません、
映像添付 忘れました。


お許しください。

Qエクセルでこんな事できますか?

添付エクセルのようにA列に任意の数字が歯抜けで入力したとき、その数字の入っているC列の同じ行(実際は、100行程ある)にだけ、数字の1を自動に入れたいのですが、、、詳しい方 教えてください。関数でも、マクロでも何でもかまいません。 よろしくお願いいたします。

Aベストアンサー

マクロならこうなります。

Sub TestSample1()
 With Range("A1", Cells(Rows.Count, 1).End(xlUp))
  .SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, 2).Value = 1
 End With
End Sub

Qエクセルでセルのデータがカタカナかどうか調べたい

セルA1の内容が、全角カタカナか半角カタカナか知りたいです。

エクセル関数又はマクロコマンドがあるはずだと信じて探していますが、見つかりません。TYPE関数では、セルの内容が数値か文字列かを調べられますが、その文字列がカタカナか英数字かなどを調べられません。ご存知の方はお教えください。

Aベストアンサー

元の 文字列が、
セルA6に あるとして、
セル内が 全て、
全角片仮名かは、
=SUMPRODUCT((CODE(MID($A$6,COLUMN(OFFSET($A$1,0,0,1,LEN($A$6))),1))=ROW($A$9506:$A$9590))+0)=LEN($A$6)

半角片仮名かは、
=SUMPRODUCT((CODE(MID($A$6,COLUMN(OFFSET($A$1,0,0,1,LEN($A$6))),1))=ROW($A$177:$A$211))+0)=LEN(A6)

で 判りますよ。

Q【エクセル】指定した日付に一番近い日付を抽出する関数について

エクセルの関数について質問です。


A____B_______
佐藤 2011/5/2
佐藤 2011/8/25
佐藤 2012/1/8
山田 2011/6/7
山田 2012/2/13
高橋 2010/7/10
高橋 2010/11/1
高橋 2010/12/5
高橋 2011/2/10

というデータから、




A____B________
佐藤 2011/5/1
佐藤 2011/9/1
山田 2011/6/1
山田 2011/8/1
高橋 2011/1/1
高橋 2011/2/1


のデータの日付に一番近い日付を
①のデータから抽出する関数を教えて頂きたいです。
指定日に一番近い日付を、
未来と過去から抽出する関数です。

分かりづらくて申し訳ございません。
ご教示願います。
よろしくお願いいたします。



iPhoneから送信

Aベストアンサー

もし、
D1に 入力された、
日と 同じ日を、
抜き出したいのなら、
此ですかね?


ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3CyTu_uDNnR5Xlwk


尚、
ファイルは 必ず、
エクセルで 一度、
開き、

ローカルに 別名保存を、
してくださいね、

別名保存でないと、
意味が 無いですよ。


そうすれば、
閲覧も、編集も、
可能に なると、
思います。

Qエクセル データの入力規制「リスト」でこんな事できますか?

エクセルでこんな事できますか?
並列したQ列 R列があり、
また、添付には表記されていませんが、
その隣にS列 T列があり、
Q列、S列は、「コード」という事で、
Q列は1,2,3,4,5,6,7,8,9,10
S列は11,12,13,14,15,16,17,18,19,20
です。
R列は、「材質」でQ列に対応し、添付ファイルのように10項目
T列も「材質」でS列に対応し、10項目あります。

R列 T列「材質」を見て Q37セルにコードを入力したく、
その時、ドロップダウンリストとして、R列、続けて、T列の項目を表示したいです。

データの入力規制「リスト」で、できそうかと思い、頑張っていたのですが、
うまくいかず困っています。
データの入力規制「リスト」にはこだわりませんが、
マクロはできるだけ使わず完成したく思います。
エクセル詳しいかたご教授よろしくお願いします。

Aベストアンサー

あー…
入力規則はあくまでも、データ入力を補助するための機能ですからリストと違う文字を反映させることはできませんよ。

例えばリストデータは、01りんご・02プリンのように作って置いて、コードを出す時に
=LEFT(Q37,2)*1
と入れておくとか?

Q大きいポイント順にデータを抽出したい

いつもお世話になっております。

元データのポイントの高い順に抽出先のC列、D列へデータを抽出したいです。
VLOOKUPとLARGE関数を使用したのですがうまくいきません。

何卒、ご教授願います。

★元データ

A列:支店コード
B列:支店名 
C列:ポイント

A列 B列   C列
1  ①支店  5
6  ②支店  6
5  ④支店  10

★抽出先

A列:作業列(元データの支店コード)
B列:順位(1位~30位まで)
C列:支店
D列:ポイント

A列 B列 C列  D列
1  1  ④支店 10
2  2  ②支店 6
3  3  ①支店 5

Aベストアンサー

No.5です。

重複データがあり、その合計で降順に表示したい!というコトですね。

一つの数式で出来るかどうか判りませんが、
↓の画像のように作業用の列を2列設けるのが一番簡単だと思います。

作業列1のE2セルに
=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B:B,B2,C:C),"")

作業列2のF2セルに
=IF(E2="","",COUNTIF(E:E,">"&E2)+COUNTIF(E$2:E2,E2))

という数式を入れ下へずぃ~~~!っとフィル&コピーしておきます。

Sheet2のB2セルに
=IFERROR(INDEX(Sheet1!B:B,MATCH(ROW(A1),Sheet1!$F:$F,0)),"")

C2セルに
=IF(B2="","",SUMIF(Sheet1!B:B,B2,Sheet1!C:C))

という数式を入れフィルハンドルで下へコピー!

これで画像のような感じになります。

※ Sheet2のC2セルは色々やり方があると思います。
例えば
=IFERROR(INDEX(Sheet1!E:E,MATCH(B2,Sheet1!B:B,0)),"")

でも大丈夫だと思います。m(_ _)m

No.5です。

重複データがあり、その合計で降順に表示したい!というコトですね。

一つの数式で出来るかどうか判りませんが、
↓の画像のように作業用の列を2列設けるのが一番簡単だと思います。

作業列1のE2セルに
=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B:B,B2,C:C),"")

作業列2のF2セルに
=IF(E2="","",COUNTIF(E:E,">"&E2)+COUNTIF(E$2:E2,E2))

という数式を入れ下へずぃ~~~!っとフィル&コピーしておきます。

Sheet2のB2セルに
=IFERROR(INDEX(Sheet1!B:B,MATCH(ROW(A1),Sheet1!$F:$F,0)),"")

C2セルに
=I...続きを読む

QEXCELでのシフト表作成

職場のシフト表を作るにあたり、下図のF列以降のA~Gの職員に、休日の出勤日はセルを青色にし、その代休日を黄色でで配色しています。また、各職員の出勤希望日を緑、休日希望日を赤で表示して、見た目に分かり易くして、シフトを作成しています。
 問題は、色を入れると同時に、青と緑で表現した休日の出勤シフトをD列に、平日の代休をE列に、A,B等の職員名を表示させたいのですが方法がわかりません。VBA等で上手くできないでしょうか?
よろしくお願いします!

Aベストアンサー

こんばんは!

お示しの画像のF~L列の色付けは手動で行うのですよね。

VBAでの一例です。
↓の画像のように少し配置を変えてみました。
D1~E2にF~L列に塗りつぶす色での各色分けをしておきます。
そしてその列の色と同じ色のセル ↓の画像では3行目 の氏名?を表示するようにしてみました。

Sub Sample1()
 Dim i As Long, j As Long
 Dim c As Range

  For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row
   Cells(i, "D").Resize(, 2).ClearContents
    For j = 6 To Cells(3, Columns.Count).End(xlToLeft).Column '//←F列~3行目最終列まで//
     If Cells(i, j).Interior.ColorIndex <> xlNone Then
      For Each c In Range("D1:E2")
       If Cells(i, j).Interior.Color = c.Interior.Color Then
        With Cells(i, c.Column)
         If .Value = "" Then
          .Value = Cells(3, j)
         Else
          .Value = .Value & "," & Cells(3, j)
         End If
        End With
       End If
      Next c
     End If
    Next j
  Next i
   Range("D:E").Columns.AutoFit
End Sub

※ データ変更があるたびにマクロを実行する必要があります。

※ お示しの画像と↓の画像は1行ずれていますので、注意してください。m(_ _)m

こんばんは!

お示しの画像のF~L列の色付けは手動で行うのですよね。

VBAでの一例です。
↓の画像のように少し配置を変えてみました。
D1~E2にF~L列に塗りつぶす色での各色分けをしておきます。
そしてその列の色と同じ色のセル ↓の画像では3行目 の氏名?を表示するようにしてみました。

Sub Sample1()
 Dim i As Long, j As Long
 Dim c As Range

  For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row
   Cells(i, "D").Resize(, 2).ClearContents
    For j = 6 To Cells(3, Columns.Cou...続きを読む

QExcel関数 賞味期限が何%経過したかを求める式

Excel関数 を教えてください!

賞味期限が何%経過しているかを求める式が作れません。

例 賞味期限 2020/07/01 (製造から未開封で30ヵ月)
この商品は、2019/01/23日時点では賞味期限は何%過ぎたことになるのでしょうか?

これが作れないと残業から解放されず、睡眠不足で辛いです。どうか助けてください。宜しくお願いします。

Aベストアンサー

A1のセルに製造日、B1のセルに賞味期限を日付で入れる(ただし、賞味期限は製造日からのカウントの場合)

例えばA1=2018/01/01、B2=2019/07/01として、
=(TODAY()-A1)/(B1-A1)
を計算させると、今日までで何割消化したかがでてきます。

Qexcelど素人です。 A列 B列 C列 D列 2 2 3 0 5 2 7 1 11 2 13 1

excelど素人です。

A列 B列 C列 D列
2 2
3 0
5 2
7 1
11 2
13 1

上で示したように、A列に表示されている素数を3で割ったとき、余りが0ならB列に、1ならC列に、2ならD列に表示されるような表を作りたいです。
何とかA列に素数を表示することはできましたが、それからは全く何をしたらよいか分かりません。どなたか教えて頂けませんか?

Aベストアンサー

MOD関数で余りを出し、それをIF関数で各列それぞれ余りが○なら表示、それ以外は空欄というように式を作ればいいかと。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング