• ベストアンサー

Excelの式が上手く行きません。

noname#52504の回答

noname#52504
noname#52504
回答No.10

#5です。 >「Sheet1!R4C15」が「A」の人をリストアップ ん~っと、R4C15というのは単一のセルに対する参照ですが、第15列についてのみ処理する、ということでよろしいでしょうか? 今回の表位置と、#6で提示した考え方に沿って書くならば、やはりLARGEを使って、  =INDEX(Sheet1!R4C4:R54C4,1/LARGE((Sheet1!R4C15:R54C15="A")/ROW(Sheet1!R4C4:R54C4),ROW()-ROW(R3)+1)-3) ということになります。 もちろん、これに限らず他にもさまざまな書き方ができます。 例えば、  =INDEX(Sheet1!R4C4:R54C4,SMALL(IF(Sheet1!R4C15:R54C15="A",ROW(Sheet1!R4C4:R54C4),""),ROW()-ROW(R3)+1)-3) など。 以下、冗長になってしまいましたが、少し詳しく解説すると… ●配列数式について 「配列」というのは、大雑把に言うと「四角いデータの並び(矩形データ)」のことですが、配列数式に用いる場合は、「一定の要素数を持つ一行または一列のデータ(ベクトル)」であることが多いので、「ある一定の長さのリスト」というふうに考えていただいても差し支えないと思います。 配列数式というのは「配列のそれぞれの要素について個別に処理せよ」という数式です。「数式の中で作業列を使った処理をやらせる」と考えると分かりやすいかも知れません。 例えば、  =INDEX(ROW(R1C1:R10C1)+3,4) という数式は、 まず、R1C1:R10C1のそれぞれのセルについて、行番号を取った配列 {1,2,3,4,5,6,7,8,9,10} を作り、 次に、その配列のそれぞれの要素について、3を加えた配列 {4,5,6,7,8,9,10,11,12,13} を作り、 更に、その配列のうち4番目を返せ、ということですから、7が返ります。 http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml ●比較演算、論理演算、算術演算について =とか、>といった比較演算子を用いた演算は、論理値(TRUEまたはFALSE)を返します。 例えば、(1+5=3*2) という比較演算子を用いた数式は論理値TRUEを返します。 一方、論理値(TRUEまたはFALSE)に対して、+とか*といった算術演算子を用いると、TRUEは1,FALSEは0として扱われます。 例えば、((1+5=3*2)*3)、はTRUE*3であり、すなわち、1*3なので、3を返します。 従って、、比較演算子と算術演算子を組み合わせれば、AND関数やIF関数を使った論理演算を代用させることができます。 例えば、  =IF(AND(R1C1=3,R2C1=4),5,0) と書いても  =(R1C1=3)*(R2C1=4)*5 と書いても ほぼ同じ意味です。 #7の回答では、これを使って、3列それぞれの判定をANDでくくる代わりに、*で掛け合わせることで「かつ」を表現しています。 http://www.kentei.ne.jp/quali/column/knowhow/030815/030815.html ●=INDEX(Sheet1!R4C4:R54C4,1/LARGE((Sheet1!R4C15:R54C15="A")/ROW(Sheet1!R4C4:R54C4),ROW()-ROW(R3)+1)-3)   について 目的は、 「検索列の条件を満たすセルのうち、N番目のセルに対応するセルを名簿列から探して返す」 ということですよね。 まず、R4C15:R54C15="A" の部分で、 「条件を満たすセルについてはTRUE,そうでないセルはFALSEとなる配列1」 を作ります。 配列1の各要素をそれぞれ、名簿列Sheet1!R4C4:R54C4の行番号で割ることで、 「条件を満たすセルについては0、そうでないセルは行番号の逆数となる配列2」 を作ります。 ココで、割るのではなく掛けてしまってもよさそうな気がしますが、そうすると、後で「N番目のセル」を探すときに、SMALL関数は0をカウントしてしまうのでうまく行きません。 例えば、{0,2,0,0,5,0,7}のうち5を探すのは「2番目に小さい正の数」ということになって困難ですが、{0,1/2,0,0,1/5,0,1/7}としておけば、「2番目に大きい数」ですからLARGEで探せます。 上記別案のようにIF関数を使って 「条件を満たすセルについては""、そうでないものは行番号の逆数となる配列」 を作るならば、SMALLで判定することもできます。 ROW()-ROW(R3)+1 は自セルの行番号とスタート行番号を比較して、「条件を満たすセルのうち何番目のセルを返すのか」を計算しています。 以下、 ・LARGE(配列2,N)  で、「N番目のセルの行番号の逆数」をとります。 ・1/LARGE(配列2,N)  で、もう一度逆数をとって、「N番目のセルの行番号」をとります。 ・1/LARGE(配列2,N)-3 で、名簿列の位置に応じて値を調整して、  「N番目のセルに対応する配列Bの行位置」をとります。 ・INDEX(名簿,1/LARGE(A'',N)-3)  で、目的の値を返します。   以上、雑駁ですがご参考まで。 説明の至らない部分も多いと思いますが、これ以上の詳細な説明は私の手には余るように思われますので、これで最終回答とさせていただきたいと思います。 長乱文陳謝。

