セルの値の検索といえばVLOOKUP関数ですが、
例えば条件にかなったセルの左上の値を返す・・・などといった
範囲が同じ行に無いものを返す関数などというのはあるのでしょうか?
知ってる方がいましたら教えてください。

A 回答 (2件)

   B   C   D


-------------
1|
2|
3| 1  10  100
4| 2  20  200
5| 3  30  300
6| 4  40  400
7| 5  50  500

こんなデータが入力されたシートがあって、C5(=30)を見つけるために、Vlookupを使わないで

 =INDIRECT("C"&MATCH(30,C3:C7)+2) とすると30が表示されます。

これは、C3:C7の中をMATCHで30を探して『3』が答えになります。これに空白行数の『2』を加えて5行目だと分かります。
次に、INDIRECTでC列と5行目を指定して『30』を取り出します。Vlookupの代用品です。代用品を作ったのはVlookupは値を返すためです。INDIRECTは『セル』と思っていいでしょう。

ここで質問の条件にかなったセルがC5とすると
右下のセルD6 =OFFSET(INDIRECT("C"&MATCH(30,C3:C7)+2),1,1)
左上のセルB4 =OFFSET(INDIRECT("C"&MATCH(30,C3:C7)+2),-1,-1) になります。

OFFSET関数は =OFFSET(セル番地,行数,列数)のように使います。
行数は下に行けば+、上が-。列数は右が+、左が-になります。Helpに詳しく書かれています。

ご参考に。
    • good
    • 0
この回答へのお礼

nishi6様
エクセルの回答をいただいたnao945です。
お礼が遅くなって本当にごめんなさい。教えていただいたOFFSET関数こそ
探していた関数でした。本当にありがとう!

お礼日時:2002/02/22 00:57

こんばんは!



  A  B  C  D
1 11     B202 式
2 12  A101
3 13  A102
4 14  B201
5 15  B202
6 16  B203
7 17  C301
8 18  C302
9    C303

例えば C1の値に合致するB列を検索して
合致する行の1つ上のA列の値を返すのであれば

・B列に同じコードが無く、A列は数値 であれば
 =SUMIF(B2:B9,C1,A1:A8)

・A列(返す値が)が数値でなく文字列の場合
 =IF(ISNA(MATCH(C1,B2:B9,0)),"",INDEX(A1:A8,MATCH(C1,B2:B9,0)))

で試してみて下さい!
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcelアルファベットを含む数値から文字に変換

Excelで質問です。
アルファベットを含む数値から文字に変換する方法
例えば、下記のような変換方法はありますでしょうか?
101 → りんご
1A2 → みかん
101,1A2 → りんご,みかん

Aベストアンサー

こんにちは!
横からお邪魔します。

↓の画像のようにSheet2に表があり、Sheet1のB列に表示するようにしてみました。
他の方々と同じようにVBAになってしまいます。

Alt+F11キー → メニュー → 挿入 → 「標準モジュール」を選択 → VBE画面に
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
尚、Sheet1のデータは1行目からあるとします。

Sub Sample1() 'この行から
Dim i As Long, k As Long, n As Long, c As Range, buf As String, myArray
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

