Excelの関数offsetやaddressについての質問
- ExcelのSMALL関数を使用して、指定範囲内の最小値を取得しようとしていますが、抽出元のセルの6列左にある「名前」の値を抽出できません。
- offset関数の基準になるセルの番地と、抽出元のセルの番地を連結するために、ADDRESS関数を使用していますが、エラーが出ています。
- 他の方法でも構いませんが、Excelのoffset関数とaddress関数を使用して、指定範囲内の最小値とその番地を抽出する方法を教えてください。
- ベストアンサー
excelの関数(offsetやaddressについて)
excelの質問です。 現在C4からC8に、SMALL関数を使って、指定範囲中の1番目から5番目までの小ささの値を抽出しています。 そして、B4からB8に、C4からC8の抽出元のセルの6列左にあるセルの値「名前」を抽出しようとしているのですが、うまくできません。 offset関数の"基準"はセルの番地でないといけないようなので、 、値を返すSMALL関数は直接代入できません。 ネットで調べたら、抽出された最大値の番地を返す式 ADDRESS(SUMPRODUCT((MAX(K2:K75)=K2:K75)*ROW(K2:K75)),SUMPRODUCT((MIN(K2:K75)=K2:K75)*COLUMN(K2:K75)),4) を見つけたのですが、これを直接offset関数の"基準"に代入してもなぜかエラーが出てしまいます。 どうしたらうまくできるでしょうか。 全く上記に書いたやり方と違っても構いません。 よろしくお願いします。
- twyford-crescent
- お礼率71% (5/7)
- オフィス系ソフト
- 回答数2
- ありがとう数2
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
》 C4からC8に、…、指定範囲中の… その「指定範囲」を SpecifiedRange とすると、 C4: =SMALL(SpecifiedRange,ROW(A1)) B4: =OFFSET(INDIRECT(CELL("address",SpecifiedRange)),MATCH(C4,SpecifiedRange,0)-1,-6)
その他の回答 (1)
- imogasi
- ベストアンサー率27% (4737/17068)
B,C列のほかに＞指定範囲中の・・などの説明が出ているが、わかりにくい。具体的な模擬実例を挙げて質問すること。 ＞6列左にあるセル・・も突然出てきてわかりにくい。 私など、関数のほとんど回答に実例を挙げて説明している。 質問の場合はなおさら初めての他人に判るように実例でも挙げて書くこと。
お礼
すみません。 指定範囲は「K2:K75」 6列左は「Ｅ列」とすべきでした。
関連するQ&A
- エクセル2007：セルを参照する関数
Sheet１とSheet2があり、 Sheet2のC1＝Sheet1!B3 Sheet2のC2にSheet1のB6の値を入れたいです。 Sheeｔ2のC3にSheet1のB9の値を入れたいです。 C2=OFFSET(Sheet1!B3,0,3) でできましたが、 C3を、C2の式を使って表したいです。 気持ち的には、 C3=OFFSET(C2,0,3) としたいのですが、当然ながらエラーです＾＾； このように入れられれば C4から下は、コピー＆ペーストで行けるのになぁ…と思います。 C3=OFFSET(C2,0,3)としたときに、 関数中のC2が、C2の値ではなく参照しているセルの数式（OFFSET(Sheet1!B3,0,3) ）を表すようにはできないものでしょうか？？？
- ベストアンサー
- オフィス系ソフト
- エクセル2007 OFFSET関数 INDIRECT関数
エクセル2007でOFFSET関数とINDIRECT関数を用いてセルの入力規則をして、リスト表示したいと思っています。 入力規則のリストは動的な値を設定したいと思っています。 ＡＢＣＤＥ １あいうえお ２かきくけこ ３さしすせそ ４たちつてと ５なにぬねの というシートA列に”データベース1”B列に”データベース2”・・・といった具合に名前を定義します。このデータベースは行がどんどん増えていく可能性があるため、名前の定義の参照範囲欄に (1)=OFFSET($A$1,0,0,COUNTA(A：A),1) としました。 別シートに 表示したいセルで入力規則→設定タブ→入力値の種類→リストを選択。 元の値の欄に (2)=INDIRECT(A2&"1",FALSE) としました。 ※A2は”データベース”と入力してあるセルです。 ここからがわからないのですが 上記式(1)、(2)の両方とも単独で使用した場合は欲しい値が得られるのですが、組み合わせて使用した場合はリストが出てこなくなってしまいます。 1)組み合わせて使うことはできないのですか 2)ほかにいい方法はありますか ということを質問します。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセル SUMPRODUCT と OFFSET
いつもお世話になります。 SUMPRODUCT関数で集計したいのですが、OFFSET関数を組合せてＡ1に関数を入力し、右にひっぱるだけで36ヶ月分を集計したいと思い、下記の計算式を入力したのですが#VALUE!になり困ってます。 A1=SUMPRODUCT((OFFSET(data!$A$1,1,COLUMN(A1)*3-3,99,1)=$G$1)*(OFFSET(data!$B$1,1,COLUMN(A1)*3-3,99,1)=$H$1),(OFFSET(data!$C$1,1,COLUMN(A1)*3-3,99,1))) dataシートには、Ａ列：商品CD、 Ｂ列：営業所CD、 Ｃ列：売上金額 の3列のデータが、36か月分108列あります。 集計するシートのＧ1に商品CDを、Ｈ1に営業所CDを入力すると、Ａ1～Ａ36に集計結果を返したいのですが、教えてくださいませんか
- ベストアンサー
- オフィス系ソフト
- エクセルNA関数の式について。。
先輩から引き継いだエクセルにグラフを作成したのですが、空白になっているセルのグラフが０を表示してしまい困っています。 ネットでNA関数を使えば解消されると勉強し、他のセルは出来たのですが以下の式が入っているデータだけは＃NA表示になりません。 どこの部分にNA()を記入するべきでしょうか？ 私にはギブアップです。 詳しい方のお知恵を頂けるとありがたいです。。 エクセルは2007を使っています。 =IF(AND(COUNT(OFFSET(F3,1-K$2,0):F3)<>K$2),"",IF(AND(COUNT(OFFSET(F3,1-K$2,0):F3)=K$2,K2=""),AVERAGE(OFFSET(F3,1-K$2,0):F3),IF(COUNT(K2=1),K2+2/(K$2+1)*(F3-K2)))) よろしくおねがいします。
- ベストアンサー
- Windows系OS
- 伝達関数
伝達関数を求める問題で困っています。一応伝達関数を求めて X2/F(S)=Cs+K2/[M1s^2+Cs+K1+K2][M2s^2+Cs+K2+K3]-(Cs+K2)^2 と出ました。ちなみに機械システムです。 この各係数に実際に与えられた数字を入れたいのですが、これは直接与えられた数字を代入してよろしいのでしょうか(K1=60N/m,K2=50N/m,K3=40N/m,C=0.1Ns/m, M1=0.04kg,M2=0.05kg) 同様に電気システムの問題もありこちらは、 Vo/Vi=Ls/(R1+R2)LCs^2+(R1R2C+L)s+R1 となり F、Ω、Hを基準にして値をそのまま代入すればよいと思うのですが、機械システムのほうは普段触れる機会がなく単位などの意味もあまりよくわかりません。どなたか解かる方いましたら宜しくお願いします。
- 締切済み
- 物理学
- OFFSET 基準値 可変できないですか？
初めてお世話になります。私なりにやりたいことをネットで検索して作ってますが行き詰まり悩んでいます。 その日のシフト勤務で早番の人はこの人、遅番の人はこの人と検索したいです。 SHEET１には勤務表が記載されています。 ＿A B C D １ １日 ２日 ３日 ２名前A 早番 早番 ・・・・ ３名前B 遅番 早番 ・・・・ ４名前C 早番 遅番 ・・・・ SHEET２には、その日勤務する人で早番がこの人とこの人（例えば、上記でいえば２日に勤務する 早番は名前A、名前Bの２名と検索できるようにしたいです。 （A１の２日はSHEET１の日付と紐付けされていて、３日、４日と変わればC列の名前は変わっていく） SHEET２ ＿ A B C １ ２日 早番 名前A ２ 早番 名前B ３ ４ 自分なりに調べて、C1のセルには、 ＝INDEX（'SHEET１'A列の名前人数分,MATCH("早番",OFFSET('SHEET1'B2,0,SHEET2!A1-1,A列の名前人数分,1）,0),1)とし、名前Aは抽出できましたが、C2セルの２番目の名前Bは検索できません。 matchの抽出は複数検索値がある場合、２番目、３番目は検索できないとあったので、それではと C2セルにOFFSETの基準値をC1で出たMATCHの結果+B2セルという風（C1で出た１番目の早番と出たセルを基準値とし、そこから下の行へ次の早番を探す）に考えましたが出た結果はセルではなく行数になってしまい無理でした。 ２番目の検索までは、OFFSETの基準値を下にずらし、高さ調節でやりようはできるのですが（早番男１名、女１名はSHEET１のA列の名前欄は男性、女性である行で明確にわかれている為検索可）、ある日によっては早番が男２名だったりする場合は破綻してしまいます。 LARGEやSMALLとかも駄目もとで試しましたが、数値じゃないのか駄目駄目でした。 ちなみにSHEET１で記載される名前順は順番を変えたくないです。 柔軟にその日のシフトを検索できる方法はありますでしょうか？ 宜しければご指導宜しくお願い致します。
- ベストアンサー
- その他（インターネット・Webサービス）
- エクセル関数について
エクセルでデータベースを作っています。別のシートからデータを抽出して合計を出すというものなのですが、どうも関数がうまくいきません。 別のシートには日付・現場名・費目・時間・金額などが入力されています。（オートフィルタが使われていました）そしてデータベースには現場名と費目から金額の合計を抽出したいのですが、どんな関数を使っていいのか分かりません。 =SUMPRODUCT((条件1)*(条件2)*....(集計する数値)) =SUMPRODUCT((入力!$C$3:$C$9810=Sheet1!$B3)*(入力!$G$3:$G$9810=Sheet1!C$1)*(入力!$K$3:$K$9810)) というものを使っていたのですが、別シートのデータが入力されていない所まで範囲を指定するとエラーになってしまいます。 =sumifだとエラーにならないのですが、私の技術では１つの条件（現場名だけ）しか抽出、合計できません。 入力されていない所を範囲指定してもエラーにならなくて上の事が出来る関数はないでしょうか、よろしくおねがいします。
- ベストアンサー
- オフィス系ソフト
- VBA WorksheetFunction.VLookupとoffsetプロパティー
エクセル２００２使用です。 VBAの記述で、WorksheetFunction.VLookupを使って求めた値の入っているセルの列方向に一つ下の値を取得したいのですが・・・・ range("A1").Value =WorksheetFunction.VLookup( range("B1").Value, Range("A3:C10"), 3, 1) 例えば上記で得た値がC4セルにあるのであれば、C5セルの値を取得したいのです。 offset（０，１）プロパティーをどこにおけば良いのか良くわかりません。 どうかよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 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セルに貼ると エラーになります。 どうかご教示お願いいたします。
- ベストアンサー
- その他MS Office製品
- エクセルの関数の質問です。
エクセルの関数の質問です。 セルに値がある空間をおいて存在します。 例 Ａの1番地からＩの一番地までは □□１■■■１□□ で Ａの２番地からＩの２番地までは □１■■■■■１□ という状態です。 この■の部分に１を返したいのですが、何かいい関数って ありますか？
- 締切済み
- 財務・会計・経理
お礼
指定範囲がK2:K75でした。 説明が足りず申し訳ありません。 B4:=OFFSET(INDIRECT(CELL("address",K2:K75)),MATCH(C4,K2:K75,0)-1,-6) としたら出来ました！ ありがとうございました！m(__)m