[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『５ケタの数字を９ケタにする方法』（ねね）
いつもお世話になっております
２０４４４ を ２０００４４４１２ としたいです。
ルールは
Ａ列のコードの最初の２ケタ＋"00"+３ケタ+Ｃ列の数字
D=LEFT(A2,2)&"00"&MID(A2,3,3)&""&(C2)
とするとC列が６の時は８ケタになってしまいます
A B C D
1 コード 商品名 数量
2 20387 AAAAAA 12 200038712
3 30067 BBBBBB 6 30000676 →希望表示 300006706
4 20633 CCCCCC 3 20006333 →希望表示 200063303
９桁に揃えたいのですが可能でしょうか？
よろしくお願いします。
＜ 使用 アプリ：Excel 97-2003、使用 OS：Windows7 ＞
=LEFT(A2,2)&"00"&MID(A2,3,3)&TEXT(C2,"00") でどうでしょうか？ （se_9） 2016/04/14(木) 12:17
=REPLACE(A2,3,0,"00")*100+C2
とか。 （笑） 2016/04/14(木) 12:55
=TEXT(A2,"0!0!0000")&TEXT(C2,"00") でも。
なお、上記の式の結果は文字列なので結果を数値にしたい場合は =(TEXT(A2,"0!0!0000")&TEXT(C2,"00"))*1 としてくれ。 （ねむねむ） 2016/04/14(木) 13:07
笑さんの方式を組み合わせて =TEXT(A2,"0!0!0000!0!0")+C2 でもいいか。 （ねむねむ） 2016/04/14(木) 13:16
コードが00053とかゼロから始まるのがあり
（se_9）さんの方法だと９桁になります。
この９桁の数字を参照して
=IF(ISERROR(VLOOKUP(D3,在庫表!C:I,7,FALSE)),"",VLOOKUP(D3,在庫表!C:I,7,FALSE))
同じファイルの別のシート（エクセル）から数字を
引っ張ってくるのですが、ゼロで始まるのは「＃N/A」という回答になっています。
エクセルでは文字列、数値、標準等の表示形式で計算できなくなってします。
どのセルも標準に直してもかわりません。
どうしたら「＃N/A」にならないようになるでしょうか？
よろしくお願いします。
（ねね） 2016/04/15(金) 15:23
ねむねむさん案をお借りして。
=TEXT(A2,"0!0!0000!0!0")+C2&"" （GobGob） 2016/04/15(金) 15:37
> コードが00053とかゼロから始まるのがあり
A列のコードは文字列だってこと？
だったら9桁の数字は（9桁の数字も文字列にしたいのなら）
=REPLACE(A2,3,0,"00")&TEXT(C2,"00")
で、 > この９桁の数字を参照して > ゼロで始まるのは「＃N/A」という回答になっています。
ゼロで始まらないものは正しい値が返るということ？
その在庫表シートのC列は数式の結果？ だったらその数式を提示してください。
それとデータが数値か文字列かというのは、TYPE 関数で調べてみてください。 =TYPE(C2) のように。 ゼロで始まっているセルはどうなりますか？
あと、数字を返すということだけど VLOOKUP ではなく SUMIF でやったらどうなりますか？
一応、確認してください。 （笑） 2016/04/15(金) 16:03
ゼロで始まっているセルの文字数も調べてみてください。
=LEN(C2) のように。 「9」になりますか？ （笑） 2016/04/15(金) 16:24
返事遅くなりました。
A列のコードは ゼロで始まる５ケタは =TYPE(A2) で 「２」になります。
=LEN(C2) で 「５」になります。
ゼロ以外で始まるのは =TYPE(A2) で 「１」になります。
=LEN(C2) で 「５」になります。
C列の数量は =TYPE(A2) で 「１」と「２」になります。
=LEN(C2) で 「１」と「２」になります。
５桁を「=TEXT(A2,"0!0!0000!0!0")+C2&""」で９桁（D列）にして
=IF(ISERROR(VLOOKUP(D3,在庫表!C:I,7,FALSE)),"",VLOOKUP(D3,在庫表!C:I,7,FALSE))
で参照するとゼロで始まらないものは正しい値が返ってきていると思います。
違うのもあるような(-_-;)
数字に見えるセルが、文字列なのか数字なのか書式をみると「標準」となっていて
どうなっているのかわかりません。
ただ、セルの左上に緑の三角があるのとないのがあります。
在庫表のファイルは都度ＡＣＣＥＳＳで集計したデータをもってくるので
さわることができません。
（ねね） 2016/04/18(月) 15:35
なんか話がかみ合ってないような･･･
TYPE 関数と LEN 関数の結果を教えてほしいのは「在庫表シートのC列」なんですけど。 それとも最初の質問に例示してあるのが在庫表シートなんですか？ 違いますよね？ > ５桁を「=TEXT(A2,"0!0!0000!0!0")+C2&""」で９桁（D列）にして
↑ の式だと「0」で始まるものは「9桁」にならないのでは？ 下記の質問にすべてお答え下さい。
1）在庫表シートのC列、「0」で始まるもの、始まらないもの、 両方ともTYPE 関数と LEN 関数の結果はどうなりますか？
2）SUMIF関数で試したらどうなりますか？
=SUMIF(在庫表!C:C,D2,在庫表!I:I)
在庫表シートの I列が数値なら、ですが。 見た目は数字だけど、数値か文字列かわからないということなら、 一応これも TYPE 関数でどうなるか調べてみてください。 以下はブックをコピーした、テスト用ファイルで試してください。
3）在庫表シートのC列を選択して、データ → 区切り位置を実行。 ダイアログが表示されますが、何もせず「完了」をクリック C列の表示形式〜ユーザー定義を 000000000 にする
検索する方のD列を
D2 =REPLACE(A2,3,0,"00")*100+C2
または
D2 =TEXT(A2,"0!0!0000!0!0")+C2
表示形式〜ユーザー定義を 000000000 にする
このデータだったら VLOOKUP の式でどうなりますか？
とりあえず以上です。 （笑） 2016/04/18(月) 16:54
ありがとうございます。理解できてなくてすみません。
５桁を下記の式で９桁にしての間違えでした。
=LEFT(A2,2)&"00"&MID(A2,3,3)&TEXT(C2,"00")
1）在庫表シートのC列、「0」で始まるもの、始まらないもの、 両方ともTYPE 関数と LEN 関数の結果はどうなりますか？
TYPE 関数=「２」
LEN 関数 =「９」 になります。
2）SUMIF関数で試したらどうなりますか？
D列=LEFT(A2,2)&"00"&MID(A2,3,3)&TEXT(C2,"00")で９桁にした後、
=SUMIF(在庫表!C:C,D2,在庫表!I:I)
在庫数が正しくでてきます。
在庫表シートの I列はTYPE 関数ですべて「１」です
3）在庫表シートのC列を選択して、データ → 区切り位置を実行。 ダイアログが表示されますが、何もせず「完了」をクリック C列の表示形式〜ユーザー定義を 000000000 にする
↑がわかりません。在庫表は都度更新するので触りたくないです。
検索する方のD列を
D2 =REPLACE(A2,3,0,"00")*100+C2
または
D2 =TEXT(A2,"0!0!0000!0!0")+C2
表示形式〜ユーザー定義を 000000000 にする
このデータだったら VLOOKUP の式でどうなりますか？
=IF(ISERROR(VLOOKUP(D3,在庫更新!C:I,7,FALSE)),"",VLOOKUP(D3,在庫更新!C:I,7,FALSE))
上記の条件でVLOOKUPで試しましたが、ブランクになります。
すみません。３）の内容が理解できてないみたいです。
よろしくお願いします。
（ねね） 2016/04/19(火) 14:25
Dim i As Long
i = 2
Do While Cells(i, 1) <> ""
Cells(i, 4).NumberFormatLocal = "@"
Cells(i, 4) = func(Cells(i, 1), 5, 1, 2) & "00" & func(Cells(i, 1), 5, 3, 3) & func(Cells(i, 3), 2, 1, 2)
i = i + 1
Loop
End Sub
Function func(arg1, arg2, arg3, arg4) 'arg1をarg2桁に直したもののarg3文字目からarg4文字を返す
func = Mid((Application.Rept("0", arg2 - Len(arg1)) & arg1), arg3, arg4)
End Function
（mm） 2016/04/19(火) 18:04
> =SUMIF(在庫表!C:C,D2,在庫表!I:I) > 在庫数が正しくでてきます。
だったら VLOOKUP をやめて SUMIF でやればいいと思うんですけど、何か問題ありますか？
なお、検索値が在庫表シートのC列にない場合、SUMIFはエラーではなく「0」になります。 この「0」を表示したくないのなら、表示形式〜ユーザー定義を # にする。 桁区切りを入れたいのなら #,### です。
在庫表シートのC列には「0」が入ることもあるので、 何でもかんでも「0」を非表示にするのはマズいということなら
=IF(COUNTIF(在庫表!C:C,D2),SUMIF(在庫表!C:C,D2,在庫表!I:I),"") 以下、参考まで。
TYPE関数の戻り値が「1」ならそのデータは数値、「2」なら文字列です。
在庫表シートのC列は「2」だということですが、どのくらいの数のデータを調べたんですか？ C列のデータが全部「2」、つまりすべて文字列なら、VLOOKUPで値によってうまくいったりいかなかったりなんてことはないと思いますよ。 数値と文字列が混在してるんでしょう。
その場合、普通にVLOOKUPでやろうと思うのなら、C列を数値なら数値、文字列なら文字列に統一する必要があります。
> ３）の内容が理解できてないみたいです。
↑ は何がわからなかったんですかね？ 区切り位置ですか？
データメニューに「区切り位置」というのがあるので、それを実行する、ということ。 前回書いたように操作すれば、文字列の数字は「数値」に変換されます。
C列のデータをさわったり作業列を作ったりしなくても、 例えば「0」で始まっているデータは文字列、それ以外は数値とはっきり分かれているのなら、 VLOOKUP の式を少し変更すればできるでしょうし、 全くランダムに数値と文字列が混在しているのだとしても、VLOOKUP でできないことはないです。
けどそんなことをするぐらいなら SUMIF でやればいいでしょ、ということです。 （笑） 2016/04/20(水) 12:50
ありがとうございます。
いくつかの方法を教えて頂いたので、すべて試してみました。
今日、改めて試したら
SUMIF,VLOOKUP両方でできました。
VLOOKUP関数を使う時は、C列を数値なら数値、文字列なら文字列に統一
TYPE関数の戻り値が「1」ならそのデータは数値、「2」なら文字列
データメニューの「区切り位置」を実行する、文字列の数字は「数値」に変換
「検索する」というワードは、VLOOKUP関数以外にも方法があることも
わかりました。
いろいろ勉強になりました。
ありがとうございます。
（ねね） 2016/04/20(水) 14:54
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.