表のばらばらの文字列をまとめるマクロ
- 表のばらばらな文字列をまとめるためのマクロや関数を提供していただきました。
- このマクロや関数は、左の表のデータに該当する文字列がSMやNPと表示されるように、右の表にまとめることができます。
- また、1行に複数の文字列が入っている場合にも対応しており、空欄を削除して左に詰めることが可能です。
- ベストアンサー
表のばらばらの文字列をまとめるマクロ 関数
以前、同じ内容の質問を聞いたのですが、少し条件を変えて質問です。 (1)左の表には数式が入っていて、この他にもう一つある表のデータに該当すると1列ごとにSMとかNPとか表示されるようになっています。 (2)この左の表は実際には136列あるので、結果を印刷すると一枚に収まりきらないのです。なので、右の表のように、最短の列に表示したいのです。 (3)この例ではたまたま1行に1個ずつしか文字列が入ってないので右の表は1列ですが、1行に付き2個以上文字列が入ることもあります。5個以上はほとんどないです。 (4)関数でできませんか? またはマクロでもいいです。 以前、回答でいただいた関数は =IFERROR(HLOOKUP("*?",$A1:$D1,1,FALSE),"") 又は =IF(COUNTIF($A1:$D1,"*?"),HLOOKUP("*?",$A1:$D1,1,FALSE),"") でしたが、これだと1行につき1個文字列があるときのみ可能です。 1行に付き2個以上文字列がある場合に、例えば空欄を削除して左に詰めるような感じでマクロでできないでしょうか?
- honeybeans
- お礼率43% (422/963)
- Excel(エクセル)
- 回答数6
- ありがとう数1
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
>この例ではたまたま1行に1個ずつしか文字列が入ってないので右の表は1列ですが、1行に付き2個以上文字列が入ることもあります。 2個以上のときは1つのセルに文字列を連結して代入したいのですか? >5個以上はほとんどないです。 「ほとんどない」と「絶対無い」では処理方法が異なります。 最大の個数を提示された方が良いでしょう。 関数でも処理可能ですがとても長い数式になります。 複数の連続したセルへ個々に代入するのであれば比較的簡単です。 元データがA列からZ列までに限定した検証ですが画像を添付します。 各行にはランダムに2文字の文字列を最大5個を設定しました。 AD1=IF(COUNTA($A1:$Z1)>=COLUMNS($AD1:AD1),INDEX($A1:$Z1,SMALL(INDEX(($A1:$Z1<>"")*COLUMN($A1:$Z1)+($A1:$Z1="")*COLUMN($AB1),1,0),COLUMNS($AD1:AD1))),"") AD1セルを右にAH1セルまでオートフィルコピーしました。 AB1=CONCATENATE(AD1,AE1,AF1,AG1,AH1) AB1:AH1を選択した状態で下へオートフィルコピーをすれば添付画像のようになります。 元データの文字列の前後に空白があるときはAD1の数式で抽出した文字列をTRIM関数で空白を除去すれば良いでしょう。 Excel 2013で検証しましたがExcel 2007でも再現できるはずです。 AD列からAH列を使いたくないときはCONCATENATE関数の第1引数から第5引数を夫々のセルの数式を直接AB列へ入力することになり非常に長い数式になります。
その他の回答 (5)
- ushi2015
- ベストアンサー率51% (241/468)
こんにちは Alt+F11キーでVBE画面出して、メニューの挿入で標準モジュール追加して、 先のコードをコピペして、例えばセルEG1に =Conc(A1:EF1) と入れればA1:EF1の範囲のデータを纏めて表示します。 ご自分なりにやって出来なかったとは、どのように試したのでしょうか?
- kagakusuki
- ベストアンサー率51% (2610/5101)
>以前、回答でいただいた関数 を回答した者です。 その >以前、同じ内容の質問 に対して私は、その関数以外にも >表のばらばらの文字列をまとめるマクロ によるユーザー定期関数も回答した筈ですが、その方法では何か不都合があったのでしょうか? 下記のURLのページの回答にあるユーザー定義関数を使って =JOIN2D($A1:$D1) とセルに入力すれば、 SN という結果が得られますし、もしA1セルに「SN」、C1セルに「C1」、D1セルに「D1」と入力されていた場合には、 SNC1D1 という結果が得られます。 更には、 =JOIN2D($A1:$D1,",") とセルに入力すれば、 SN,C1,D1 という具合に指定した文字列(この場合は「,」)で区切った形で結果を得る事も出来ます。 【参考URL】 表のばらばらの文字列を1列にまとめたい 関数 【OKWAVE】 > 回答No.3 http://okwave.jp/qa/q9256531/a25800564.html
- ushi2015
- ベストアンサー率51% (241/468)
こんにちは ユーザー定義関数で、 Function Conc(t As Variant) As Variant Dim r As Variant Dim v As Variant For Each r In t v = v & r Next Conc = v End Function とかを作って、セルに=CONC(A1:EF1)のようにセットしてはどうですか?
補足
その辺あまり詳しくないのですが、自分なりにやって出来ませんでした。
- Chiquilin
- ベストアンサー率30% (94/306)
Excelのバージョンがいくつなのか分かりませんが Office365サブスクライバーなら TEXTJOIN関数でおしまいです。 > またはマクロでもいいです。 だったらユーザー定義関数を自分で作ればいいでしょう。For~Nextで文字列を繋 ぐだけです。 > 関数でできませんか? 左表を値に戻して PHONETICで範囲指定するか 作業シートを用意して 1セルずつ 繋いだものを参照するか。
補足
すみません、知識がなくて分かりません。 エクセルは2007です。
- ushi2015
- ベストアンサー率51% (241/468)
こんにちは 普通に =A1&B1&C1&D1 とか =CONCATENATE(A1,B1,C1,D1) ではダメなのですか?
補足
136列あり、スペースかカンマを入れるとさらに長くなります。短いのありませんか?
関連するQ&A
- 表のばらばらの文字列を1列にまとめたい 関数
添付の左の表を右の表にしたいです。 (1)左の表には数式が入っていて、この他にもう一つある表のデータに該当すると1列ごとにSMとかNPとか表示されるようになっています。 (2)この左の表は実際には136列あるので、結果を印刷すると一枚に収まりきらないのです。なので、右の表のように、1列に表示したいのです。 (3)1行のうち、2個以上文字列が入ることもありますが、その可能性は低いので、とりあえず1行に1個ずつ出現すると考えてもらっていいです。(2個以上文字列が出現する場合も分かれば教えてください。) (4)関数でできませんか? またはマクロでもいいです。
- ベストアンサー
- Excel(エクセル)
- <文字列マクロについて>
<文字列マクロについて> 皆さま お力を貸して下さい。 初心者で、何もわからず書いておりますがすみません。 下記の2つのマクロを教えて下さい。 ?横に並んだ「ある行」に在るデータを「列」に変換し、 指定した数だけ並べる あいうえお ←A1セルからE1まで5列(これは状況により変わる) ↓(マクロ実行:3行同じ数だけ並べるという指定) あ →A1セルから下にひたすら、同じ文字が続き順に並ぶ あ あ い い い う う →A1セルから下に並べる(3行は指定できれば別の数字にも変えたい う え え え お お お ?規則的に並んだデータのまとまりをセットで移動する。 例だと5つの塊(これは変数とおきたい) あ A a い B b う C c え D d お E e ↓(マクロ実行) あ い う え お A B C D E ・ ・ ・ ・ a b c d e
- 締切済み
- その他MS Office製品
- エクセルでHLOOKUP関数の選択範囲について
エクセルでHLOOKUP関数を使って、検索したいのですが、 シートは、一覧表のシートと データが入っているA101、B203、C305、...シートは300シートくらいあります。 一覧表のシートには、下のような表になっていて、 A列 B列 C列 D列 ... 1003、1004、1005、1006、... 2行目 A101 3行目 B203 4行目 C305 . . . データのはいっているシート、A101は下の表になっています。 B列 C列 D列、・・・、Z列 2行目 1004、1005、1006、... 3行目 100、 200、 150、... 一覧表のB列の2行目には HLOOKUP(B2、シートA2のB2:Z3、2行目、FALSE) という感じで、シート名をセルA2のものを参照にして 探して表示させ、B列、C列、D列の2行目から下の行も 表示させたいのですが、うめくできませんでした。 INDIRECT関数を使ってみましたが、セル範囲が無効という エラーがでてしまいます。↓こんな感じで入力してみたのですが... SUMPRODUCT((INDIRECT($A2&"!$B$2:$Z$3"))=$B$1,(INDIRECT($A2&"!$B$2:$Z$3"))) 1つづつデータを見て手打ちはデータが多く、 どんどんデータが増えていくので できれば関数を使って表示させたいと思っています。 詳しい方いらっしゃいましたら、どうか教えてください よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 複数条件に合う文字列をカウントする関数
現在、Excel2010を使って、タッチパネルを想定した処理表を作っているのですが、 関数に関してわからないことがあります。 ランダムに配列された項目(画像右、2行目のア~エ)と、その下にある文字列(A+~B)の右表から、 項目ごとに文字列をカウントして左の表に移したいです。 項目(ア~エ)の数やその順番はランダムに増減したり入れ替わったりするので、 関数によって項目名と文字列を一致するものを探し、それをカウントしたいのですが、 どのような関数を使えばこのような複数条件に合う文字列をカウントできるのかわかりません。 もしご存知の方がいれば、ご教授頂けないでしょうか?よろしくお願いします。m(_ _)m
- ベストアンサー
- Excel(エクセル)
- マクロを使って文字列を分類したい。
マクロを使って、次の処理を行うマクロを作りたいのですが、 まず、漢字・ひらがな・カタカナ・数時・アルファベットを識別するコードの抽出方法と、コードの一覧がわかりません。 それから、文字列の処理の関数がなかなか思いつきません。 2年ほどマクロを使っていません。エクセルも2007になり、かなり勝手が違います。 できれば、マクロを示していただければありがたいのですが、 重要なヒントを示していただいても構いません。 ----------------------------------------------- セル(H、5)の中に、いろいろな文字列があります。 例えば、 「東京1月アメリカEUやまとChinaイギリス日本2009年おわり」 ですが、 これを 漢字をA列に1行から順に、 東京 月 日本 年 ひらがなをB列に1行から順に、 やまと おわり カタカナをC列に1行から順に、 アメリカ イギリス 数字をD列に1行から順に、 1 2009 アルファベットをE列に1行から順に、 EU China というように出力するマクロを作りたいのです。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- セルの結合がされた表をHLOOKUPで検索する場合について
Excel初心者です。質問があります。 下記のような表があります A列 B列 C列 D列 行番号1 12/1 1/1 行番号2 件数 金額 件数 金額 行番号3 2 100 3 200 行番号5 1/1 ここでHlookupを使って下記のように検索します A5に1/1と入力して 件数を検索するには =HLOOKUP(A5,A1:D3,3) → 3 ここまではいいのですが 金額の「200」を検索するにはどのような関数を使えばよいのでしょうか? ※行番号1の「12/1」と「1/1」はセルの結合をしており 「12/1」のセルはA1、「1/1」のセルはC1になっています。 HLOOKUP関数とVLOOKUP関数又はCOLUMN関数を組み合わせれば出来るのでしょうか よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 表の中でもっとも多く使用されている文字(数字)を抽出するには
|A|B|C|D| -------------- 1|あ|い|う|え| -------------- 2|お|あ|あ|か| -------------- 上記は1行目に左から「あ」「い」「う」「え」が入力されていることを表しています。 この表の中から、一番多く入力されている文字だけを別のセルに抽出させるにはどのようにしたら良いでしょうか。 上記の例を使用すると、A列の5行目(任意のセル)に「あ」と抽出するようにしたいです。 いろいろと調べてみましたが、このように表の中で最も多く入力されている、「文字」または「数字」だけを抽出するという処理方法がみつかりませんでした。 マクロなら可能でしょうか? このような処理ができる関数もなさそうなので、無理なのかな。 アドバイスを御願い致します。
- 締切済み
- オフィス系ソフト
- Excel 2007 マクロ 表の貼り付け
Excel 2007 マクロ 表の貼り付け Excel 2007 マクロ 表の貼り付けについて教えてください。 <Sheet1>の表にあるA2からD4を <Sheet2>に値貼り付けをします。 表は画像を添付します。 マクロの記録では下記内容になります。 Sub Macro1() ' ' Macro1 Macro ' ' Range("A2:D4").Select Selection.Copy Sheets("Sheet2").Select Range("A2:D4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub <Sheet1>のA列の行の増減にかかわらず、<Sheet2>に貼り付けることができるように するにはどのようにマクロを修正すればよろしいでしょうか。
- ベストアンサー
- その他MS Office製品
- エクセルで列抽出できる関数かマクロを探しています。
エクセルで列抽出できる関数かマクロを探しています。 マクロ初心者です。 表の形は、 A1 B1 C1 D1 E1 容量 0.1 5.5 11 22 形式 NF30 NF63 NF125 NF250 カバー TCS … と、電気部品のモータ容量と定格電流で抽出したいのです。 行で抽出するととても見づらく、列で一気に見れるように (制御器選定スケールの様に)したいのです。 スケールだけで形式は選定できるのですが、カバーも選定するために 毎回カタログで確認するのは仕事の効率が良くなく 表を作成しようと思いたったのですが・・・ いろいろ探してみたのですが、今のところ見つからず 今試しているのが、表を一度コピーして列行を入れ替えて 別シートにコピーしたものを作成してからオートフィルタをかけて 抽出したデータを別シートにもう一度列行を入れ替えて貼り付けする 方法しか考え出せていません。 質問なんですが、 上記のように列抽出できる関数やマクロがあるのか? それとも一度行抽出に変えて最後に列に戻すやり方の方が良いのか? 又、そのやり方を初心者なりに作成してみたのですが 1回目は出来ても、容量が変わるとエラーが出てきて出来ません。 Sub Macro1() Range("A31").Select ActiveCell.FormulaR1C1 = "=Sheet1!R5C2" Range("J31").Select ActiveCell.FormulaR1C1 = "=Sheet1!R6C2" Range("J32").Select ActiveWindow.SmallScroll Down:=-9 Range("A1:X24").Select Range("A1:X24").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A30:X31"), Unique:=False ActiveWindow.SmallScroll Down:=-12 Selection.Copy Sheets("Sheet1").Select Range("F1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Sheet3").Select Application.CutCopyMode = False ActiveSheet.ShowAllData End Sub たぶん、行列の入れ替えが上手くいかないのかなとは素人ながらに考えて 色んなサイトで調べてみていじってみるのですが、なかなか上手くいきません。 皆様、宜しくお願いいたします。
- ベストアンサー
- Windows系OS
- Rを.にして文字列を数字として取り出す
関数を教えてください A列に品番が入っています 例)ABC075C75 ABC035C6R5 AAA285D7 文字列は左から7文字が品番をあらわして残りが長さをあらわしています。例)ABC075C75は75 ABC035C6R5は6.5 AAA285D7は7です。 Rが小数点を意味しています。 このような場合にB列のみを使って長さの情報を取り出す関数の組み合わせを教えてください。列を複数行使えば、私にも長さを数字として取り出せますが、表が大変大きいのでなるべく列を増やしたくないので、 1列に関数を収めてしまいたいと思っています。よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
お礼
できました。ありがとうございました!