痔になりやすい生活習慣とは?

お世話になります
INDEXとMatchを使い行の一番右(左から順にセルが埋まるので右端のセルが一定では無い)の値を返すようにしました。以下です。

しかし、一番右から2番目のセルの値(求めたものの左隣り)もほしいのです。
その場合はどうしたらよいのでしょうか?

=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))))

宜しくお願い致します。

A 回答 (8件)

> INDEXとMatchを使い行の一番右(左から順にセルが埋まるので右端のセルが一定では無い)の値を返すようにしました。



数値にも文字列にも対応したすばらしい式ですね。感心しました。

> 一番右から2番目のセルの値(求めたものの左隣り)もほしいのです。

こんな数式を作れる人が何をおしゃいますことやら・・・。
左隣ならINDEXを1つ少なくするだけのことですから

=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)

でいいんじゃないでしょうか?
    • good
    • 0
この回答へのお礼

あああ。そんな簡単なことで良いのですね。

もともとの式は似て非なるものを前任の方が残して行ったので、それをそれを紐解いて応用して作成したものなのです。
あるものを理解し紐解いて応用できたのですが、そこにない情報をどのように組み入れるのかがさっぱりわからず。。。。

なるほどぉ。。。見るとわかるのですが、作り出せないので感動しました。
ありがとうございました。

お礼日時:2008/10/01 14:21

◆こんな方法もありますよ


=INDEX(1:1,LARGE(INDEX((1:1<>"")*(COLUMN(1:1)),),2))
★後ろの数字が一番右から何番目かになります
    • good
    • 0
この回答へのお礼

本当にいろいろな方法が考えられるものですね。これは単純化されていて良いですね。

次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。

ありがとうございました。

お礼日時:2008/10/01 16:20

左隣が空白の場合にも対処するにはMAXの代わりに、LARGE関数使えばよかったのですね。



{=INDEX(1:1,LARGE(IF(ISBLANK(1:1),0,COLUMN(1:1)),2))}

教えて!は本当に勉強になります。
    • good
    • 0
この回答へのお礼

本当にいろいろな方法が考えられるものですね。

次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。

ありがとうございました。

お礼日時:2008/10/01 16:19

みなさんいろいろなやり方を考えますねぇ



僕もひとつ考えました。配列数式を使いますが、式が直感的でわかりやすかなと思います。

{=INDEX(1:1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))}

左隣はやはりINDEXの-1なんですが、その左隣が空白の場合はどうすればいいのか。。。正直、悩んでます。
    • good
    • 0
この回答へのお礼

本当にいろいろな方法が考えられるものですね。

次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。

ありがとうございました。

お礼日時:2008/10/01 16:19

左から順に入っているのなら 既に出ている方法(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))
    • good
    • 0
この回答へのお礼

なるほどぉぉぉ。

本当に勉強になりますねぇ。。。。

ありがとうございました。

お礼日時:2008/10/01 14:33

INDEX関数の列を示す引数のところにー1すれば済む。


同行一番右は
=INDEX(A1:Z1,1,COUNTA(A1:Z1)) (Z列までが入力範囲として)
もし式複写するなら =INDEX($A1:$Z1,1,COUNTA($A1:Z$1))など
または
=INDEX(1:1,1,COUNTA(1:1))
その左はー1列すればよい
=INDEX(A1:Z1,1,COUNTA(A1:Z1)-1)
または
=INDEX(1:1,1,COUNTA(1:1)-1)
    • good
    • 1
この回答へのお礼

そのようですね。下の回答を拝見して学びました。
このマイナス1が曲者でした。

このような使い方ができるとは知らなかったので、とても役に立ちました。

ありがとうございました。

お礼日時:2008/10/01 14:31

よくもまあこんな複雑な式を作りましたねぇ。


