□ 戻る □VBAのサロン □ 使用方法 □ 新着記事 □ 新規に質問する！ □ トピック一覧 □ 検索 □ 過去ログ
[ 最新記事及び返信フォームをトピックトップへ ]
| 特定文字列を合致する別の列の値を取り出したい | |
|---|---|
[134341] 特定文字列を合致する別の列の値を取り出したい- ■親トピック/記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/17(15:10)) □U R L/ 下記の表の特定の文字列からのデータの取り出しを考えております。 A（ID) B（氏名） C（家族氏名） D（扶養） 1 1101 大阪 太郎 大阪 花子 控除対象外 2 1101 大阪 太郎 大阪 和子 扶養親族 3 1102 三重 次郎 三重 朋子 扶養親族 4 1103 奈良 三郎 奈良 恵子 同一生計配偶者 5 1104 京都 ひろし 京都 佳代 同一生計配偶者 6 1104 京都 ひろし 京都 政子 控除対象外 7 1104 京都 ひろし 京都 和彦 扶養親族 以下A列にID、B列に氏名（家族の人数分）、C列に家族氏名 D列に扶養状態が入力されているシートから氏名をキーにD列の「扶養親族」の家族氏名Cを取り出したり、D列「同一生計配偶者」の家族氏名C取り出したい考えておりますが、ご教示いただけますでしょうか？ ++++++++++++++++++++ OS ⇒Windows 10 Version⇒Excel 2021 ++++++++++++++++++++ | |
[134342] Re[1]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ 半平太 -(2024/04/17(16:37)) □U R L/ 実際にどこにどう出すのか分からないと具体案が提示できませんが。 ※特に2名以上該当者が居る家族の場合 | |
[134347] Re[2]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/17(18:03)) □U R L/ 半平太様 ＞実際にどこにどう出すのか分からないと具体案が提示できませんが。 ＞※特に2名以上該当者が居る家族の場合 説明不足で申し訳ございません。 前述のデータベースが「table1」シートにあります。 別の出力用のシート「Sheet1」が有り ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ A B C D E F 1該当者指名 tabel1!B1 2 3 4同一配偶者の氏名 C4 5 6扶養親族の氏名等 C6 7 C7 8 C8 C4に同一生計配偶者の氏名を抽出したい C6〜C8に扶養親族の氏名を抽出したい と考えております。 なかなかうまく説明ができず申し訳ございません。 | |
[134349] Re[3]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ 半平太 -(2024/04/17(19:13)) □U R L/ 処理対象範囲は、動的に取得していますので、マニュアルで調整する必要ないです。 C4セル =LET(r,tabel1!B2:XLOOKUP(B1,tabel1!B:B,tabel1!D:D,"",0,-1),FILTER(CHOOSECOLS(r,2),(CHOOSECOLS(r,1)=B1)*(CHOOSECOLS(r,3)="同一生計配偶者"),"")) C6セル =LET(r,tabel1!B2:XLOOKUP(B1,tabel1!B:B,tabel1!D:D,"",0,-1),FILTER(CHOOSECOLS(r,2),(CHOOSECOLS(r,1)=B1)*(CHOOSECOLS(r,3)="扶養親族"),"")) 下にコピーは不要です <Sheet1 結果図> 行 ________A________ _____B_____ ____C____ 1 該当者指名 京都 ひろし 2 3 4 同一配偶者の氏名 京都 佳代 5 6 扶養親族の氏名等 京都 和彦 7 京都 和代 <tabel1 サンプル> 行 ___A___ _____B_____ ______C______ _______D_______ 1 A（ID) B（氏名） C（家族氏名） D（扶養） 2 1101 大阪 太郎 大阪 花子 控除対象外 3 1101 大阪 太郎 大阪 和子 扶養親族 4 1102 三重 次郎 三重 朋子 扶養親族 5 1103 奈良 三郎 奈良 恵子 同一生計配偶者 6 1104 京都 ひろし 京都 佳代 同一生計配偶者 7 1104 京都 ひろし 京都 政子 控除対象外 8 1104 京都 ひろし 京都 和彦 扶養親族 9 1104 京都 ひろし 京都 和代 扶養親族 | |
[134351] Re[4]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/18(08:58)) □U R L/ 半平太 様 ご返信ありがとうございます。 早速試してみます。 | |
[134356] Re[5]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/18(15:51)) □U R L/ 半平太 様 大変恐縮なのですが、試してみたところ#NAME?と返ってきます。 自分で調べたもののわかりません。 間違いなく同条件で貼り付けているのですが、間違っている箇所等ありますでしょうか？ | |
[134358] Re[6]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ 半平太 -(2024/04/18(16:03)) □U R L/ >Version⇒Excel 2021 バージョンは正しいですか？ | |
[134359] Re[7]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/18(16:16)) □U R L/ versionは2021となっっているのですが、社内のEXCELになっておりアップデートができていない状況です。 確認しますと、CHOOSECOLS関数がでてこない状態です。 他の関数は関数の挿入にて確認できました。 大変申し訳ございません。 | |
[134361] Re[8]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ 半平太 -(2024/04/18(16:42)) □U R L/ ＞確認しますと、CHOOSECOLS関数がでてこない状態です 済みません。その関数を使っていたのを失念しました。m(__)m これでやってみてください。 ↓ C4セル =LET(r,tabel1!B2:XLOOKUP(B1,tabel1!B:B,tabel1!D:D,"",0,-1),FILTER(INDEX(r,0,2),(INDEX(r,0,1)=B1)*(INDEX(r,0,3)="同一生計配偶者"),"")) C6セル =LET(r,tabel1!B2:XLOOKUP(B1,tabel1!B:B,tabel1!D:D,"",0,-1),FILTER(INDEX(r,0,2),(INDEX(r,0,1)=B1)*(INDEX(r,0,3)="扶養親族"),"")) | |
[134365] Re[9]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/18(19:27)) □U R L/ ありがとうございます。 無事希望の値が返ってきました。 もしよろしければ、数式の簡単なご説明いただければ幸いです。 | |
[134366] Re[10]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ 半平太 -(2024/04/18(20:51)) □U R L/ =LET(r,tabel1!B2:XLOOKUP(B1,tabel1!B:B,tabel1!D:D,"",0,-1),FILTER(INDEX(r,0,2),(INDEX(r,0,1)=B1)*(INDEX(r,0,3)="扶養親族"),""))
(1)~~~~~~~~~~~~~(2)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(3)~~~~~~~~~~~~(4)ア~~~~ (5) ~~~~~~~ (4)イ ~~~~~~~
(1)tabel1のデータ範囲は何度も使うので、変数「r」に代入して、以後数式をシンプルに仕上げる方針にする。
(2)データ範囲は、左上のB2セルから、右下つまりD列の最終セルまで。
D列の最終セルは以下の手順で見つける
Xlookup関数で、検索値を"*"にして、何か文字が入っているセルを下から(-1)検索する。
検索値の*は、ワイルドカードなので、文字なら何でもヒットしてくれる。
(3)求めるデータが入っているC列は、rの2列目である。その中から以下のフィルタにかけて抽出する
(4)抽出条件は
ア. ｒの1列目がB1とイコールであること(真偽値が返る)
イ. ｒの3列目が"扶養親族"とイコールであること。(真偽値が返る)
(5)上記４の条件は「且つ条件」なので、それぞれの真偽値を掛け算する。
※真偽値が四則演算に組み込まれると、Trueは1、Falseは0として扱われる。
逆に1(正確には0以外の数値）をフィルタ条件にするとTrueとして作用し、
0はFalseとして作用する。 | |
[134370] Re[11]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/19(08:48)) □U R L/ 半平太 様 ご返信遅くなり申し訳ございません。 丁寧なご説明ありがとうございます。 | |
[134374] Re[12]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ じゃこ -(2024/04/19(10:17)) □U R L/ <Sheet1 結果図> 行 ________A________ _____B_____ ____C____ 1 該当者指名 京都 ひろし 2 3 4 同一配偶者の氏名 京都 佳代 5 6 扶養親族の氏名等 京都 和彦 7 京都 和代 8 該当者指名 奈良 三郎 9 10 11 同一配偶者の氏名 空白 12 13 扶養親族の氏名等 奈良 恵子 14 何度も申し訳ございせん。 上の月分の表示結果の奈良 三郎 分の奈良 恵子 であれば C11セルに =LET(r,tabel1!B2:XLOOKUP(B8,tabel1!B:B,tabel1!D:D,"",0,-1),FILTER(INDEX(r,0,2),(INDEX(r,0,1)=B8)*(INDEX(r,0,3)="同一生計配偶者"),"")) C13セルに=LET(r,tabel1!B2:XLOOKUP(B8,tabel1!B:B,tabel1!D:D,"",0,-1),FILTER(INDEX(r,0,2),(INDEX(r,0,1)=B8)*(INDEX(r,0,3)=""),"扶養親族")) と設定したのですが、思った結果が返ってこないです。 ご教示いただければ幸いです。 | |
[134375] Re[13]: 特定文字列を合致する別の列の値を取り出したい- ■記事引用/メール受信=OFF■ □投稿者/ 半平太 -(2024/04/19(10:51)) □U R L/ 済みません。数式がおかしかったです。 m(__)m 解説も 提示した数式とマッチしていません。(ワイルドカードなんて存在しない） 以下に変更してください。 C4セル =LET(r,tabel1!B$2:XLOOKUP("*",tabel1!B:B,tabel1!D:D,"",2,-1),FILTER(INDEX(r,0,2),(INDEX(r,0,1)=B1)*(INDEX(r,0,3)="同一生計配偶者"),"")) C6セル =LET(r,tabel1!B$2:XLOOKUP("*",tabel1!B:B,tabel1!D:D,"",2,-1),FILTER(INDEX(r,0,2),(INDEX(r,0,1)=B1)*(INDEX(r,0,3)="扶養親族"),"")) C11セルは、C4をコピペする C13セルは、C6をコピペする | |
[134377] Re[14]: 特定文字列を合致する別の列の値を取り出したい- ■  / 記事引用/メール受信=OFF■
□投稿者/ じゃこ -(2024/04/19(14:46)) □U R L/ 半平太様 ばっちりでした。 長い間お付き合いいただきありがとうございました。 | |