エクセル勉強中です。問題集で理解できないところがあります。
数式を解説頂けるとありがたいです。
よろしくお願いします。
(同一記号でのMATCH関数の照合形式の違いが解かっていません)
問題は、画像のような表があり●の付いた工事期間を求める物です。
表1の工事期間セルL2には
L2=TEXT(INDEX($B$1:$K$1,MATCH("●",B2:K2,0)),"m/d")&"~"&TEXT(INDEX($B$1:$K$1,MATCH("●",B2:K2,1)),"m/d")
として日付表示で工事期間が求められるのが回答です。
Match関数の照合の型は
1の場合 検査値以下の最大の値が検索
0の場合 検査値に完全に一致する値が検索
-1の場合 検査値以上の最小の値が検索
ですが、検査値が記号“●”での考え方はどういう事なのでしょうか?
MATCH("●",B2:K2,1)
この式で“8”が返るのは、
(B2:K2)=”●”で
{FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}
TRUEの最大値が8個目だからでよろしいのでしょうか。
学習なので問題に少し変化を加えてみました。
6/7が休日で全ての工区が休日をとったとします。それが表2です。
同様の式では工事期間の終了日が6/6となってしまいます。
工事終了日のMATCH関数の照合を-1にしてみました表3
検査値以上の最小の値が検索されますので、終了日と開始日が同じになります。
MATCHの検索文字を“●”ならダメですが表4
“*”に変えて
L23=TEXT(INDEX($B$8:$K$8,MATCH("*",B23:K23,0)),"m/d")&"~"&TEXT(INDEX($B$8:$K$8,MATCH("*",B23:K23,-1)),"m/d")
にすると目的が達成できます。ワイルドカードでの文字抽出と
“●”指定抽出 何が違ってくるのでしょうか?
すいません、根本的なことが理解できていないと思います。よろしくお願いします。
No.1ベストアンサー
- 回答日時:
Match関数についてのサイトです。
http://www.excel-list.com/match.html
*(ワイルドカード)が使えるのは実は
照合の型 が 0(完全一致)の場合だけです。
MATCH("*",B23:K23,-1))として使える理由ですが
"*"で文字で認識しています。
Match関数の使い方は、上記にあるような使い方が基本ですが
誰が見つけたかは不明ですが、先人の方が面白い事実に気が付かれたので
裏ワザとして、以下のような使い方があります。
一例ですが
A B
2 B
3 C
5 E
4 D
9 J
とでもデータを作ってみます。
=Match(1,A:A,0) とすれば完全一致ですので エラーが出ます。
=Match(10,A:A,1) とすれば、最後の行が出ます
=Match(1,A:A,-1) としても、最後の行が出ます。
完全一致(=)の場合は、最初に一致した行で検索は終了します。
その他の場合は、一旦すべてを検索して(> または <)該当するデータがない場合に
最後の行を表示するようです。
B列の様な文字列の場合は、JISコードに準じて検索されます。
=Match("Z",A:A,1) とかいれてZ存在しない、データのある文字より
Code関数で大きな数値がでる文字を検索させてみてください。
*(アスタリスク)ですが =Code("*")と Code("●") で結果をみたら
*(アスタリスク)の方が●よりCodeでは先になりますので
結果、●の最終列が計算結果として出ます。
MATCH("A",B23:K23,-1))
でも
MATCH("1",B23:K23,-1))
でも結果は同じではないでしょうか。
MATCH("◎",B23:K23,1))
とか、●より後にある文字を使えますね。
今回は 空白と ●だけですがその他の文字がある場合などに
検討されると良いと思います。
hallo-2007 様
ご回答ありがとうございました。
頭が固くてなかなか理解できずご返答までお時間がかかり申し訳ございません。
ご提示いただいたサイト何度も見ていたのですが、確かに完全一致の所のみ
ワイルドカード記載ですね。結果が表示されるので何の疑いもなく使えているのかと
思い込んでいました。それが、文字認識のJISコードでたまたまだったのですね。
ありがとうございます。勉強になりました。
文字として“*”という事なら開始日にわざわざ利用しなくとも完全一致ですから
“●”で良かったのですよね。
ひょんな事からど素人が裏ワザの世界に足を突っ込んでしまっていたとは。。。
ひとりで理解できるはずも無いわけですね。
アドバイス頂いていた項目自分なりに色々試して結論として
開始部分については、照合形式”0”で完全一致 完全一致だから検索値”●"で2列目で
TRUEとなり検索終了で”2”
完全一致だから場合によってはワイルドカードも利用できる。
終了部分については、検索範囲の文字"●"のJISコード ”8572”の為
照合形式で”1”を使用するには、検索文字列のJISコードを”8572”より大きい値を検索値とする。
例えば”Z”のJISコード ”9050”で最大値”8”が返ってくる。
参照形式”-1”で抽出する場合は”8572”より小さい値で検索する。例えば”@”
”@”のJISコード”8567”
といった感じで大丈夫ですよね。MATCH関数の正攻法では無いみたいですが、たまたまではなく利用できる裏技でいいのですよね?しかし、この問題を求める為のこの数式は正しいのか?結果は得られているがもっと簡素な求め方があるのか??エクセル関数ってやり方次第で多彩な数式を利用できるので何が正しいのか?奥が深いですよね。。。
ありがとうございました。
No.5
- 回答日時:
NO.2です。
>ワイルドカードでの文字抽出と“●”指定抽出何が違ってくるのでしょうか?
>L23=~~~TEXT(INDEX($B$8:$K$8,MATCH("*",B23:K23,-1)),"m/d")
照合の型-1で検索値に"*"を使用しても、ワイルドカードとして機能してません。
文字"*"アスタリスクなので検索値に使用して問題ないです。
なので、MATCH("*",B23:K23,-1)の結果は正しいです。
申し訳ありません。m(_ _)m
kito2015 様
すいません。皆さんの様に頭が柔軟で無い為一つ一つの理解に時間を要しご返答が遅くなりました。
再びありがとうございます。
アスタリスクも単に文字として機能しているようですね。
私の頭の中ではまだ何となくの理解ですが。。。
ただ、補足にも上げたのですが、皆様からのご回答で文字としてのコードで機能するようなのですが、
以上以下のコード比較で頭がまとまりません。
これを考えるにはレベルが低すぎる?のかもです。
すいません。ありがとうございました。
No.4
- 回答日時:
セルB3に =MATCH("●",$B$2:B2,?) 照合の型に「0,1、-1」をそれぞれ入て数式を右方向へコピー、
または=MATCH("*",$B$2:B2,-1) の式を右方向へコピーしてみた場合に返ってくる値の違いが分かると思います。
Esupuresso 様
ご回答ありがとうございました。ご返答まで遅くなり申し訳ございません。
一見考え方を理解するのに非常に解かり易いものでした。
しかし、頭がこんがらがってしまって整理するのに時間を費やしてしまいました。
未だに理解が出来ていません。
とりあえず、“*”はワイルドカードではなく文字として認識していて文字コードにて
以上・以下の比較で数式結果が出るという事のようですが、
検査値が小さい場合(質問補足3-図番1)と検査値が大きい場合(質問補足3-図番2)で結果が
なぜ同じになるのでしょう?また、同じように検査値が大きい場合(質問補足3-図番3)でも
結果が変わるものもあるようです。
理解するのに文字列は余計にややこしいそうなので同値の数字に置き換えて考えてみましたが、
照合形式“-1”の定義
-1 の場合検査値以上の最小の値で検索。検査範囲 を、降順に並べ替えておく必要があります。
以上以下でいうと(質問補足4-図番5と6)は以上という事で言えば、同じだと思うのですが
なぜ結果が範囲右端列と左端列に別れるのでしょう??
法則的なものが未だ理解できません。私のレベルではまだ無理があるのかもわかりません。
すいません。早々にご回答頂いていたにも関わらず、お礼が大変遅くなり申し訳ございませんでした。
ありがとうございました。
No.3
- 回答日時:
No.2です。
>MATCH("*",B23:K23,-1)だと、B23:k23で文字列を検索して一致した(相対的な位置)最少が結果になります。
>C23に"●"があるので、2が本来の結果だと思います。でも実際には10が結果として返ってきます
10ではなく、8が正しいです。
No.2
- 回答日時:
Match関数の照合の型-1と1を使用する場合、説明にもありますが並び替えが必要です。
並び替えて、同じ値は連続する状態でないと正しい結果を得られません。
表1の場合、"●"が連続していているので並び替えを行った状態と言えます。
表2~4の場合、範囲の"●"と"●"の間に空白が入っているので、
並び替えを行っていない状態なので一致するものがあれば一応結果が返しますが、
正しい結果とは限りません。
>MATCH("●",B2:K2,1)検査値が記号“●”での考え方はどういう事なのでしょうか?
>{FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}
>TRUEの最大値が8個目だからでよろしいのでしょうか。
正しいと思います。
検査値が文字の場合、一致(True)か不一致(False)で通常は一致した(相対的な位置)最大の8が
結果となります。
検査値が数値の場合、一致するものがなければ検査値以下の最大の値が結果になります。
>ワイルドカードでの文字抽出と“●”指定抽出何が違ってくるのでしょうか?
>L23=~~~TEXT(INDEX($B$8:$K$8,MATCH("*",B23:K23,-1)),"m/d")
説明に、「検索の型 が 0 で、検索値が文字列の場合、半角の疑問符 (?) または半角のアスタリスク (*) をワイルドカード文字として使用できます。」と書かれてます。照合型-1と1で使えるとは書かれてません。
図4で、正しい結果が得ているように見えますが本来の使い方からすると間違いだと思います。
"*"は任意の文字列を表し、検索対象が文字であれば一致(True)、それ以外の数値、空白は不一致(False)になります。そして、照合の型-1は「検査値以上の最小の値が検索されます。」と書かれてます。
MATCH("*",B23:K23,-1)だと、B23:k23で文字列を検索して一致した(相対的な位置)最少が結果になります。
C23に"●"があるので、2が本来の結果だと思います。でも実際には10が結果として返ってきます。
動作をみると一致した(相対的な位置)最少ではなく最大の結果を返してくるみたいです。
説明からすると間違った動作ですが、今回ように一致した(相対的な位置)最大を求める場合には使えるのかもしれません。数値と文字が混在していても結果を返してくれます。
あくまで、私の理解ですので間違ってる可能性があります。
結果の違いを見るなら、セルに一文字づづ入れて、
パターン1 ●●●△● 検索値は"●"
パターン2 ●●△●● 検索値は"●"
パターン3 △●△●△ 検索値は"●"
パターン4 日月火水木 検索値は"金"
MATCH関数のそれぞれの検索型で違いを確認されたらいいかと思います。
kito2015 様
ご回答ありがとうございました。ご返答まで遅くなり申し訳ございません。
ちょっとこの問題を考えるには私のレベルでは早すぎたようで頭の中がごちゃごちゃです。
No1様のご回答より少しまとまりかけていた頭ですが、また崩壊寸前です。
仰って頂いているように空白があり並び替えが出来ていない文字列の配列では全てが
正しい結果を返さないようです。
自分なりに頂いたアドバイスより導き出した法則ですが
1 昇順を利用する照合形式 ”1” 検査値以下の最大の値で検索の場合
全ての数値(検索範囲のJISコード等)の上限下限の範囲外にする。
(今回はすべて同一記号 同一JISコードなので検索文字のJISコードよりも
大きい・小さいJISコードの文字を検索文字とする。
・検索値文字のJISコード数値が検索範囲文字JISコードより小さい場合はエラーとなる
・逆に大きい場合は値の最大値となる
2 降順を利用する参照形式 ”-1” 検査値以上の最小の値で検索の場合
では逆に
・検索値文字のJISコード数値が検索範囲文字JISコードより大きい場合はエラーとなり
・逆に小さい場合は値の最大値となる
これでNo1のお礼の検証時”●”と”@”他、数個試した時は問題なく求められていたのですが、
▲と● ◎と●では上記の法則が崩れてしまいます。(質問補足1-図番1)
お勧め頂きましたパターン1~4もやってみました。(質問補足2-図番1)
そのまま、G42=MATCH("●",$B42:$F42,1) で照合形式を変えてみましたが
まぁバラバラです。違いがよく解かりました。
開始日については、完全一致で求められるので数値に変えて行い、
終了日については、MATCH関数について質問をしておきながら元も子もないですが、
MAX関数を利用する方が楽なのかも??と考えてみました。
(ごちゃごちゃした数式なのかも解かりませんが)質問補足2-図番2-2)
MATCH関数の正しい使い方ではないようですから、エクセルに精通されている方なら
こういう時どのような数式を利用されるのでしょうかね??
あっ!すいません。
お忙しい中ご丁寧にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) エクセルの関数について 5 2023/01/26 15:26
- Excel(エクセル) Excel 値を返す数式についてです 3 2022/11/21 20:08
- Excel(エクセル) INDEX関数とMATCH関数を使用し値を返す数式についてです 2 2022/04/20 13:40
- その他(Microsoft Office) Excel 2列の値を返す数式についてです 1 2022/11/23 22:59
- Excel(エクセル) エクセル 関数について質問です。 2 2022/10/03 11:14
- Excel(エクセル) この関数の誤りを教えて下さい。 3 2023/08/08 07:36
- Excel(エクセル) Excel 組み合わせ関数の使い方を教えてください。 1 2023/08/11 17:45
- Visual Basic(VBA) Excelのマクロコードについて教えてください。 1 2022/03/27 13:25
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのツールバーから数値...
-
祝日と土曜、日曜の合計をカウ...
-
【マクロ】2回実行したら、エ...
-
特定の文字列を含む、住所を抽...
-
EXCELの散布図で日付が1900年に...
-
マイクロソフトのPADを使ってい...
-
Excel分数の表示について
-
Excelで表を作ったところに文字...
-
マクロエクセルのブロック解除
-
文字2桁、3桁交じりの文字列...
-
絶対参照
-
Excelについての質問です。 B2...
-
在庫管理表に使うエクセルの関...
-
【マクロ】VLOOKUPにて参照元に...
-
【EXCEL】画像の黄色部分の抽出...
-
DATE関数で現在の年齢を出した...
-
ユーザー定義関数をアドイン登...
-
エクセルでの作業計算方法について
-
行数が不規則な一週間ごとの合...
-
Excelピボットテーブルの1行目
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報
文字のコードでは完全でないときがあるようです。
MATCH関数で質問しておいて元も子もないですが。。。
質問補足3
質問補足4