今だけ人気マンガ100円レンタル特集♪

タイトルの件、質問いたします。

下記の図をご覧ください。

図1より、E2に、1月度の成約件数が、関数により、カウントされています

関数

=SUMPRODUCT((YEAR(A:A)=2013)*(MONTH(A:A)=1)*(B:B="月曜日")*(C:C="成約"))

です。

しかし
図2のように
表に、項目【日付、曜日、結果】という、項目をつけると、関数がエラーを表示します。

項目をつけても、上記の指定【A:A】列指定して、カウントできるようにはできないでしょうか?

以上、ご存知の方、いらっしゃいましたら、宜しくお願いします。

「【エクセル】列指定【A:A】した際の、関」の質問画像

このQ&Aに関連する最新のQ&A

SUMPRODUCT」に関するQ&A: SUMPRODUCT関数について

A 回答 (5件)

 次の様な関数にされると宜しいかと思います。



=SUMPRODUCT((YEAR(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(9E+99,$A:$A)))=2013)*(MONTH(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,MATCH(9E+99,$A:$A)))=1)*(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(9E+99,$A:$A))="月曜日")*(INDEX($C:$C,ROW($A$1)+1):INDEX($C:$C,MATCH(9E+99,$A:$A))="成約"))

或いは

=SUMPRODUCT(ISNUMBER(1/(YEAR($A:$A)=2013)/(MONTH($A:$A)=1))*($B:$B="月曜日")*($C:$C="成約"))
    • good
    • 0

たとえば


=SUMPRODUCT((TEXT(A:A,"yyyymm;;;")="201301")*(B:B="月曜日")*(C:C="成約"))
といった工夫で計算できます。
もちろん重い(それはあなたが最初に成功した式でも同じですけど)ですけどね。

無駄な列:列をやめて1行目を計算範囲から外すのは当然の措置として,しかしデータ中にイレギュラーに文字列が出てくる(たとえば数式で""を計算しているといった)場合の回避措置としても有効です。
    • good
    • 0

こんにちは!


SUMPRODUCT関数は配列数式になりますので、列全体を範囲指定してしまうと
PCにかなりの負担をかけ、計算速度も極端に遅くなります。

SUMPRODUCT関数を使用する場合は範囲を明確に指定してやる方がよいと思います。

さて、SUMPRODUCT関数の「関数の引数ダイアログボックス」を表示させ
配列1の部分に
MONTH(A1:A100)=1
と入力してみてください。
この時点で範囲内データが数値以外の行が#VALUE!と表示されてしまいます。
すなわち、Excelがシリアル値だと判断できないデータがあるとエラーになってしまいます。

解決方法としては
=SUMPRODUCT((MONTH(A2:A100)=1)*(B2:B100="月曜日")*(C2:C100="成約"))
といった感じにするしかないと思います。m(_ _)m
    • good
    • 0

SUMPRODUCT関数は一般には範囲を指定して使うものです。

列指定ですと計算に時間がかかりますし、今回のようにA列に日付でないデータが入力してある場合にはYEAR(A1:A1)はエラーになりますので当然エラーになってしまいます。
日付の項目が1行目に入っているのですから式は列の指定ではなく下記のように範囲を指定することが必要です。

=SUMPRODUCT((YEAR(A2:A100)=2013)*(MONTH(A2:A100)=1)*(B2:B100="月曜日")*(C2:C100="成約"))
    • good
    • 0

MONTH関数では判断できない文字列『日付』が含まれているからでしょう。



A:AでA列全部となるので、
A2:A○と範囲を指定すればできると思います。
    • good
    • 1

このQ&Aに関連する人気のQ&A

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

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

このQ&Aを見た人が検索しているワード

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

Qエクセル関数で(A:A)が使えない?

=SUMPRODUCT((A:A=C1)*(B:B=D1),C:C) とやったところエラーになりました。
そこで、=SUMPRODUCT((A1:A567=C1)*(B1:B567=D1),C1:C567) に書き直したら出来ました。
関数によって(A:A)が使えない場合があるのでしょうか。

毎月データ数(行数)が変動するので、(A:A)が使えないのなら、(A1:A30000)としようかなとは思っているのですが、疑問に思いましたので、質問させていただきました。

Aベストアンサー

こんにちは。

ヘルプの「仕様」の中に、

#ワークシートの配列   使用可能メモリに依存。
#また、配列では列全体を参照することはできません。たとえば、配列で列全体 C:C や範囲 #C1:C65536 を参照することはできません。

となっていますので、配列数式を用いるときは、列全体を指定することが出来ません。
なお、詳しく、この挙動を調べてみると、空白の部分は配列数式では認識はしているのですが、メモリの負担にはなっていないようです。本当に、配列数式が自由に使えるようになったのは、Excel2002以上からです。それまでは、他にも配列個数の制限が存在していました。(Excel2000の仕様は、Excel2002と同じように書かれてはいましたが、それは違っていたようです。)

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Q=SUMPRODUCTで、縦列全てを対象とする方法

