教えてください。
L4~L19セルにP1230~連番で表示されています。
(P1239の次からはP123A~P123Fとなっています)
("P"の前に文字があったりもします)
N4~N19セルに
"ABC"とか"DE"とか"FG123"とか"HJKL/"とか
色んな文字を入力します。
例えば、
N4セルに"ABC"と入力すれば、
M4セルに"ABC1230"と表示
N5セルに"DE"と入力すれば
M5セルに"DE1230"と表示
N6セルに"FG123"と入力すれば、
M6セルに"FG123"と表示
N7セルに"HJKL/"と入力すれば、
M7セルに"HJKL"と表示
という風に、M列を計算式で表示させたいです。
10000行くらいあり、手で変換するのは大変なので
どうかご教示ください。
上記では分りづらいと思いますので画像を添付します。
microsoft365のエクセルを使用しています。
よろしくお願いいたします。
No.5ベストアンサー
- 回答日時:
こんにちは
条件の説明が、重複する個別例だけで系統だっておらず、全てのケースが網羅されていないので不明部分がありますけれど、説明にない部分は勝手に解釈しました。
少なくとも、以下で、ご提示の例と一致した結果にはなります。
(ただし、M5セルは除く)
ご提示のレイアウトで、M4セルに
=IF(IFERROR(FIND("/",N4),0),SUBSTITUTE(N4,"/",""),IF(IFERROR(AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,N4),1),0),N4,N4&IFERROR(MID(L4,AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,L4),1),LEN(L4)),"")))
を入力し、下方にフィルコピーではいかがでしょうか。
No.11
- 回答日時:
"" ""
O列=Lの数値前記号とする
O4=LEFT(L4,AGGREGATE(15,6,FIND(ROW($1:$10)-1,L4),1)-1)
P列=Lの記号後数値とする
P4=RIGHT(L4,LEN(L4)-LEN(O4))
Q列=Nの”/”有無とする
Q4=IF(ISERROR(FIND("/",N4)),"","/")
R列=Nの”/”以外とする
R4=SUBSTITUTE(N4,"/","")
S列=Nの数値前記号とする
S4=IF(ISERROR(LEFT(N4,AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)-1)),N4,LEFT(N4,AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)-1))
T列=Nの記号後数値とする
T4=RIGHT(N4,LEN(N4)-LEN(S4))
M列=①Nに"/"有,②Nに数値無,③その他の順に表示する。
M4=IF(Q4="/",R4,IF(T4="",S4&P4,N4))
M4の数式中、O,P,Q,R,S,Tを、各列の数式に置き換えれば、O,P,Q,R,S,T
列は不要です。
No.10
- 回答日時:
No.6~9です。
連投失礼します。No.9で回答した数式に不備があり、「L列が『P0001』という様に0がつくとき、M列では0が省略されてしまう」という問題が発生することが判明しました。
つきましては、No.9回答の数式は無かったものとし、不備数式をお詫び申し上げるとともに、M4に記述する数式を以下の数式に訂正いたします。誠に申し訳ありませんでした。
=IF(IFERROR(FIND("/",N4),0)+IF(ISERR(AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)),0,1),SUBSTITUTE(N4,"/",""),N4&RIGHT(L4,AGGREGATE(14,6,(1-ISERR(HEX2DEC(RIGHT(L4,ROW($1:$4)))))*ROW($1:$4),1)))
添付画像も修正いたしました。
No.9
- 回答日時:
ご質問者の当初の説明では
>L4~L19セルにP1230~連番で表示されています。
という話と、添付画像の説明とを併せて、L列の末尾4文字は「16進数」(0~Fまで)と理解していたのですが、
お礼のコメントで
>L列の文字数は不規則に変わります。2文字~7文字で色々ある事を伝えられていませんでした。
との補足がありました。
本当は、5~7文字の例は既にお示しいただいているので2~4文字の例をお示しいただけないと回答のしようがないのですが、
>L列の数字より前のアルファベットが消えませんでした。
>添付画像だと"P"、"CP"、"AP"、"FP"、"DP"、"HP"等)
ともおっしゃっているので、
勝手に、L列は
(1)数字は4桁に限らず3桁以下のこともある
(2)さらには、2文字のときは末尾の4桁数字がないときもある
ということではないかと推測して、数式を修正してみました。
M4セルに以下の数式を記述して、下方向へコピーでいかがでしょうか?
=IF(IFERROR(FIND("/",N4),0)+IF(ISERR(AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)),0,1),SUBSTITUTE(N4,"/",""),N4&RIGHT(L4,IFERROR(MATCH(AGGREGATE(14,6,HEX2DEC(RIGHT(L4,ROW($1:$4))),1),INDEX(HEX2DEC(RIGHT(L4,ROW($1:$4))),0),0),0)))
添付画像の20~22行目のセルの内容をご確認ください。
No.8
- 回答日時:
No.7です。
連投失礼します。前回回答の添付画像のN6セルは「FG1232」となっていますが、ご質問者の例示では「FG123」です。
これはL6セルの内容が「CP1232」であるため、N列に同じ数字のデータが登場した場合、何らかの問題を引き起こさないかチェックするため、敢えて例示とは異なる「FG1232」を入力しています。
つまり、N6セルが「FG123」のときM6セルが「FG123」となることは既にチェック済みです。
例示データと異なる事情を補足説明させていただきました。
たくさん確認してくださってありがとうございます。
原因が分かりました。例のL列は5文字以上しか記載してなかったのですが、L列の文字数は不規則に変わります。2文字~7文字で色々ある事を伝えられていませんでした。こちらの不手際です。申し訳ありません。
No.7
- 回答日時:
No.6です。
ご質問者のお礼に>L列の数字より前のアルファベットが消えませんでした。
>(添付画像だと”P”、”CP”、”AP”、”FP”、”DP”、”HP”等)
>私もこれで填まってしまい、今回質問を投稿しました。
との記述がありますが、意味が判りません。
当方の環境では添付画像のとおり、正しく処理されてる気がするのですが・・・・。当方の勘違いがあるのでしょうか?
No.6
- 回答日時:
No.5さんのご指摘のとおり、ご質問者が例示されたM5セルの内容は「DE1230」ではなく「DE1231」が正しいように思います。
この前提でM4セルに記述すべき数式は、No.5さんの回答でよいと思います。
しかし、No.5さんの回答はご質問者が説明された条件をそのまま数式化されておられるので、若干長めの数式になっている気もします。
少し短い以下のような数式も使えると思います。M4セルに記述し、下方向へコピーするということになります。
=IF(IFERROR(FIND("/",N4),0)+IF(ISERR(AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)),0,1),SUBSTITUTE(N4,"/",""),N4&RIGHT(L4,4))
回答ありがとうございます。いい感じまで行けたのですが、L列の数字より前のアルファベットが消えませんでした。(添付画像だと”P”、”CP”、”AP”、”FP”、”DP”、”HP”等)私もこれで填まってしまい、今回質問を投稿しました。
No.4
- 回答日時:
いやえっと、N列に数字があるかどうかで結果が変わるんですよね?
だからN列について聞いているんですが…
>文字や数字や文字+数字
このような書き方ですと、数字は末尾(後半)のように受け取れますが違うならどのようなパターンなのか具体例を出してください。サンプルにはなかったので。後、数字のみのパターンもないようですが…?
考えられるパターンは全て書き出した方がいいですよ。
No.2
- 回答日時:
ごめんなさい。
質問の例だけでは規則性を見いだせないのです。
いくつも任意のルール(条件)あるなら、それらをすべて網羅する必要があります。
全部、質問の「補足」に書いてください。
(”P1230" の ”P” はどうなるの?などの説明もしてください)
それができないのでしたら、一つずつ手作業で処理することになります。
たかが10000行です。5人くらいで分担すれば、1時間もかからないでしょう。
ご指摘ありがとうございます。L列の数字より前のアルファベットは削除となります。(添付画像の内容だと”P”、”CP”、”AP”、”FP”、”DP”、”HP”等)N列はアルファベット+数字かアルファベットのみになります。数字のみのパターンは無いです。あとは添付画像以外のルールは特にありません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/08 09:05
- Excel(エクセル) 条件に合った数値の合計を表示させたい関数と条件指定の方法 3 2023/05/13 16:07
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) エクセルの表示形式について教えてください あるセルの「A」という値と、別のセルの「B」という値を組み 4 2023/02/21 21:55
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- Excel(エクセル) エクセルで日付が入っているセルを一定の法則に従って違うセルに表示したい 2 2022/04/04 17:16
- Excel(エクセル) エクセルで、特定のセルの内容を更新すると、別の特定セルに 更新日付が自動的に表示させる方法はあります 1 2022/11/14 21:03
- Excel(エクセル) 文字列を数式として変換する事はできますか? 6 2022/06/23 10:38
- Excel(エクセル) エクセルでセルに数式を入力した状態で、数式バーをクリックすると添付資料のように 「数式に関連するセル 2 2022/11/25 19:36
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/02 10:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルのデーターが2か月前の...
-
エクセル共有したが、アクセス...
-
エクセル②
-
エクセルの計算
-
【マクロ】顧客番号にて一致さ...
-
エクセルを使っていて2024/5/15...
-
(マクロ)データをAブックからB...
-
Microsoft 365の Excel を使用...
-
エクセルで日付を数字+アルフ...
-
Excel
-
Excelでセルの値が同じか...
-
エクセル 文字を増やしたい。
-
UNIQUE関数が使えないバージョ...
-
指定文字の間に
-
Googleスプレッドシートでファ...
-
エクセルで年休を管理する方法...
-
Microsoft365に変えたのですが...
-
エクセルの暗号化なしのバーの...
-
Excelで縦軸の書式を0:00形式の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報
ありがとうございます。
L列は必ず末尾に必ず数字があるというわけではありませんが、数字があるところもあります。というのは、P1239の次からはP123A,P123B,P123B,P123C,P123D,P123E,P123Fとなるからです。Pの前には色々な文字があったりなかったりします。
N列は全く規則性はなく、文字や数字や文字+数字が入ります。
よろしくお願いいたします。