[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『sumifsで外部ファイルを参照できない』(さとし)
sumifsは選択範囲として、外部ファイルを参照できない仕様だということがわかりました。
sumifなら、配列数式を使えば、外部ファイルを参照するのと同じようなことができるようですが、sumifsも配列数式でなくてもいいので、代替手段はないでしょうか?
sumifsではなくても、同じ機能があるほかの関数で、選択範囲に外部ファイルを参照で切ればそれでも問題ありません。
最終手段として、作業用のシートを作って、シートを非表示にしようと思っていますが、あまりにも回りくどいのでやりたくありません。
マクロも使わずに、関数でやりたいと思っています。
よろしくお願いします。
< 使用 Excel:unknown、使用 OS:Windows10 >
こんばんわ。
SUMIFSで別のExcelブックを参照出来ますよ。 どのような手順で行われて出来なかったのでしょうか?
(sy) 2016/09/22(木) 20:10
参照先のブックが閉じた状態だと #VALUE! エラーになるってこと? ブックを開けば使えると思いますけど、それではダメなんですかね?
どうしても参照先のブックを閉じた状態で使いたい、ということだとして、 配列数式でならできるとわかってるのに使わないのはナゼ? 理由を教えてください。
ちなみに SUMIFS だと具体的にどんな数式? (笑) 2016/09/22(木) 21:01
> sumifなら、配列数式を使えば
こっちは SUMIF だった。
配列数式でもいいんですかね?
とにかくその SUMIFS の式を提示してもらわないことには 話は始まりません。 (笑) 2016/09/22(木) 21:16
SUMIFSは普通のSUMIFSだと思います。相対パスで参照しています。ちょっとミスがあるかもしれませんが、大体、以下のような簡単なSUMIFSです。
SUMIFでもSUMIFSでも、参照元のブックがたくさんあるので、必ずブックを閉じた状態で参照できないと、実用的に使えないと思います。
マクロは使わない方針ですが、SUMIFSでなくても同じことができれば、ほかの関数でもほかの数式でも、まったく問題ありません。
book.xlsx
A 1 =SUMIFS([sample.csv]sample!$A$1:$A$10,[sample.csv]sample!$B$1:$B$10,"abc")
sample.csv
A B 1 1 abc 2 2 efg 3 3 hij 4 4 klm 5 5 nop 6 6 qrls 7 7 tuz 8 8 vws 9 9 yz
(さとし) 2016/09/23(金) 06:06
ワークシート関数での外部参照は相手ファイルがEXCELファイルのみ。
CSVファイルは通常EXCELに関連付けされているがただのテキストファイル。
ワークシート関数でCSVファイルの参照はできない。 (ねむねむ) 2016/09/23(金) 09:04
補足。 同じExcelでCSVファイルを開いている場合に =SUMIFS([sample.csv]sample!$A$1:$A$10,[sample.csv]sample!$B$1:$B$10,"abc")
で値が取れるのはすでにExcelで開かれているので扱いとしてはCSVではなくExcelファイルとなっているため。
(ねむねむ) 2016/09/23(金) 10:05
ワークシート関数での外部参照は相手ファイルがEXCELファイルのみ。
SUMIFだけではなく、SUMIFSも使う必要があるのですが、EXCELファイルであっても、外部ファイルを閉じた状態では、SUMIFSは外部参照できないのですが、どこか私のやり方に、ミスがあるのでしょうか?それとも仕様なのでしょうか?
CSVファイルは通常EXCELに関連付けされているがただのテキストファイル。
で値が取れるのはすでにExcelで開かれているので扱いとしてはCSVではなくExcelファイルとなっているため。
勉強になります。
(さとし) 2016/09/23(金) 10:35
安定感がない方法のように思えてきました。
(さとし) 2016/09/23(金) 10:40
SUMIFS関数、COUNTIFS関数は基本的にSUMIF関数、COUNTIF関数と同じ制約を持つ。
あと、式をよく見ていなかったが =SUMIFS([sample.csv]sample!$A$1:$A$10,[sample.csv]sample!$B$1:$B$10,"abc") であればSUMIFS関数の必要はなくSUMIF関数で十分ではないのか?
SUMIFS関数が必要なのはAND条件が二つ以上の場合、例えば =SUMIFS([sample.csv]sample!$A$1:$A$10,[sample.csv]sample!$B$1:$B$10,"abc",[sample.csv]sample!$C$1:$C$10,"def") のようにB列がabcでD列がdefの物のA列の合計といった場合。
で、これを配列数式にすると =SUMPRODUCT([sample.csv]sample!$A$1:$A$10*([sample.csv]sample!$B$1:$B$10="abc")*([sample.csv]sample!$C$1:$C$10="def")) となる。 (実際には[sample.csv]部分はフルパスで) 追記 あっ、参照相手はEXCELブックで。 (ねむねむ) 2016/09/23(金) 10:49
SUMIFS関数、COUNTIFS関数は基本的にSUMIF関数、COUNTIF関数と同じ制約を持つ。
SUMIFS関数、COUNTIFS関数、SUMIF関数、COUNTIF関数は、ファイルを閉じた状態では、ファイルの種類にかかわらず、外部参照できないと思ってよいでしょうか?この点はきちんと認識しておきたいです。
であればSUMIFS関数の必要はなくSUMIF関数で十分ではないのか?
あくまでも、サンプルとしてなので、どうしても、SUMIFS関数が必要な場合もあります。
=SUMPRODUCT([sample.csv]sample!$A$1:$A$10*([sample.csv]sample!$B$1:$B$10="abc")*([sample.csv]sample!$C$1:$C$10="def"))・・・?@とします。
このような配列関数は、初心者の自分には難しいというだけではなく、そもそも論として、VBAが使えない人でも使っているような普通の関数では、外部ファイルの参照は、やらないほうがよいのでしょうか? 安定感がないので、やらないほうがよいと書いてあるページが、いくつかありました。
(実際には[sample.csv]部分はフルパスで)
ファイルをいろいろなパソコンに移動するので、フルパスではなく、INDIRECT関数を使って、相対パスで書いていました。INDIRECT関数を使わないと、相対パスにしても、自動でフルパスに変換されてしまうためです。
相対パスの場合は、?@に書いていただいた式を使うのは、使えなかかったり、避けたほうがよいでしょうか?
(さとし) 2016/09/23(金) 12:02
Excelの関数のことで教えてください。Sumproduct関数で外部ファ... - Yahoo!知恵袋
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11143127801
総合的な判断として、今回の場合、外部参照するのではなく、ブック内に作業用のシートを作ったほうがよいでしょうか?
外部ファイルが何百何千とあるなら、現実的に、外部参照するしかないと思いますが、10個前後なので、作業用のシートを10個前後作っても、少し面倒くさい程度です。
(さとし) 2016/09/23(金) 12:09
とりあえずINDIRECT関数についてだけ。
ExcelのヘルプでINDIRECT関数を調べると
>参照文字列で他のブックを参照している場合 (外部参照)、そのブックを開いておく必要があります。 >参照先のブックを開いていない場合、INDIRECT 関数は、エラー値 #REF! を返します。 と書かれている。
(ねむねむ) 2016/09/23(金) 12:19
マクロを使うとしたら、どのような方針になりますか?もっと選択肢が広がりますか?
(さとし) 2016/09/24(土) 05:15
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.