aloe_bear
質問者

お礼

どうもありがとうございました! 色々調べて、やったところ、上手く行きました!! 結局全て頂いた回答の中に答えがあったのですが、 自分が理解できていなかっただけでした・・・。 詳しく説明していただいてありがとうございました。 おかげさまで理解できたので、これから仕様変更があっても対応できます。 本当にありがとうございました。

関連するQ&A

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

    下のような「得点」という名前のシートがあります。 (「田中」のセルがA1です。)  [ 田中 ][ 10 ][ 200 ]  [ 山田 ][ 21 ][ 150 ]  [ 佐藤 ][ 76 ][ 250 ]  [ 鈴木 ][ 53 ][ 350 ] 別のシートのA1セルに、「佐藤」と入力すると、  [ 佐藤 ] 「得点」シートから「佐藤」の列を見つけて、B1、C1に  [ 佐藤 ][ 76 ][ 250 ] のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。 「得点」シートでは氏名が重複する事はありません。 IF文を使うと思うのですが、いまいち良く分かりませんでした。 よろしくおねがい致します。

  • エクセルで条件に一致する複数の列を取得するには

    下のような「得点」という名前のシートがあります。 (「田中」のセルがA1です。)  [ 田中 ][ 10 ]  [ 山田 ][ 21 ]  [ 佐藤 ][ 76 ]  [ 鈴木 ][ 53 ] 別の「組」シートで、以下のようになっています。  [ A ][ 田中 ]  [ A ][ 佐藤 ]  [ B ][ 山田 ]  [ C ][ 鈴木 ] ここで、「組」シートのどこか空いているセル(例えばF1)に、 「Aの組の点数の合計値」を表示したいと思っています。 考えた方法としては、  1.まず「組」シートのA1からD1を順番に見て、  2.値が「A」になっている場合のみ、そのひとつ右の名前を取得して、  3.「得点」シートで、取得した名前を探し、その点数を取得し、  4.それを全て足す という感じなのですが、どういう式を書けば良いのか分かりません。 度々すみませんが、よろしくおねがい致します。

  • EXCELでの式の書き方

    エクセルでの式の書き方を教えてください。 ファイルの中に2つのシートを作成しています。シート名は「田中4月」 「H19.4月集計」です。 「H19.4月集計」は「田中4月」で一覧入力したデータを集計してるシートです。 やりたいことは、下記2パターンです。セルにどのように式を入力すればよいか教えてください。 (1)「H19.4月集計」のシートで、シート名「H19.4月集計」の年月を使用し、   4月の場合”平成19年4月30日現在"   5月の場合”平成19年5月31日現在"とセルに表示したい。   年月を取得する方法は、調べました。   =TEXT(SUBSTITUTE(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31),"月集計","")&".1","ggge" & "年" & "m" & "月") (2)「H19.4月集計」で「田中4月」のセルA10の内容を表示したい。  セルに"=田中4月!A10"と入力すればいいのですが、  これを  田中=「H19.4月集計」のA2のセルの内容 ※A2にはダイレクトで"田中”と入力してあります。  4月=シート名「H19.4月集計」から月を取得  を使用して式を書きたいと思います。  今後、5月、6月と増えているため式にしたいと思います。 よろしくお願いします。   

  • エクセル関数式、入力範囲の一番右端の値を返すには?

    エクセル関数式質問です。 Sheet1のセルA1値、A2値、A3値、A4値を,Sheet2のセルA1に「=Sheet1!A1」,セルB1に 「=Sheet1!A2」,セルC1に「=Sheet1!A3」,セルD1に「=Sheet1!A4」という仕組みを作っ ておいて、Sheet1のセルA1からA4に人の名前をそれぞれ入力したとします。 例えば、Sheet1のセルA1に「田中」、セルA2に「中村」、A3は空白、A4も空白としたと き、Sheet2のセルA1には「田中」,セルB1には「中村」,セルC1に「空白」,セルD1に「空 白」(この場合、これらの空白は空白に見えても実際は、「Sheet1!A*」が隠されている わけですが…)となります。 以上の条件において、Sheet2の別セルに、今、Sheet2のA1からA4の範囲に表示されてい る名前のうち、常に右端の名前を表示させたい場合、この場合は「中村」です。その右 となりは空白ですので。仮にA3に「大山」と表示されたてA4は空白の場合は、もちろん 「大山」になります。この別セルに入力する関数式を教えてください。宜しくお願いし ます。

  • エクセルで部分一致による自動計算

    先日も似たような質問をしたのですが、新たな問題が発生したので、 よろしくお願いいたします。 http://okwave.jp/qa4303345.html sheet1のA列のセルには名前、B列のセルにはポイントが入力されているとします。 sheet2のA1セルに”田中”と入力した時にB1のセルに田中の総ポイントが自動表示されるにはどのようにすればよいでしょうか? sheet1     A     B 1 田中・鈴木   5 2   伊藤     8 3   田中     4 Sheet2     A     B 1   田中    9

  • エクセル2000でのデータ作成で・・

    顧客管理のデータを入力しています。  月日 顧客名   数量 累計 担当者 コード    欄外  2/14 ○○○    1    1  田中   A       1  2/14 ○○○    1    2  田中   B      2  2/14 ○○○    2   4  鈴木          1  2/15 ○○○    2   6  鈴木   C      2  こんな感じだとします。 累計にはIF(ISBLANK(C3),"",D2+C3)のような式が入ってます。  コードAと空欄のものと、BとCとの2つの表に分けたいと します。シート1には全体の表を、シート2にAと空欄、シート3に BとCを表示したい時はどうしたらいいでしょうか?  新しいデータはシート1にどんどん入力したいのです。 シート2とシート3それぞれの累計と通番号も付けたい場合、 どうやるのが一番でしょうか・・。  今はシート1の欄外に1と2と入力してシート2に =IF(シート1!H1=1,シート1!A1,"") と入れています。  そうするとどうしても空白の行ができてしまい、 累計の演算式もエラーが出ます。行削除して、 通番号を付け直していますが、他に一発で隙間なく シート2,3へ転記され、各シートごとの累計を出す方法が ありましたらおしえてください。

  • エクセルのIF式教えて下さい。

    何度やっても上手く思うように行きません。 IF式の組み立て方が悪いのか? AND、ORの使い方が悪いのか? お手上げの状態です。 質問は、2つのパターンです。 助けて下さい。 質問1 セルA1にデータ10と入力 セルA5にデータ5と入力 セルA9に大きい値の10を表示させ、セルA1が空欄の時は、空欄にしたい。 セルA10に小さい値の5を表示させ、セルA5が空欄の時は、空欄にしたい。 質問2 セルB2に1を入力 セルC2に1を入力 セルD2に両方が1以上ならOK、それ以外ならNGと表示させたい。 以上の2つの質問のIF式を教えて下さい。

  • エクセル 条件つきで最小値を求めたいです

    A列に200名を超える生徒の名前が入っています。 B列に計算テストの結果が入っています。 計算テストは何度も行われており、A列に何度も同じ生徒の名前が出てきます。A列に入っている生徒の名前は1000を超えます。 A   B 田中  42 佐藤  48 鈴木  52 佐藤  56 田中  72 鈴木  60 鈴木  23 などです。この中で鈴木君のとった得点の最小値は23点ですが、それを求める数式を知りたいです。 『rankif』とか『minif』みたいな関数があればいいのですが、ないですよね。ちなみに、私が使用しているパソコンはexcel2000です。 他のシートやC列から右に生徒を判別する関数を入れていけば、私の知っている知識でも可能なのですが、関数を多用すると重くなりますし、避けたいです。また、もともとのデータの入力の仕方を変えたらいいのですが、それはできない状態です。 =MIN(IF(A2:A7="鈴木",B2:B7),false) みたいな感じかなと式を立てたのですが、うまくいきません。上の状態で、セルに23と表示されるには、どうすればいいか教えてください。お願い致します。m(_ _)m

  • Excel式をそのままsheetを変えても使うには

    Excelsheet1のEセル4行目に=IF(AND(C4=0,D4=0),"",E3+C4-D4) この式を入れて家計簿を作りました sheet2にこの式を使いたいのですが sheet1をコピーしてsheet2で文字のみ削除しました このセルの=IF(AND(C4=0,D4=0),"",E3+C4-D4) この式はどうすればsheet2で使えますか? 家計簿だと月ごとにsheetを変えて増やしていく方がいいのでしょうか?そのままダラダラ下に行を増やせばいいのですか? Excel2003 SP2 です

  • エクセル2007

    シートA、B、Cを使用しています。 シートCセルa1に、=if(B!a1="","",B!a1-A!a1)と入力しました。 シートCセルa2,a3,a4,a5,a6にも同様の式{a2には、=if(B!a2="","",B!a2-A!a2)}を入力しました。 シートCセルa7に(a1+a2+a3+a4+a5+a6)の式を入力しました。 シートBセルa3,a4,a5,a6に数値が入っていないので、シートCセルa3,a4,a5,a6とa7がエラー表示されています。 【質問】 ・シートCセルa3,a4,a5,a6をエラー表示ではなく空欄にして、a7にはa1とa2の合計を表示したい場合はどうすればよいでしょうか?