excel初心者です。excelの文字列中の一部が、決まった検索ワード(複数、別シートに一覧になっている)と一致する場合に、その検索ワードに対応する数字がふられるようにする数式を教えてください。countifやvookupでやってみましたが、検索ワードが複数なのでワイルドカード*が使えず、挫折しました。
sheet1が基データで、sheet2が検索ワードとそれに対応する数字のリストです。sheet1のB列に検索ワードの対応数字を入れたいです。sheet1のB列にどんな数式を入れたらいいのでしょうか。
sheet2の検索ワードの範囲はA1~B65です。
*追伸:サンプルの各シートのA列とB列の間にスペースを入れたつもりですが、質問文では削除?されて、くっついた表示になっています。見づらくてすみませんが、数字はB列だと思ってください。もうしわけありません。
サンプル:
sheet1
(A列)
山口理事長
佐藤名誉会長
小林事業本部長
高橋代表取締役社長
高田課長
山本専務理事
sheet2
(A列) (B列)
社長 1
会長 1
代表取締役 1
理事 2
本部長 2
専務 3
(以下50個ほど続きます。B列の数字は重複します)
↓以下のようにしたいです
sheet1
(A列) (B列)
山口理事長 2
佐藤名誉会長 1
小林事業本部長 2
高橋代表取締役社長 1,1
高田課長
山本専務理事 3,2
アドバイスの程、何卒よろしくお願いします。
No.2ベストアンサー
- 回答日時:
複雑な関数を使ってもマクロを使用してもシート2の項目が多くなりますと計算速度も遅くなります。
作業列を使って対応することが最も簡単で計算にも負担のかからない方法です。
シート1ではA3セルから下方にお示しのデータが入力されているとします。
またシート2でがA1セルから下方にっく伯セルが無い状態で下方にお示しのデータが、また、B1セルから下方にお示しの数値が入力されているとします。
これらのデータをシート1のD1セルから右横に社長などの文字を、D2セルから右側に点数を表示させることにします。
そのためにD1セルには次の式を入力してD2セルまでドラッグコピーしたのちに右横方向にドラッグコピーします。
=IF(INDEX(Sheet2!$A:$A,COLUMN(A2))="","",IF(ROW(A2)=1,INDEX(Sheet2!$A:$A,COLUMN(A2)),IF(ROW(A2)=2,INDEX(Sheet2!$B:$B,COLUMN(A2)),"")))
D3セルには次の式を入力したのちに右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。
=IF(OR($A3="",D$1=""),"",IF(COUNTIF($A3,"*"&D$1&"*"),C3&","&D$2,C3))
その後にB3セルには次の式を入力して下方にドラッグコピーします。
=MID(SUBSTITUTE(INDEX(D3:XX3,COUNTIF(D$1:XX$1,"?*")),"0",""),2,3)
これでお望みのデータがB列に表示されます。
作業列が目障りでしたらそれらの列を選択して右クリックし「非表示」を選択すればよいでしょう。
なお、山田専務理事は2,3と表示されますがシート2での並びを専務が理事よりも上になるように並び替えることで3,2のように表示させることができます。
お礼が大変遅くなり申し訳ありません。
何度も試してみましたが、知識が少なくうまくいきませんでした・・
そのうち仕事でこの作業が不要になってしまいました。
でもすぐにこんな複雑で丁寧な回答をいただき、感謝しています。
どうもありがとうございました。
No.1
- 回答日時:
こんにちは!
VBAになってしまいますが、一例です。
Alt+F11キー → メニュー → 挿入 → 「標準モジュール」を選択 → VBE画面に
↓のコードをコピー&ペースト → Excel画面に戻りマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
尚、両Sheetともデータは1行目からあるとします。
Sub Sample1() 'この行から
Dim i As Long, k As Long, buf As String, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
wS1.Columns(2).ClearContents
For i = 1 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To wS2.Cells(Rows.Count, 1).End(xlUp).Row
If InStr(wS1.Cells(i, 1), wS2.Cells(k, 1)) > 1 Then
buf = buf & wS2.Cells(k, 2) & ","
End If
Next k
If Len(buf) > 1 Then
wS1.Cells(i, 2) = Left(buf, Len(buf) - 1)
buf = ""
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで
※ >山本専務理事 が 3,2
ではなく
2.3 という表示になってしまいますが・・・
こんな感じではどうでしょうか?m(_ _)m
お礼が大変遅くなり申し訳ありません。
VBAの知識がなく、まったくわかりませんでした・・
そのうち仕事でこの作業が不要になってしまいました。
でもすぐにこんな複雑で丁寧な回答をいただき、感謝しています。
どうもありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Visual Basic(VBA) VBA初心者です 検索した数字の行に色をつける 5 2023/02/13 14:22
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Access(アクセス) AccessVBAで降順にするテーブル作成クエリを使用して作成したテーブルを削除し同一のテーブル作成 1 2023/01/06 11:17
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Excel(エクセル) エクセルにおいてセル内の文字を出来るだけ大きく表示する方法を教えてください。 4 2022/07/30 08:08
- Excel(エクセル) エクセルでこのようなことはできますか? 3 2022/07/10 19:57
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Excel(エクセル) LEFT関数で文字数を指定しないで取りだす方法 7 2023/06/30 09:49
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IMEパッドの手書き入力
-
A4縦の2枚のワードをA3にするや...
-
ワード、上下中央揃えにしたい...
-
ワードが白と黒が反転してしま...
-
ワードの差し込み文書で困って...
-
microsoftのことで困っています
-
右側に半券のついたチケットを...
-
2時間くらいの会議をICレコー...
-
最近MacBookに変えました。 マ...
-
アスタリスクは真ん中じゃない...
-
例えば斎藤->齋藤、渡辺->渡邉...
-
Office2012の起動が遅い
-
ワードでPC画面上には色を付け...
-
"樋"の旧字体の一文字フォント...
-
KADOKAWA電撃大賞の互換ソフト...
-
ワードやエクセルのファイルの...
-
txtってパソコンでも横書きに表...
-
パソコンで見た時とスマホで見...
-
MicrosoftのOfficeに代わるソフ...
-
webで賞で送る場合、パソコンの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ワードの行✖️文字数について。 ...
-
A4縦の2枚のワードをA3にするや...
-
A4サイズ横位置というのは ワー...
-
アップルに電話したけどやはり...
-
ワード、上下中央揃えにしたい...
-
ワードってスマホから見ると、...
-
スマホで横書きでもパソコンで...
-
txtのファイルから編集してもい...
-
スマホアプリのワードが横書き...
-
ワードのこと教えてくれるとこ...
-
スマホのワードが開かないのっ...
-
https://dengekitaisho.jp/nove...
-
表示かダウンロードのどちらか...
-
最近MacBookに変えました。 マ...
-
これってワードですか? 先方が...
-
KADOKAWA電撃文庫のSafariで見...
-
txtって、ページ番号振れないん...
-
スマホの文書って見え方がどう...
-
普通の場合は400字詰め換算でも...
-
これらアプリって購入済みにな...
おすすめ情報