- ベストアンサー
エクセルで求めたセルの左隣りのセルの値を返したいです
お世話になります INDEXとMatchを使い行の一番右(左から順にセルが埋まるので右端のセルが一定では無い)の値を返すようにしました。以下です。 しかし、一番右から２番目のセルの値(求めたものの左隣り)もほしいのです。 その場合はどうしたらよいのでしょうか？ =INDEX(1:1,MAX(IF(COUNT(1:1),MAX(MATCH(MAX(1:1)+1,1:1,1))),IF(COUNTIF(1:1,"*"),MATCH("",1:1,-1)))) 宜しくお願い致します。
- berry_late
- お礼率74% (100/135)
- オフィス系ソフト
- 回答数8
- ありがとう数8
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
> INDEXとMatchを使い行の一番右(左から順にセルが埋まるので右端のセルが一定では無い)の値を返すようにしました。 数値にも文字列にも対応したすばらしい式ですね。感心しました。 > 一番右から２番目のセルの値(求めたものの左隣り)もほしいのです。 こんな数式を作れる人が何をおしゃいますことやら・・・。 左隣ならINDEXを１つ少なくするだけのことですから =INDEX(1:1,MAX(IF(COUNT(1:1),MAX(MATCH(MAX(1:1)+1,1:1,1))),IF(COUNTIF(1:1,"*"),MATCH("",1:1,-1)))-1) でいいんじゃないでしょうか？
その他の回答 (7)
- maron--5
- ベストアンサー率36% (321/877)
◆こんな方法もありますよ =INDEX(1:1,LARGE(INDEX((1:1<>"")*(COLUMN(1:1)),),2)) ★後ろの数字が一番右から何番目かになります
お礼
本当にいろいろな方法が考えられるものですね。これは単純化されていて良いですね。 次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。 ありがとうございました。
- howarage
- ベストアンサー率20% (2/10)
左隣が空白の場合にも対処するにはMAXの代わりに、LARGE関数使えばよかったのですね。 ｛=INDEX(1:1,LARGE(IF(ISBLANK(1:1),0,COLUMN(1:1)),2))｝ 教えて！は本当に勉強になります。
お礼
本当にいろいろな方法が考えられるものですね。 次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。 ありがとうございました。
- howarage
- ベストアンサー率20% (2/10)
みなさんいろいろなやり方を考えますねぇ 僕もひとつ考えました。配列数式を使いますが、式が直感的でわかりやすかなと思います。 ｛=INDEX(1:1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))｝ 左隣はやはりINDEXの-1なんですが、その左隣が空白の場合はどうすればいいのか。。。正直、悩んでます。
お礼
本当にいろいろな方法が考えられるものですね。 次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。 ありがとうございました。
左から順に入っているのなら 既に出ている方法(COUNTを使う方法) でできそうですが せっかくなので > =INDEX(1:1,MAX(IF(COUNT(1:1),MAX(MATCH(MAX(1:1)+1,1:1,1))), > IF(COUNTIF(1:1,"*"),MATCH("",1:1,-1)))) この式は =LOOKUP(10^16,CODE(1:1),1:1) こうすることもできますね。ひとつ前なら =INDEX(1:1,LOOKUP(10^16,CODE(1:1),COLUMN(1:1)-1))
お礼
なるほどぉぉぉ。 本当に勉強になりますねぇ。。。。 ありがとうございました。
- imogasi
- ベストアンサー率27% (4737/17068)
ＩＮＤＥＸ関数の列を示す引数のところにー１すれば済む。 同行一番右は =INDEX(A1:Z1,1,COUNTA(A1:Z1)) （Ｚ列までが入力範囲として） もし式複写するなら =INDEX($A1:$Z1,1,COUNTA($A1:Z$1))など または =INDEX(1:1,1,COUNTA(1:1)) その左はー１列すればよい =INDEX(A1:Z1,1,COUNTA(A1:Z1)-1) または =INDEX(1:1,1,COUNTA(1:1)-1)
お礼
そのようですね。下の回答を拝見して学びました。 このマイナス１が曲者でした。 このような使い方ができるとは知らなかったので、とても役に立ちました。 ありがとうございました。
- onlyrom
- ベストアンサー率59% (228/384)
よくもまあこんな複雑な式を作りましたねぇ。 ２，３ケ月後に見て、直ぐ分かるのだろうかと人事ながらちょと心配。。。(^^;;; 途中に未入力セルがない前提なら、、、 ●右端の値 =INDEX(1:1,0,COUNTA(1:1)) ●右端よりひとつ前の値 =INDEX(1:1,0,COUNTA(1:1)-1) ●右端より二つ前の値 =INDEX(1:1,0,COUNTA(1:1)-2) 外しましたらご容赦願！ 以上です。
お礼
ありがとうございます。 そうなんです。未入力セルがなければ一番右はこれだと私も思うのです。 ところが、空白があるもので、これだとだめなんです。 でも、右寄りひとつ前の値への応用は#1の場合と同じでこれが私にはわからなかったので、大変勉強になりました。
- okormazd
- ベストアンサー率50% (1224/2412)
複雑な式なので、にわかには理解できませんでした。 で、 こんなのでは駄目なんですか。 一番右 =INDIRECT(ADDRESS(1,COUNTA(1:1))) 左隣 =INDIRECT(ADDRESS(1,COUNTA(1:1)-1))
お礼
ありがとうございます。 INDIRECTは使ったことがない関数で、今パラパラと本をめくってなるほどと思いました。 次回何かの折にぜひ使用したいものです。 ところが今回は空白があるものでこれだとだめなんです。 でも、右寄りひとつ前の値への応用は#1の場合と同じでこれが私にはわからなかったので、大変勉強になりました
関連するQ&A
- エクセル2010 同データの検索と関連セルの表示
先の質問、 http://okwave.jp/qa/q8405162.html において、実践後の画像を再アップさせて頂きます。 まずは、S1セルに =IF(INDEX($Y:$Y,ROW())="","",INDEX($Y:$Y,ROW())&"■"&COUNTIF(INDEX($Y:$Y,1):INDEX($Y:$Y,ROW()),INDEX($Y:$Y,ROW()))) を入力後、ctrl + ENTER で確定し、オートフィルで最下部まで。 その後、すべてctrl + ENTER で 以下を貼り付けました。 B9セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) B10セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($Z:$Z,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($Z:$Z,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) B11セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($AA:$AA,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AA:$AA,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) B12セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($AC:$AC,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AC:$AC,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) C9セル =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(COUNT(INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))),LOOKUP("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))),""))) C9セルの書式設定の表示形式を[日付]に。 C10セル =IF(ISERROR(1/(INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)<>"")),"",INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)) C11セル =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(INDEX($AB:$AB,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AB:$AB,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) C12セル =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(INDEX($AD:$AD,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AD:$AD,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) 機器Aの１回目は求める数値を抜き出して表示してくれています。 それを、コピー＆ペーストで 機器Bの１回目、機器Aの２回目に貼り付けました。 これは、オートフィルでも同じ数値が帰って来ます。 それで問題点なのですが、 機器Bの１回目、E9セルには１月１日と、E10セルには空白が帰って来ています。 また機器Aの２回目も同じ場所、C13セルが１月２日と、C14セルが空白で帰って来ています。 ちなみにE9セルの数式は =IF(ISERROR(1/(INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)<>"")),"",INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)) E10セルの数式は =IF(ISERROR(1/(INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)<>"")),"",INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)) となっています。 これで問題点が明らかになるでしょうか？ よろしくお願いいたします。
- 締切済み
- Excel（エクセル）
- Exelで最大値を求めた値と別セルの品番を求める
Exelで最大値を求めた値と別セルの品番もランキング集計表で拾いたいです。INDEX,MATCH,MAXを使うと思うのですが。。。 Ｂ列の最大は=MAX(B2:B10)で”11”が求められますがどの品番かを隣のセルにランキング形式で算出したいんですが、よくわかりません。MATCHやINDEX関数を使うと思うのですが、式を教えてください。
- 締切済み
- 財務・会計・経理
- エクセルで最下行の値を表示させられますか？
エクセルでセルＡ１からＡ２、Ａ３・・・と、数字がランダムに入ってきます。このとき常に一番下の最新の数字をＡ１００セルに表示させることは出来るでしょうか。 他で教わった「=INDEX(A:A,MATCH(MAX(A:A)+1,A:A,1))」では、値が０となってしまったのですが・・・。
- ベストアンサー
- オフィス系ソフト
- ExcelセルにVBAでINDEX関数を入力
ExcelでINDEX関数とMATCH関数で"B11:E13"セルに以下の数式データがあります。 数式は "B11"=IF(ISERROR(INDEX($A$1:$A$9,MATCH(1,B$15:B$23,0))),"",INDEX($A$1:$A$9,MATCH(1,B$15:B$23,0))) "B12"=IF(ISERROR(INDEX($A$1:$A$9,MATCH(2,B$15:B$23,0))),"",INDEX($A$1:$A$9,MATCH(2,B$15:B$23,0))) "B13"=IF(ISERROR(INDEX($A$1:$A$9,MATCH(3,B$15:B$23,0))),"",INDEX($A$1:$A$9,MATCH(3,B$15:B$23,0))) "C11"==IF(ISERROR(INDEX($A$1:$A$9,MATCH(1,C$15:C$23,0))),"",INDEX($A$1:$A$9,MATCH(1,C$15:C$23,0))) "B15～B23"に数値1～3を入力した場合イニシャルA～I が入力される式ですが、列を連続で入力できるような処理をVBAのWorksheetFunctionで行うにはどのようにコードを記述すればよいのでしょうか。 ご回答のほどよろしくお願いします。
- ベストアンサー
- Visual Basic
- エクセルの抽出について
エクセル初心者です。ネットや参考書をみて何となくやっているのですが、以下の事がどうしてもわかりません。（抽出の質問をいろいろみたんですがよくわからなかったので質問が重複していたらごめんなさい） 加工 Pr単価 チェーンカット 20 組み立て 30 メッキ 25 ハンダ 10 のり付け 15 エポ入れ 30 という表（これで伝わりますでしょうか？）で、「加工」の項目の、「メッキ」と「ハンダ」と「エポ入れ」を抜いたデータを別のシートのセルに表示させたいのですが、出来るのでしょうか？（その時に、「単価」も一緒に表示させたいのですが） 後、タイトルとは違うのですが、もう１つお聞きしたい事があるんですけど、ある表に品番を入力して最後にきた品番を別のセルに表示させるようにしたいので以下の関数？を使ったのですが、機能はするのですが、表に何も文字が入力されていないと、「#VALUE!」という文字がセルに表示されてしまいます。機能するのでかまわないのですが何か気持ち悪いので解決できる方法があればおねがいします。 =INDEX(A41:A51,MAX(IF(COUNTIF(A41:A51,"*"),MATCH("",A41:A51,-1)), IF(COUNT(A41:A51),MATCH(MAX(A41:A51)+1,A41:A51,1)))) すいません、あともう１つあるんですけど、隣のセルに文字が入力されたら「T」の文字が表示されるようにしたのですが、数字だけ入力されると表示されません。数字以外の文字が混ざっているとちゃんと表示されるのですが、こちらも合わせてよろしくお願いします。 =IF(COUNTIF(A5,"*"),"T","") 質問がたくさんになってしまい申し訳ありませんが、どなたかお力添えをお願いします。（Excell X for Macを使っています）
- ベストアンサー
- オフィス系ソフト
- エクセル 複数シートの値のみコピーで
こんにちは いつもお世話になっています。 先日、選択した複数のシートを新しいブックに値だけコピーするマクロをこちらで教えていただきました。（関数が入ったシートなのでタブの右クリックからの新規ブックへのコピーでは関数がコピーされてしまうので） 今回、このマクロで失敗するシートがあったので原因を教えてください。 値だけコピーするマクロは以下です。 Sub 値コピー() Dim WS As Worksheet ActiveWindow.SelectedSheets.Copy For Each WS In ActiveWorkbook.Worksheets With WS.Cells .Copy .PasteSpecial Paste:=xlPasteValues End With Next Application.CutCopyMode = False End Sub 失敗するシートにはつぎの関数があります。 A1セルに=REPLACE(CELL("filename",A1),1,FIND(".xlsx]",CELL("filename",A1))+LEN(".xlsx]")-1,) B3セルに=IF(COUNTIF(Sheet2!$1:$1,$A$1),IF(ROWS($3:3)>COUNTIF(OFFSET(Sheet1!$J:$J,,MATCH($A$1&"クラス",Sheet1!$J$1:$N$1,0)-1),B$2),"",COUNTIFS(OFFSET(Sheet1!$C:$C,,MATCH($A$1,Sheet1!$C$1:$G$1,0)-1),">"&INDEX(Sheet1!$C:$G,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0),MATCH($A$1,Sheet1!$C$1:$G$1,0)),OFFSET(Sheet1!$J:$J,,MATCH($A$1&"クラス",Sheet1!$J$1:$N$1,0)-1),B$2)+1&"位 "&INDEX(Sheet1!$B:$B,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0))&" "&INDEX(Sheet1!$C:$G,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0),MATCH($A$1,Sheet1!$C$1:$G$1,0))&"点"),"") 別シートのデータから条件に合うものを引き出す関数です。 これらの関数もおしえていただいたもので、まだ理解できていませんので、説明不十分で申し訳ありません。 これらの関数があるシートではなぜ上記のマクロが失敗するのでしょうか。 もちろん、シートのデータを選択、コピーして新規ブックに値のみコピーはできます。 上記のような関数があるシートでも複数選択シートでの値のみコピーができるマクロを教えていただけないでしょうか。 情報不足がありましたら、教えてください。 よろしくお願いします。 エクセル2007
- ベストアンサー
- オフィス系ソフト
- エクセル隣のセルと違う数字の場合は色付け、空白はそのままにしたいのです。
条件付書式でやってみたんですが、「セルの値が」「次の値に等しくない」「＝（左隣のセル）」でやって、違う数字の場合はうまく色付になるんですが、空白の場合も色が付いてしまいます。その左隣のセルに数式を入れてるからかなと思い、数式を消すと、色は付きませんでした。 左隣のセルに数式を入れたまま、そのセルも右のセルも空白の場合、色を付けたくないんですが、どうすればいいでしょうか。
- ベストアンサー
- オフィス系ソフト
- エクセルでセルの値がある範囲にあれば１と表記したいです
あるセルの値(B1)が C2からC13までの中に含まれていれば1, C14からC28に含まれていれば2 のように表記したいと考えています。 以前教えて頂いたことのある if(countif(C2:c13,B1)>0,"1","")のような式を応用しようとして ネストやANDを使ってみたのですがエラーが出て式が作れません。 アドバイス頂けますでしょうか。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- 相互に連動して値の変化するセル（エクセルＶＢＡ）
当方プログラミング初心者です。こちらのカテゴリならお分かりになる方がいらっしゃると思い質問させていただきました。 それぞれに入力できなおかつ一方の値が変化すると他方の値が変化するものを作りたいのです。 具体的には、ある年度の総売上を100％としてその内訳の売上、例えば、生鮮食品6500円で65％として、左のセルに6500右のセルに65と値あった場合、左のセルを7500にすると右が75になり、右のセルを55とすると左が5500になるようなものです。 つまり、売上のシミュレーションができるものを考えているのですが、そのようなことが可能かどうかできるならば具体的なコードでできなければ何か別の方法をご提示いただきたいのです。
- ベストアンサー
- Visual Basic
- 選択したセルの値を別シートのセルに取り込む方法
顧客情報を閲覧・印刷するためのフォームがsheet1とします。顧客の情報が入ったデータベースがsheet2とします。 以下のマクロでsheet2の48列目を空欄にして、48列のいずれかのセルに「出力」と入力すると、そのセルの行の値を出力結果というシートに渡すようにしています。取り込んだ行の顧客番号をsheet1のmach関数の参照先に指定して、index関数で各項目に取り込むようにしています。 Worksheets("sheet2").Activate Dim i, LastRow As Long LastRow = Cells(Rows.Count, 48).End(xlUp).Row For i = 1 To LastRow If Cells(i, 48) = "出力" Then Rows(i).Copy Sheets("出力結果").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) End If Next i もう少しこれを改良して、Sheet2の顧客番号の入っている1列目の任意のセルを選択して、sheet1のmatch関数の参照先（例としてK4）に選択した顧客番号を渡す方法はありませんでしょうか。sheet1は顧客番号だけ取得できれば、match・index関数でフォームが完成します。 VBAは初心者です。上記マクロは検索で調べて必要な個所をコピーして今の環境にアレンジしました。よろしくお願いします。
- ベストアンサー
- Excel（エクセル）
お礼
あああ。そんな簡単なことで良いのですね。 もともとの式は似て非なるものを前任の方が残して行ったので、それをそれを紐解いて応用して作成したものなのです。 あるものを理解し紐解いて応用できたのですが、そこにない情報をどのように組み入れるのかがさっぱりわからず。。。。 なるほどぉ。。。見るとわかるのですが、作り出せないので感動しました。 ありがとうございました。