No.4ベストアンサー
- 回答日時:
意味を説明するのは数式を考えるより難しいです。
解らなかったら悪しからず。
COLUMN(A1:Z1)は1から26の配列を生成します。
従って文字列の単語が26個まで対応することになります。
実態に合わせて変更してください。
<例>30個ならCOLUMN(A1:AD1)
数式の概要は
TRIM(MID(SUBSTITUTE(SUBSTITUTE("♪"&SUBSTITUTE(A2,"//","♪"),"/",""),"♪",REPT(" ",100)),(COLUMN(A1:Z1))*100,100))で
りんご_いちご_…レモンという単語の文字間が100個の半角ブランクで繋がった文字列(_は半角ブランク100個を表す)を作成。
TRIM(MID(上記の文字列,1*100,100))→りんご
(100文字目から100文字を取得し、前後のブランクを削除)
TRIM(MID(上記の文字列,2*100,100))→いちご
(200文字目から100文字を取得し、前後のブランクを削除)
:
TRIM(MID(上記の文字列,6*100,100))→レモン
TRIM(MID(上記の文字列,7*100,100))→""
:
TRIM(MID(上記の文字列,26*100,100))→""
で次の26個の単語配列を生成。
{"りんご","いちご","ぶどう","ばなな","めろん","レモン","","",…}
MATCH関数で、この配列を検索値としてD列(検査範囲)を検査し、返った数値(配列)をLOOKUPの検査値としてC列(検査範囲)E列(対応範囲)
し、変換文字を配列取得。MATCHでエラーになる場合(置換表に文字が無い)は26個の単語配列から元の単語を配列取得。
{"Apple","Strawberry","Grape","Banana","Melon","Lemon","","",…}
TEXTJOINで配列の文字を編集
おはようございます。
とてもわかりやすくご説明いただき感謝いたします。
数式の内容を理解いたしました。
今回こそ無事に解決いたしました。
何度もお手数をおかけして申し訳ございませんでした。
ありがとうございました!
No.3
- 回答日時:
№2です。
置換表にヒットしない文字は元の文字を残す配列数式になります。
置換表は図のように最左に№(連番)を追加してください。
ローテクな配列数式なので大変長いです。
A4=TEXTJOIN("/",,IFERROR(LOOKUP(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE("♪"&SUBSTITUTE(A2,"//","♪"),"/",""),"♪",REPT(" ",100)),(COLUMN(A1:Z1))*100,100)),D2:D8,0),C2:C8,E2:E8),TRIM(MID(SUBSTITUTE(SUBSTITUTE("♪"&SUBSTITUTE(A2,"//","♪"),"/",""),"♪",REPT(" ",100)),(COLUMN(A1:Z1))*100,100))))
№3 様
こんにちわ。
ご回答ありがとうございます。
ご提示いただいた数式で無事解決いたしました。
ご丁寧に教えてくださり、ありがとうございました!
No.2
- 回答日時:
ローテクな配列数式でEXCEL2019以降で対応します。
置換表はB列で昇順にします。
※置換表にヒットしない文字は文字列から除外されます。
A4=TEXTJOIN("/",,IF(ISERROR(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),
COLUMN(A1:Z1)),$B$2:$B$8,0)),"",IFERROR(LOOKUP(MID(A2,
ROW(INDIRECT("1:"&LEN(A2))),COLUMN(A1:Z1)),$B$2:$B$8,$C$2:$C$8),"")))
Ctr+Shift+Enterで数式確定入力
No.1
- 回答日時:
こんばんは
各文字列を探し置き換え、つなげる・・TEXTSPLIT・MATCH・INDEXを使っても
1セルでやるのは・・どうだろう?無理っぽいけど
文字の探し置き換えが無くただ決まった文字を置き換えるだけなら
例えば、
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"//","|"),"/",""),"|","/")
とか・・関数は良く解らないので
ユーザー定義関数でやっちゃうのはどうでしょう
かのん2222様は使い方をご存知かと思いますので登録詳細は割愛
Function VlpRep(rVip As Range, rTrg As Range, ix As Integer, n As Integer, _
Wt As String, Rp As String, k As Integer) As String
'VlpRep(vLookup範囲,計算対象セル,vLookup抽出列index,検索方法,置換文字,置換後文字,右削除文字数)
Dim arr As Variant
Dim strTrg As String
Dim i As Long, buf As String
strTrg = Left(rTrg.Text, Len(rTrg.Text) - k)
arr = Split(strTrg, Wt)
'On Error Resume Next 'vLookUP見つからない場合は飛ばす
For i = 0 To UBound(arr)
If Not buf <> "" Then
buf = Application.VLookup(arr(i), rVip, ix, n)
Else
buf = buf & "/" & Application.VLookup(arr(i), rVip, ix, n)
End If
Next
VlpRep = buf
End Function
数式例(画像の配置)
=VlpRep(C7:D10,A2,2,0,"//","/",1)
関数リストなどに登録するコードは割愛
№1 様
こんにちわ。
ご回答いただきありがとうございます。
ユーザー定義関数については使用したことがないため、調べてみたいと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの数式について教えてください。 7 2023/06/18 10:16
- Visual Basic(VBA) 特定の文字を簡単な操作で半角スペースに変換するか削除したい 2 2022/11/01 10:35
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/06/23 15:02
- Visual Basic(VBA) VBAで、特定の文字より後を削除して残った数値を文字列に変換と特定の文字より前も削除したい 3 2022/04/15 19:21
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/02/10 11:41
- Excel(エクセル) エクセルの条件付き書式で*を使いたい 4 2022/05/13 16:49
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/06/15 14:11
- Excel(エクセル) セルに入力した小文字アルファベット、数字を大文字表示させるには? 3 2022/07/13 10:01
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/02 10:20
- MySQL SQLについて質問です。 SELECT TO_CHAR(TO_DATE('90-10-08.'YY- 4 2022/05/13 20:55
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル 価格表から単価を呼び...
-
シートAで横に並んだ項目→シー...
-
行数が不規則な一週間ごとの合...
-
ある列、或いは、ある行のセル...
-
エクセルの順位別一覧表の自動...
-
Excel2013のF6キー操作について
-
エクセルで複数のシートにまた...
-
excelの不要な行の削除ができな...
-
Excelが固まってしまった。
-
エクセルで特定の範囲内から小...
-
スマートな関数を教えて下さい。
-
Excel元に戻す方法を教えてくだ...
-
Excelはなんで先頭の0を消すん...
-
【Microsoft Office Excel Comp...
-
4つのパターンを表示するEXACT...
-
[関数について]わかる方教えて...
-
Excel 2019 のピボットテーブル...
-
Excel初心者です。 詳しい方、...
-
関数を教えて下さい。
-
エクセルでチェックボックスを...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
文字変換において、m3(立方)の...
-
DocuWorks 文字認識 検索でき...
-
【Excel関数】特定の文字を別表...
-
ハガキ宛名の住所の数字を漢数...
-
和暦を西暦に変換する方法
-
EXCEL VBA 記号の削除
-
エクセルで住所の数字を漢数字...
-
9文3分、10文という足袋のサイ...
-
VBAで小数を16進数に変換
-
エクセルでインチの分数表示
-
税金を盗る
-
PowerPointで比例記号を入力す...
-
RGB出力を直接HDDレコーダに録...
-
wordで→「┗」はどうやって変換...
-
mp4→sb1
-
USB->シリアル変換コネクター自作
-
文字式の表し方にしたがって表...
-
このタイプの納品書を・・・
-
昔の文字入力の仕方
-
Excel 内の英小文字を大文字にu...
おすすめ情報
お礼で無事解決したとお伝えしたのですが、追加でご教授願います。
(COLUMN(A1:Z1))
→この部分なのですが、どのような意味か教えていただいてもよろしいでしょうか?