ちちくろ目線 
こんにちは、ちちくろです♪
ブログにコメントを頂いたことがきっかけで・・約、2年ぶりくらいでExcelのワークシートを眺めながらあれこれ考えることが出来たのですが・・。
ふと思い出したことが・・それは・・現役時代に項番購入を使用していて・・少し不便に感じていたことがあったのですが。
こんな風に改良しようと思っていたのですが・・日常の業務に追われ・・やらずじまいになっていました・・。
今回・・久しぶりにExcelのワーク使途と向き合い・・やってみると・・出来たので今回投稿してみたいと思います。
どのような部分に不都合があり・・どのように改良してみようと思ったのかご説明したいと思います♪
目次
番号付与
図のように項目の列に品名が記載されているワークシートがあると仮定します。
A列の項番に・・番号を挿入するのは・・COUNTA関数で項番の列に番号を付与することも・・複数列を使いIF関数とISBLANK関数でも可能です。
これが前回までのお話し・・。
カテゴリ分類したい
この図をよく見てみると・・項目の品名のところどころに数行空白になっています・・これは・・品物のカテゴリが違うから・・。
品名をカテゴリ別で分けてみたい時・・。
B3とB4は・・リンゴとバナナなので・・B2に果物と・・カテゴリ表示とわかるように【果物】と記載する
同様に・・B7・B8は白菜・大根なので・・B6には【野菜】・・更に・・B10には【鮮魚】 B14には【精肉】等と・・。
前回までにご紹介した豊富だと・・。
カテゴリもカウントされる
カテゴリを記載すると・・カテゴリを記載したセルもカウントされてしまう・・ということに・・。
COUNTA関数を使った場合
このように・・複数の列を使った場合も同様で・・。
やっぱりこうなります・・。
LEFT関数
そこで・・いろいろ考えた結果・・LEFT関数を使ってみることに・・。
LEFT関数とは 指定された文字列から指定された文字数を取り出すことが出来ます♪
LEFT関数を使う方法を具体的に説明すると・・セルに”=LEFT(指定された文字列［参照セル］,文字数［数値］”と書き込みます。
そこで・・LEFT関数を使って品名の１文字目が【なら処理を変えてみようかと思い・・IF関数と組み合わせることに・・。
COUNTA関数を使った場合
COUNTA関数を使った表のA2のセルにLEFT関数を付けたすと・・具体的に書き込んだ内容は・・”=IF(OR(ISBLANK(B2),LEFT(B2,1)=”【”),””,COUNTA($B$2:B2))”となります。
すると・・A2やA6といったB列にカテゴリが記載された行は・・空白になるのですが・・品名の番号がおかしなことに・・。
リンボが［2］になり・・白菜が［5］に・・項目に連続した番号割り当てられなくなってしまいました・・。
これはCOUNTA関数が、カテゴリが書かれたセルを空白でないと判断し・・カウントしてしまうことが原因です・・。
残念ながら・・この現象を回避する方法が、私には思いつきません・・。
これだと使うことはできませんよね・・。
複数の列を使う方法
今度は・・複数の列を使う方法で試してみます。
結論から言うと・・複数の列を使う豊富だと回避することが出来ました・・。
回避する方法は・・同じくLEFT関数を使用することです・・具体的に説明すると・・。
まずは・・A2のセルにLEFT関数を追加します・・実際に書き込んだ数式は・・”=IF(OR(ISBLANK(C2),LEFT(C2,1)=”【”),””,B2)”というもの・・。
すると・・セルC2の１文字目が【なので・・セルA2は空白に・・しかしA3以降に付与された番号は ・・COUNTA関数を使った場合と同じように・・。
そこで・・B2の数式も変更・・。
B2のセルにもLEFT関数を追加・・B2のセルの数式は・・”=IF(OR(ISBLANK(C2),LEFT(C2,1)=”【”),B1,B1+1)”というもの・・。
子の数式の意味は・・セルC2の１文字目が【ならば・・B1のセルの値を記入・・そうすることでセルC2の１文字目が【ならばカウントしなくなります。
これでA列の番号付与が正常になりました♪
さらなる工夫
しかし・・これだと何か違和感が・・。
違和感の正体は・・A列の番号が途切れているから・・。
そこで、さらに工夫することを考えてみました・・。
カテゴリごとに番号を付与
カテゴリが変わったら・・番号を新しく付与すれば・・。
そこで・・セルB2の数式を変更することに・・セルB2の数式を”=IF(OR(ISBLANK(C2),LEFT(C2,1)=”【”),0,B1+1)”に変更・・。
この数式の意味は・・セルC2の１文字目が【ならB2のセルの値を0にする・・こうすることで品名のカウントを０にすることが・・すると品名の番号をカテゴリごとに１・2・3と番号を付与することが出来ます。
しかし・・このままだとなんか見辛い感じが・・同一の表に同じ番号が存在するために紛らわしくなってしまいます。
CONCAT関数で見やすく
表をより見やすくするために ・・カテゴリにも番号を付与することに・・その方法は・・。
ワークシートに１列追加することにし・・新たにB列を追加します。
そんなことして・・大丈夫・・そう思うかもしれませんが・・。
Excelの関数は優秀で・・列を新たに追加しても・・数式の内容は、今迄のB列は・・C列に・・C列は・・D列にと変更してくれます。
これは・・セル参照が相対参照なので、列を追加するのだけで、新たに数式を書き換える必要はないということ・・。
そして・・セルB2に新たな数式を・・数式の内容は”=IF(LEFT(D2,1)=”【”,B1+1,B1)”というもの・・。
この数式の意味は・・セルD2の１文字目が【ならば・・B1の値に１をプラス・・逆に【でなければB1の値を表示するというもの・・。
更にセルA2の数式も変更・・ここで登場するのがCONCAT関数になります。
CONCAT関数とは・・指定された文字列を連結するというもの・・。
CONCAT関数の具体的な書き方は・・”=CONCAT(文字１,文字２・・・)というもの・・。
文字列１・文字列2と、どんどん文字を付け足してくれます・・いくつまで可能か・・わかりませんが・・。
セルA2の書き込む新たな数式は・・”=IF(ISBLANK(D2),””,IF(LEFT(D2,1)=”【”,B2,CONCAT(B2,”-“,C2)))”というもの・・。
この数式の意味は・・セルD2の１文字目が【ならばB2の数値を表示し・・セルD2の１文字目が【以外ならば・・セルB2の値と”-“とセルC2の値を連結して表示ということ・・。
A列の項番に表示される番号は・・カテゴリが記載されればカテゴリの番号が・・品名が記載されればカテゴリの番号と品名の通し番号を１”-“を挟んで表示すように・・。
これだととても分かりやすいと思いませんか？
後は・・B列とC列を非表示にするだけ・・。
まとめ
今回はExcelで項目番号を入れる方法を紹介 ・・なぜなら・・以前の投稿にコメントを頂いたから♪
前回の投稿はわかり辛かったみたいで・・ゴメンナサイ♪
Excelには沢山の件数があるけど・・用途にぴったりと合ったものはなかなか見つからないもの そんな時は・・関数を組み合わせて使いのが良いかと思います♪
補足
関数を使い使わないにかかわらず・・Excelのワークシートで数式を使うならワークシートを保護することをお勧めします♪
数式を記載されたワークシートで作業をしていると・・知らぬ間に・・数式が変わってしまうことはありませんか？
これは・・数式を書き込んだセルを・・いつの間にか書き換えてしまっているために起こってしまいます・・。
それを避けるためには・・ワークシートを保護するようにしましょう♪
ワークシートを保護する場合 ・・保した後にデーターを入力するン必要が有るセルは・・ロックを解除しておきましょう
ロックを解除するには・・セルのプロパティから・・保護タブを選択し・・ロックを解除すればOK
更にロックを解除したセルは・・セルの色を変えておくとわかりやすくて良いですよね ♪
ワークシートを保護しておけば・・今回のように非表示にしたセルが・・ひょっこり表示されることも避けることが出来ます♪
今回は私が・・個人的に数式を作ってみたのですが・・これらを採用するかしないかは・・皆さん次第・・もし採用された場合自己責任でお願いしますね♪