2,3ケ月後に見て、直ぐ分かるのだろうかと人事ながらちょと心配。。。(^^;;;

途中に未入力セルがない前提なら、、、

●右端の値

  =INDEX(1:1,0,COUNTA(1:1))

●右端よりひとつ前の値

  =INDEX(1:1,0,COUNTA(1:1)-1)

●右端より二つ前の値

  =INDEX(1:1,0,COUNTA(1:1)-2)


外しましたらご容赦願!
以上です。
 
    • good
    • 0
この回答へのお礼

ありがとうございます。
そうなんです。未入力セルがなければ一番右はこれだと私も思うのです。
ところが、空白があるもので、これだとだめなんです。

でも、右寄りひとつ前の値への応用は#1の場合と同じでこれが私にはわからなかったので、大変勉強になりました。

お礼日時:2008/10/01 14:26

複雑な式なので、にわかには理解できませんでした。


で、
こんなのでは駄目なんですか。

一番右
=INDIRECT(ADDRESS(1,COUNTA(1:1)))
左隣
=INDIRECT(ADDRESS(1,COUNTA(1:1)-1))
    • good
    • 0
この回答へのお礼

ありがとうございます。
INDIRECTは使ったことがない関数で、今パラパラと本をめくってなるほどと思いました。
次回何かの折にぜひ使用したいものです。

ところが今回は空白があるものでこれだとだめなんです。

でも、右寄りひとつ前の値への応用は#1の場合と同じでこれが私にはわからなかったので、大変勉強になりました

お礼日時:2008/10/01 14:29

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

このQ&Aを見た人はこんなQ&Aも見ています

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

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

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

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

QExcel 表から値をさがして隣のセルの値を返す

お願いします、教えて下さい。
VLOOKUPのようにいまから左端に番号などを入れる手間無く、値を返したいです。

一つの目のbook     2つ目のbook
 A列  B列      A列   B列
顧客   担当    木下麻衣
小野博美 佐藤          ↑
田中香織 吉岡       ここに関数を入れて、B列の金田を返す
木下麻衣 金田   

同シート上で表をふたつ作っても構いません、どなたか教えて下さい。 

Aベストアンサー

質問者さんの意図にあっているかどうかわかりませんが。

内容を見る限りでは2つ目のbookのA列が検索値に
なるようでしたら、

=VLOOKUP(A1,Sheet1!A1:B5,2)

では如何でしょうか?
1つ目のbookをsheet1、2つ目のbookをsheet2で考えてます。
また、左上よりA1としてますので確認ください。

QExcelで最大値の入っている数値の右側にある項目名を拾いたい。

Excelで最大値の入っている数値の右側にある項目名を拾いたい。

下のようなデータがあります。(A列は年、Bは何かの生産量・・とします)

1 2000 7
2 2001 11
3 2002 14
4 2003 10
5 2004 9
6 2005 8

生産量(B列)の最大値を求めるのは=MAX(B1:B7)で"14"が得られると思いますが、求めているのは"14"の入っている年の"2002"という数字を隣のC1に入れたいと思います。
どのような関数が必要でしょうか?

Aベストアンサー

こんばんは!
一例です。

↓の画像でC2セルに表示するようになりますが・・・

C2セルの数式は
=INDEX($A$2:$A$1000,MATCH(MAX(B2:B1000),B2:B1000,0))
にしています。
(エラー処理はしていません)
A列の1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば幸いです。m(__)m

Q自分の左隣のセル

セルB1に=A1と入力してますが、
左隣のセルという指定は出来ますか?
行は同じで、列番号-1というように指定したいです。

Aベストアンサー

こんばんは!

当方もNo.2さんと同じ疑問を持ちつつ・・・

とりあえず
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))
という数式を入れておけば、どのセルに数式を入れても
常に左隣のセルが返ります。

※ A列に上記数式を入れるとエラーになります。m(_ _)m

QExcel 右から2つ目の数字を抜き出し数字以外の時は、空白としたい。

Excel2007です。
画像の
B列から数字(1桁と2桁)を1桁ずつC列とG列に取り出したい。
右1番目は、=right(B1,1)で取り出せます。
右2つ目の数字を取り出す方法を教えて下さい。2つ目が数字以外(全角です。)のときは、空白としたい。
よろしくお願いします。

Aベストアンサー

・B列の文字は全部で何桁あるのか?
・特に1文字だけ(=2文字目が存在しない事)があり得るのか?

で微妙に違ってきます。

絶対に2文字以上あるなら
=IF(ISERROR(VALUE(ASC(MID(B1,LEN(B1)-1,1))))=TRUE," ",MID(B1,LEN(B1)-1,1))

2文字未満があり得るならLEN(B1)が2未満の場合の処理をIF文の一段外側に追加でしょう。

Excel2003だと全角数字でもValue関数でエラーになりませんのでASC関数は無くてもOK(他バージョンは不明)
ISERRORはTRUEかFALSEを返すので=TRUEは無くても動きます

Q値が入っている一番右のセル位置を返す方法