質問させていただきます。
「SUMPRODUCT」を使って集計表を作成しておりますが、
以下の設定が可能かどうかお教えいただければありがたいです。

現在集計表には、=SUMPRODUCT((A1:A65500="入場者数")*(B1:B65500="本日"))
と設定されております。
しかし、AとB列は共に全てを対象としたいと考えておりますので、
出来れば65500という数字を入れずにAとB列を一括設定できないかと思っております。
例えば、「COUNTIF」の場合は、=COUNTIF(A:A,"入場者数")と入力すれば、
A列全てが対象となります。
他の掲示板で質問してみましたが、今のところこの件に関しては回答をいただけておりません。
もし設定方法があれば是非お教えください。
よろしくお願いいたします。

Aベストアンサー

残念ですが数式の仕様上できません。
一度 $A$1:$A$65536で試しましたが$A:$Aに置換されNGでした。
現在は1行目を項目名(列の見出し)として
$A$2:$A$65536を「挿入」「名前」「定義」で名前付けして使うようにしています。

EXCEL2007ならCOUNTIFS関数(複数条件でのCOUNTIF)
=COUNTIFS(A:A,"入場者数",B:B,"本日")
で計算可能です。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

Qセル番号を返す関数

エクセルで、セル番号を返す関数はありますか??
セルの内容ではなくて、番号です。(A1、B3など)
調べてみましたがなかなか見つからなかったので、
わかる方教えてください。
宜しくお願い致します。

Aベストアンサー

文字として"C4"を返す。
=ADDRESS(ROW(C4),COLUMN(C4),4)

式を入力したセルの位置を文字列で返す。
=ADDRESS(ROW(),COLUMN(),4)

ADDRESSの3つ目の引数を変えると
$C$4
$C4
C$4
C4
の4つの形に出来ます。

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで特定の行を抽出させる方法について。

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

     A     B    C     D  
 1  名前   日付  場所   時間
 2  Aさん  10/7  工場1   8.0
 3  Bさん  10/7  工場1   8.0
 4  Cさん  10/7  工場2   4.0
 5  Cさん  10/7  工場1   8.0
 6  Aさん  10/8  事務所   8.0
 7  Cさん  10/8  工場2   8.0
 8  Aさん  10/9  事務所   4.0

以上の表から別のシート2にAさんだけの行を抽出するには、どのようにすれば良いのでしょうか?
INDEXやMATCH・LOOKUPを使っていろいろ試したのですがイマイチうまく行かないもので・・・。

因みに、シート2のイメージは

      A     B     C  
 1   名前  :  Aさん 
 2
 3   日付   場所   時間
 4   10/7   工場1    8.0
 5   10/8   事務所    8.0
 6   10/9   事務所   4.0

このような感じです。

よろしくお願いします。

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

     A     B    C     D  
 1  名前   日付  場所   時間
 2  Aさん  10/7  工場1   8.0
 3  Bさん  10/7  工場1   8.0
 4  Cさん  10/7  工場2   4.0
 5  Cさん  10/7  工場1   8.0
 6  Aさん  10/8  事務所   8.0
 7  Cさん  10/8  工場2   8.0
 8  Aさん  10/9  事務所   4.0

以上の表から別のシート2にAさんだけの行を抽出するには、どのように...続きを読む

Aベストアンサー

こんにちは!

極端にデータ量が多い場合はおススメできませんが・・・
一気に表示する方法です。

↓の画像で右側がSheet2とします。
Excel2007以降のバージョンをお使いだとして・・・

Sheet2のA4セルに
=IF($B$1="","",IFERROR(INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(A$1:A$1000)),ROW(A1))),""))
これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定!
この画面から数式をコピー&ペーストする場合は
上記数式をドラッグ&コピー → Sheet2のA4セルを選択 → 数式バー内に貼り付け
→ そのまま(編集可能なまま)Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
これをC4セルまでコピー!

A4セルの表示形式は「日付」にし、A4~C4セルを範囲指定 → C4セルのフィルハンドルで下へコピー!

これで画像のような感じになります。m(_ _)m꜊

こんにちは!

極端にデータ量が多い場合はおススメできませんが・・・
一気に表示する方法です。

↓の画像で右側がSheet2とします。
Excel2007以降のバージョンをお使いだとして・・・

Sheet2のA4セルに
=IF($B$1="","",IFERROR(INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(A$1:A$1000)),ROW(A1))),""))
これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定!
この画面から数式をコピー&ペーストする場合は
上記数式をドラッグ&コピー → Sheet2のA4セルを選択 → 数式バ...続きを読む


このカテゴリの人気Q&Aランキング