Excel OFFSET and COUNTA functions
- Learn how to use Excel OFFSET and COUNTA functions to create dynamic summary tables.
- Discover how to copy Print Areas from multiple sheets and paste them in a specific cell.
- Understand the different ways to reference data using the VLOOKUP function.
- ベストアンサー
Excel OFFSET関数とCOUNTA関数
A Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ Ｉ 1 ＡＢＣ商会 合計 9,200 2 3 4 連番 出荷日 型 部品代 製品代 合計 5 1 10 aaa 1,000 1,850 2,850 6 2 12 bbb 650 900 1,550 7 3 18 ccc 800 2,000 2,800 8 4 30 ddd 500 1,500 2,000 9 10 合計 2,950 6,250 9,200 契約者毎にA1：Ｆ10ような集計表を作成しており、5行目以降行は追加されていくので、 可変の表になります。 各契約者集計シートのPrint Areaをコピーし、Ａ1セルに貼るとＧ1、Ｈ1、Ｉ１に契約者名と 合計がABC商会 合計9,200と表示できる上記のような表になるようにしたいと思っています。 関数で対応したいのですがうまくいきません。 =VLOOKUP(H1,OFFSET(C4,0,0,COUNTA(C:C)-4,4),2）ではエラーにはなりませんが、合計を 表示できませんでした。 また、他の契約者集計シートのPrint AreaをＡ1セルに貼ると エラーになります。 どうかご教示お願いいたします。
- o_poipoi
- お礼率33% (1/3)
- その他MS Office製品
- 回答数3
- ありがとう数1
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
方法１： Ｃ列に「合計」と記入してある行のＦ列の数字を参照してくるには =VLOOKUP("合計",C:F,4,FALSE) と記入します。 ＃初心者さんから，VLOOKUP関数についての「よくある勘違い」の回答が寄せられています。 「昇順で並べ替え云々」というのは，全く関係有りませんので，間違えないように注意して下さい。 ＃言わずもがなですがこの方法は，Ｃ列に「合計」とそもそも書いてないなら使えません。色々と「実は書いてないこと」があるという事ですが，ホントはどうなんですか。 方法２： ＞Ｆ10の金額はＦ5：Ｆ8の合計を四捨五入しております。 このように「質問は例えばで書いたんで，ホントにヤリタイ事はずいぶんと違っています」では，これではまっとーなやり方など回答で得られるハズもありませんよ。 それならこちらも，てきとーに思いつきで回答するだけになっちゃいます。出来るとか出来ないとかはあなたが勝手に判断して下さい。 =SUMIF(A:A,"",F:F) 方法３： Ｆ１０の合計セルを，SUM関数で合計するのをやめて =ROUND(SUBTOTAL(9,F1:F9),0) のように修正します Ｉ１には =ROUND(SUBTOTAL(9,F:F),0) のようにします。
その他の回答 (2)
- Nouble
- ベストアンサー率18% (330/1783)
こういうことですか？ 何行目にあるかは解らないが、Ｄ列に「合計」と記載されているセルがある その行の横手、Ｆ列の値を読み出したい。 こういうのは如何でしょうか？ (エラー処理は省きます) =OFFSET($E$3,SUMPRODUCT(($C:$C="合計")*ROW($C:$C)-1),1,1,1) VLOOKUP構文は昇順でないと確か駄目だったはずです。 …よね？ …(違ったかな？) しかし順位付けが「型」の部分と「合計」という文字列では 上手くいかなかったように思えます。 こういった場合 VLOOKUP,HLOOKUP,MATCHなどは 使えない公算が高いです。 (とんちんかんなこと云ってたら済みません) そんな時は １つ１つセルの値を検証する式を立てて、 マッチングしたセルが何処にあったか を元に、参照式を 作ってやると良いでしょう。 ところで、 =ROUND(SUMPRODUCT(INDEX(OFFSET($A$3,1,MATCH("合計",INDEX($4:$4,,),0),MATCH("",INDEX($F$5:$F$65530,,),0)-1,1),,)),-2) て、駄目ですかね？ お役に立てていたならば幸いです
- keithin
- ベストアンサー率66% (5278/7940)
「ＡＢＣ商会」がどこから出てくる（Ａ：Ｆ列のどこにある）のか不明ですが、少なくとも今欲しい合計の値は Ｉ１： =SUM(F:F)/2 で計算できます。
補足
ありがとうございます！ 実は金額は整数ではありませんのでＦ10の金額はＦ5：Ｆ8の合計を四捨五入しております。 Ｆ10の数字をそのままＩ１セルに表示することは無理でしょうか・・・・？
関連するQ&A
- エクセル関数
エクセル2003関数での質問ですが、 ある売上の集計表を作りたいと思っています。 別シートに行ごとに「契約件名」「契約日」「契約金」を入れています。 例えば１行目に「Ａ（契約件名）」「2008/4/1」「\10000」 ２行目に「Ａ（契約件名）」「2008/4/2」「\10000」 ３行目に「Ｂ（契約件名）」「2008/4/2」「\10000」 ４行目に「Ａ（契約件名）」「2008/5/2」「\10000」となっており、 集計表で、４月中にＡとういう契約件名の契約金合計を出したいのですが、sumifで組もうとしたところ勉強不足のせいか、二つ以上（契約件名と契約日）の条件でやろうとしても上手くいきません。 どうすれば良いのでしょうか？できれば月毎に分けないやり方があれば助かります。 それと、ＶＢＡを用いないやりかたでお願いします。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセルの関数について
Windows Xp エクセル2003を使用しています。 業務で使用している集計表ファイルがあり、別ファイルにデータを反映させたいのです。 集計表のセルA1を検索値としセルB1とセルB2の値を返す関数はないでしょうか？ 集計表のつくりは約1000行あり、セルA3を検索値としてセルB3の値を返す行もあります。 セルA5を検索値とし、B5、B6、B7、C5,C6の値を返す行など様々です。 こういったデータがランダムで表になっています。一つの検索値に対して、検索値の水平方向はvlookup関数で表示出来たのですが、 一つの検索値で2つの値を返す関数が分からず、困っています。 １行下や２行下を検索する関数も参考にしたのですが、集計表のつくりがランダムの為、検索してほしくないものまで表示されてしまうなど、ウマクできませんでした。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- INDIRECT関数が上手く使えません。
集計シートＦ334セルに、「=SUMIF(貼付!E$2:E$300,A334,貼付!K$2:K$300)」と 入力して、貼付シートのE2:E300の範囲から、集計シートA334の値を探して、 貼付シートのK2:K300の値を合計して、集計シートＦ334セルに表示させています。 集計シートF338セルに「K」と入れて、集計シートI338セルに「=F338&2&":"&F338&300」と 入力して、集計シートI338に「K2:K300」と表示させています。 貼付シートで必要な列が、K列からJ列・H列など月ごとに変わるため、 INDIRECT関数を使用して、集計シートF338セルに入力した値から集計シートF334セルの 値を更新しようと思っています。 そこで、集計シートＦ334セルの値を次のように変更しましたが、求めている結果が得られません。 0と表示されます。どこが誤っているのか、教えていただけないでしょうか。 「=SUMIF(貼付!E$2:E$300,集計!A334,INDIRECT("集計!"&I$338))」
- ベストアンサー
- オフィス系ソフト
- Excel - ちょっとややこしい値の引き方
宜しくお願いします。 文字で説明するのが難しいのですが、例えば。 Sheet1が【集計表】、Sheet2が【データ】（数字で入力）というブックがあるとしまして、【データ】から【集計表】に、曜日ごとの合計値を引きたいとします。 -------------------------------------------- Sun Mon Tue Wed Thu Fri Sat A B C -------------------------------------------- こんな表になります。 そしてA,B,Cは任意に入力できるようにして、例えばAであれば、【データ】シート上の[A1：C10]というように、引いてくる元の範囲を可変にするとします。ABCに対する範囲は固定で、ABCの横に並んだ状態であらかじめ用意されているとします。 -------------------------------------------- A A1:C10 B D1:F10 C G1:I10 Sun Mon Tue Wed Thu Fri Sat □ □ □ ↑任意のアルファベットを入れます -------------------------------------------- こういう状態です。 ここで、例えばCの項目の、日曜日の合計を出そうと思ったら、範囲はINDIRECTか何かで引くとして、SUMIF？SUMPRODUCT？どのようにすれば良いでしょうか。色々試してみたのですが、何故かどれも0になってしまいまして。 （SunのセルをE2とします）
- ベストアンサー
- その他MS Office製品
- Excelで統合の機能を使いたい
Excelで統合の機能を使ってデータを集計したいのです。 例えば、Aさんの売上表をsheet1に作成します。 売上表は、項目やセル位置に若干の違いはあるものの、 似たような作りの表なので、 Aさんのシートを元にして、シートコピーを行い、 コピーしたシートに、Bさんの売上表、Cさんの売上表を作成しました。 そして、集計するためのシートにて、統合を選択しました。 集計方法を「合計」、 統合元範囲をそれぞれ「Aさんのシートにある表範囲」 「Bさんのシートにある表範囲」「Cさんのシートにある表範囲」を追加し、 統合の基準で「上端行」「左端列」にチェックを入れ、OKを押しました。 しかしその結果、合計行が2つでてしまい、正しく集計が行えませんでした。 どこか、やり方が間違っているのでしょうか？ 試しに、シートコピーを行わないで各シートごとに表を1から作成し、 統合を実行したところ、ちゃんと合計行も1つで、正しい集計結果が得られました。 もしかして、統合の時には、表をコピーしてはいけないのでしょうか？ 以前にも、1から表を作った場合と、シートコピーをした場合とで、 集計結果に、「項目の並び順の違い」がでたことはあったのですが、 そのときは並び順だけが違い、集計結果はあっていたので、 特に気にしていませんでした。 なぜ、こういう結果になってしまうのか、 どなたか、ご存知の方いらっしゃいましたら、 アドバイスいただけたらと思います。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセルの関数について
エクセルの関数について教えて頂きたいです。 業務でエクセル（2000）で作成してある集計表があります。 その集計表の数値を別BOOKに反映させたいのです。 セルA1を検索値として、セルB1とセルB2の値を返す関数はありますでしょうか？ 集計表の行数は約500行、並びはランダムでセルC1を検索値としてセルC2の値を返す 箇所もあります。一つの値を検索値として二つの値を返すことが出来ずに困っています・・・ 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルで３次元の表を作りたい
１シート上に複数の２次元の表（通常のエクセルの表のことです）がたとえば３個有り（この表はそれぞれ同じ行・列をもつ同じ形式の表です）、 その３個の表の対応するセルの内容を合計の表として、同一シート内に作りたいのです。 １個の表に数量を入力すると、合計の表にも集計されるようにする。 集計の計算式は簡単ですが、表の個数が多くなると１個１個式を書くのは時間が掛かるので、何かテクニックで解決できないか？が質問の要旨です。 添付画像は支社A～Cが、その３個の表を示します。 作りたいのは黄色で示した表です。それぞれ支社・商品・入荷数毎の対応するセルの合計を全体の集計表としたい。 解りにくいと思いますが、宜しくお願いします。
- ベストアンサー
- その他MS Office製品
- エクセルの日にちごとのアンケート集計の関数について
シート名（アンケート） A列：日付 （12/1 12/2 など） F列：分類 （電話 パソコン など） シート名（合計） A13には 12/1と記入されており 13行は 12/1の分類の集計を入力致します。 14行は 12/2の分類の集計を入力致します。 その際、13行のE（E13）には12/1の 電話の集計 13行のF（F13）には12/1の パソコンの集計 14行のE（E14)には12/2の 電話の集計 を入力する場合、どのような関数を使えばよろしいのでしょうか。 よろしくお願いします。 アンケートと合計は 同じBOOKのエクセルの中にあります。 ご教示いただけますでしょうか。
- ベストアンサー
- オフィス系ソフト
お礼
すごいです！！ ３つの方法すべてできました。 今後すべての契約者シートを方法３に修正することにしました。 どうもありがとうございました！