wS1.Columns(2).ClearContents
For i = 1 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
buf = ""
If InStr(wS1.Cells(i, 1), ",") > 0 Then
myArray = Split(wS1.Cells(i, 1), ",")
For k = 0 To UBound(myArray)
Set c = wS2.Columns(1).Find(What:=myArray(k), LookIn:=xlValues, LookAt:=xlWhole)
n = c.Row
buf = buf & wS2.Cells(n, 2) & ","
Next k
wS1.Cells(i, 2) = Left(buf, Len(buf) - 1)
Else
Set c = wS2.Columns(1).Find(What:=wS1.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
n = c.Row
wS1.Cells(i, 2) = wS2.Cells(n, 2)
End If
Next i
End Sub 'この行まで

こんな感じではどうでしょうか?m(_ _)m

こんにちは!
横からお邪魔します。

↓の画像のようにSheet2に表があり、Sheet1のB列に表示するようにしてみました。
他の方々と同じようにVBAになってしまいます。

Alt+F11キー → メニュー → 挿入 → 「標準モジュール」を選択 → VBE画面に
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
尚、Sheet1のデータは1行目からあるとします。

Sub Sample1() 'この行から
Dim i As Long, k As Long, n As Long, c As Range, buf As Strin...続きを読む

Q関数が入っている列で値のある最後のセルの値を返す

お尋ねします。

関数が入っている列で式の値が入っている一番下のセルの値を調べるにはどうしたら良いでしょうか?

     A列
  1   5
  2   10
  3   13
  4   (式の値が無いのでゼロ)

B1セルにA3セルの13と言う値を返したいのですが、列に式(SUMPRODUCT関数)が入っていて、実際はA4セルのゼロが返ってきます。

またシート1~シート5 まで上記と同じ式が入っていて、シート1のA列の最後の値「13」をシート6のB1セルに値を返す場合、INDIRECT関数を使った場合、どういう式になるでしょうか?
ご回答よろしくお願いします。

Aベストアンサー

とりあえず今ご質問で掲示されている通り、「式の値がある」のは全部プラスの数字で、しかも上から詰めて表示されるという事なら
=INDEX(Sheet1!A:A,COUNTIF(Sheet1!A:A,">0"))
といった具合でも計算できます


A列にいま記入してある具体的な数式の内容を添えてご相談が書かれていれば、それをどんな風に手直ししたらどんな風に簡単に計算できるかといったお話しもできましたが、それは次回のご参考に。

Qactionscript アルファベットを数値に変換できる?

Flash Actionscriptの開発で困ってます。
テキストフィールド1とテキストフィールド2があります。それぞれのフィールドに入力されたアルファベットを数値に変換(例えばAと入力されたら1、Bなら2、Cなら3)して、A+B=3のように足し算して返すにはどうしたらよいでしょうか??
こういうのを覆面算?っていうんでしょうか。

Aベストアンサー

ご使用の Flash のバージョンや ActionScript のバージョンなどがわかりませんので,
何とも言えませんが...。
(何とも言えないというのは場合によって答えがありすぎて,つまり変わりすぎて回答のしようがないということです。)



普通考えるのは ASCIIコード で対象参照すれば良いのではないでしょうか。
しかし,
規則が不規則な場合は配列やハッシュ(ハッシュもどき:結合配列)を使うかもしれません。



ActionScript1.0 or 2.0 で ASCII コードを使う方法

新規ドキュメントを作成して
フレーム1 に次のスクリプトをコピペして
「制御」→「ムービープレビュー」

-------------------------------------------------
// 変数 my_str の値を "A" とする
my_str = "A";
// 変数 my_str の 1 番目の文字を ASCII コードで出力
trace(my_str.charCodeAt(0));
// ↑ココで 65 が返るので 64 を引いてやる
trace(my_str.charCodeAt(0)-64);

// ↓"B"に関してもやってみる
my_str = "B";
trace(my_str.charCodeAt(0)-64);
// ↑ちゃんと 2 が返る
-------------------------------------------------



ActionScript1.0 or 2.0 で ハッシュもどきを使う方法

新規ドキュメントを作成して
フレーム1 に次のスクリプトをコピペして
「制御」→「ムービープレビュー」

-------------------------------------------------
// 変数 STR という Object を作成
STR = new Object();
// "A"エレメントの値を1,"B"エレメントの値を2,"C"エレメントの値を1 とする
STR = {A:1, B:2, C:30};

// "A"エレメントの値を取り出してみる
trace(STR["A"]);
// "B"エレメントの値を取り出してみる
trace(STR["B"]);
// "C"エレメントの値を取り出してみる
trace(STR["C"]);
// ↑ ちゃんと 「1」 「2」 「30」 が返りますね

// ちなみに値の型は何?
trace(typeof (STR["A"]));
// ↑numuber(数値) です
-------------------------------------------------



※ ASCII や ハッシュ が使えないバージョンでも
  場合によっては if文 を使えば乗り切れます。

ご使用の Flash のバージョンや ActionScript のバージョンなどがわかりませんので,
何とも言えませんが...。
(何とも言えないというのは場合によって答えがありすぎて,つまり変わりすぎて回答のしようがないということです。)



普通考えるのは ASCIIコード で対象参照すれば良いのではないでしょうか。
しかし,
規則が不規則な場合は配列やハッシュ(ハッシュもどき:結合配列)を使うかもしれません。



ActionScript1.0 or 2.0 で ASCII コードを使う方法

新規ドキュメントを作成して
フ...続きを読む

Qvlookup関数で検索値がブランクの時に、ブランクを返す事はできますか?

こんにちは、みなさん!!

【質問】vlookup関数で、検索値がブランクの時に、答えとして
    ブランクを表示する事はできますか?どうしても、エラーが
    表示されてしまいます。エラーではなく、ブランクを表示さ    せたいのですが。

ご存知の方いらっしゃいましたらよろしくお願いします。

Aベストアンサー

もとの式が分かりませんが、

=IF(検索値="","",VLOOKUP(検索値,範囲,列番号))

としたらどうでしょうか?

QPHPで、日本語からアルファベットに変換

PHP初心者です。
PHPプログラム上で、日本語からアルファベットに変換する書き方を探しています。
漢字仮名まじりの日本語を、アルファベットに変換できますか?

自分では、
$str = mb_convert_encoding($title,"ascii","UTF-8");
という風にやってみましたが、できませんでした。

どなたかお知恵をお貸しください。

Aベストアンサー

>> 日本語からアルファベットに変換する書き方を探しています。

ローマ字に変換する必要は無いのですよね?

英数字でもよいのなら
base64_encode("日本語");

アルファベットだけでなく数字や%などが混じっても良いのなら
urlencode("日本語")

いずれも逆の変換で元の日本語にも戻せます

QExcelで行を複数のセルの値を返す関数を

 いつもお世話になります。
ExcelでたとえばA列に名前、B列に住所、C列に電話番号というふうにデータを入力します。
 名前から住所、電話番号というふうに複数のセルを返すにはどうすればよいでしょうか?
 Vlookupの検索型で複数のセルの値を返したいのです。
 わかりづらい説明になって申し訳ありません。
どうか、御教授よろしくお願いします。

Aベストアンサー

何がしたいのかわかりかねますが、
(1) 名前を指定すると、住所、電話番号を得る。
(2) 名前が重複しており、そのすべての住所、電話番号を得る。
のどちらかだと(勝手に)思います。結果としてどういうものが欲しいのか、具体例を挙げて書いて頂くとありがたいのですが。

ともかく、(1)の場合、
住所は
=Vlookup(名前,A2:C200,2,0)
電話番号は
=Vlookup(名前,A2:C200,3,0)

で得られますよね?
データがA2:C200にあるとしています。名前の部分は、名前を入力するセル番地を指定してください。

(2)の場合、
2つ目の住所は、
=VLOOKUP(名前,INDIRECT("$A"&TEXT(MATCH(A9,$A$2:$A$200,0)+2,"###")&":$C$200"),2,0)
で、得られます。

じゃあ、登録住所が同じ名前で3つ以上あった場合は?・・・宿題とさせてもらいます。

そもそも、こんなことで良いのかどうかもわかりませんので、補足をいただければ幸いです。

何がしたいのかわかりかねますが、
(1) 名前を指定すると、住所、電話番号を得る。
(2) 名前が重複しており、そのすべての住所、電話番号を得る。
のどちらかだと(勝手に)思います。結果としてどういうものが欲しいのか、具体例を挙げて書いて頂くとありがたいのですが。

ともかく、(1)の場合、
住所は
=Vlookup(名前,A2:C200,2,0)
電話番号は
=Vlookup(名前,A2:C200,3,0)

で得られますよね?
データがA2:C200にあるとしています。名前の部分は、名前を入力するセル番地を指定してください。

(...続きを読む

Qかなで書かれた文字をアルファベットに変換できませんか?

エクセルででも、なんででもいいのですが、かなで書かれた文字をアルファベットに一度に変換できないでしょうか? たとえば、エクセルの関数phonetic を使えば、漢字を仮名に変換できますよね? それと同じように、仮名をアルファベットに変換する方法はないでしょうか? もし、そんな便利なことができたら、とってもうれしいのですが。。

よろしくお願いいたします。m(__)m

Aベストアンサー

以前、似た質問に回答しました。

No.806286 質問:Excelで五十音順に並べ替えたい
http://oshiete1.goo.ne.jp/kotaeru.php3?q=806286

No.8の手順3)で、

kakasi -Kj -Jj

を実行すると、

(株)アイウエオ
(株)イノウエ
(株)ウラシマ
(株)阿部商店
(株)石井商店
(有)アイウエオ

(kabu)aiueo
(kabu)inoue
(kabu)urashima
(kabu)abeshouten
(kabu)ishiishouten
(ari)aiueo

と変換できました。

参考URL:http://kakasi.namazu.org/, http://oshiete1.goo.ne.jp/kotaeru.php3?q=806286

Q<エクセル>離れたセルの値から○の数を返す関数を教えてください。

ABCDEFG列があったとします。それぞれの列2〜100行のセルに○が入っていて、AとCとEの列の○の値を数えたいのですが、countifでは不可能のようです。。。。
エクセル初心者ですみませんが教えて下さい。

Aベストアンサー

「=COUNTIF(A:A,"○")」「=COUNTIF(C:C,"○")」「=COUNTIF(E:E,"○")」
ではいけないんですか?

Q全角アルファベットを半角アルファベットに変換

Word 2003 で文書中の全角アルファベットを半角アルファベットに一括変換するにはどうすればよいでしょうか。一括が無理なら、個別でもかまいません。

Aベストアンサー

全角英字を半角に一括にするなら検索を使ってはいかが。
[検索]ダイアログで[ワイルドカード]にチェックを入れておきます。

検索する文字列 : [A-Z]  (AとZは全角英字にして記号は半角)

[見つかったすべての項目を強調表示する]へとチェックを入れます。
これで[すべて検索]ボタンを押します。

全角英字がすべて選択されたら、ANo.1の回答にもある[文字種の変換]
をします。

Qエクセルの関数で,たとえばB15のセルの値を返すには?

エクセルの関数の質問です。

たとえば,
A1のセルに7
A2のセルに8
が入力されているとします。

ここで,少し回りくどいのですが,
A1(の7)と,A2(の8)と,関数などを使って,
あるセルに,B15のセルの値を返したいのですが,
可能でしょうか?

間違ってますが,
=B(A1+A2)
のような式を書きたいのですが…。


あっさりと,
=B15
とすればいいのはわかっていますが,
やむえない事情がありまして…。

よろしくお願いします。

Aベストアンサー

=INDIRECT("B"&A1+A2)
で良いと思います。


人気Q&Aランキング

おすすめ情報