たとえばA行に値(文字列)が横に並んでいるとして
値が入っている一番右のセルの位置を返すようにするにはどのようにすればいいでしょう?

Aベストアンサー

セルの位置を返したいのであれば
=CHAR(MATCH(MAX(1:1)+1,1:1,1)+64)&1
でどうでしょうか。

Q値の入っているセルのうち、一番右側の値を取得したい(エクセル)

現在、データを左から右へ順次追加しています。
一番右側が、最新データとなります。
やりたいことは、複数の行にある、最新データを拾って、グラフを作成したいと思ってます。
どうやったら、できるのでしょうか?


<イメージ?>
●:最新データ
×:古いデータ
○:空欄
●のみを拾って、グラフを作成したい。

×××××●○
××××●○○
×●○○○○○
××××●○○

Aベストアンサー

こんにちは。maruru01です。

データがB列から、順に右へ入力されていくとして、A列に最新データを表示する場合は、

A1を選択して数式バーに、

=INDIRECT(ADDRESS(ROW(),MAX(IF(LEN($B1:$M1),COLUMN($B1:$M1)))))

と入力し、まだカーソルが数式バーにある状態で[Ctrl]と[Shift]を押しながら[Enter]を押して決定します。
(数式の両端に「{}」が付いて配列数式になります。)
これを下の行へコピーします。
あとは、このA列の値でグラフを作成すればいいと思います。
なお、上記の数式ではデータ入力範囲をB~M列にしましたが、後ろの列は、データを入力する可能性のある最大の列にして下さい。
(何列まで入力するか分からない場合は、エクセルの最終列のIVにしましょう。)

各関数については、ヘルプなどを参照して下さい。
配列数式については、以下のページを参考にして下さい。

http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

参考URL:http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

こんにちは。maruru01です。

データがB列から、順に右へ入力されていくとして、A列に最新データを表示する場合は、

A1を選択して数式バーに、

=INDIRECT(ADDRESS(ROW(),MAX(IF(LEN($B1:$M1),COLUMN($B1:$M1)))))

と入力し、まだカーソルが数式バーにある状態で[Ctrl]と[Shift]を押しながら[Enter]を押して決定します。
(数式の両端に「{}」が付いて配列数式になります。)
これを下の行へコピーします。
あとは、このA列の値でグラフを作成すればいいと思います。
なお、上記の数式ではデータ入...続きを読む

QExcel 空白を含む列範囲からn番目のセル

Excel2010を使用しています。


皆様のお知恵をお貸しください。

下の図のようにA2からA10までの表があり、空白も混じっています。
C3セルに2と入力したら、A2からA10までの表から空白を除いた2番目の数値
つまり、7がC5セルに表示されるようにしたいのです。

ご教示のほど、宜しくお願い致します。

Aベストアンサー

次の数式で良いと思います。
=INDEX(A$1:A$10,SUMPRODUCT(SMALL((A$2:A$10<>"")*ROW(A$2:A$10)+(A$2:A$10="")*100,C3)),1)
SUMPURODUCT関数を使わなくてもSMALL関数でn番目の行番号を抽出すればINDEX関数で値を取り出せます。
但し、計算途中で配列値を扱うので数式の確定時にCtrl+Shift+Enterの操作が必要です。
SUMPURODUCT関数を使うと通常のEnterキーで確定できます。
SMALL関数の範囲は値が入力されているセルに対して行番号を適用し、未入力に対しては定数の100を適用しています。
実情に合わせて検索範囲と定数を変更してください。

Qエクセル 同じ値を探して隣の数値をコピーする

エクセルで以下の作業を簡単にする方法を教えて下さい。

Sheet1の1行目には見出しがあり、A列とB列の2行目から下にデータが入っています。(約200行)
A列     B列
田中    13
山本     8
谷口    11
鈴木    6
佐々木    9
奥村     15




Sheet2のA列1行目から下には別のデータが入っています。(約600行)
A列
太田川
山村
田中
多賀先
鈴木
奥村
幸田




Sheet2のA列のデータと完全一致するデータ(名前)をSheet1のA列から探して、同じ名前があれば、その隣のB列にある数値をSheet2のB列に貼り付ける。
見つからない場合はSheet2のB列は空欄のままです。
Sheet2
A列      B列
太田川     
山村
田中    13
多賀先
鈴木     6
奥村    15
幸田


Aベストアンサー

シート2のB2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet1!A:A,A2)=0,"",VLOOKUP(A2,Sheet1!A:B,2,FALSE))


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング