Facebookページで【書いてみた】ワンポイントです。
2月22日
Excel。TRUNC関数。
読み方は、トランクで、指定桁数になるように切り捨てる
2月23日
Excel。TYPE関数。
読み方は、タイプで、データ型を表す数値を算出する
2月24日
Excel。UNICODE関数。
読み方は、ユニコードで、文字列の最初の文字のUnicode番号を返す
2月25日
Excel。UNICHAR関数。
読み方は、ユニコードキャラクターで、数値で指定したUnicode番号の文字に変換
2月26日
Excel。UPPER関数。
読み方は、アッパーで、英字を大文字に変換する
2月27日
Excel。VALUE関数。
読み方は、バリューで、文字列を数値に変換する
2月28日
Excel。VAR関数。
読み方は、バリアンスで、数値群を標本データとみなした不偏分散を算出
Excelには、セル内での改行方法は色々あります。
知っているいないで、資料を効率よく、また綺麗につくることが難しい場合もありますので、今回は、三種類の改行のやり方をご紹介していきます。
最初は、お馴染みの「セル内改行」のAlt+Enterから確認していきます。
次のように、セルの列幅から溢れてしまったときに、好きな場所で改行したい時に行うのが、「Alt + Enter」というショートカットで改行することができます。
このように、セル内で改行することができました。
なんで、Alt + Enterをするのかというと、ホームタブの「折り返して全体を表示する」をつかうと、列幅に合わせて、全体を表示してしまうので、好きな所で改行することができません。
続いては、数式（関数）をつかった、改行を紹介していきます。
先程のAlt + Enterは、一つのセルだけならば、いいのですが、実施したいセルが複数ある場合は、何度も繰り返す必要があるので、面倒な作業となってしまいます。
例えば、次のような表。
A列の都道府県とB列の市区町村を結合したのが、D列。
D列の数式を確認してみます。
=A2&CHAR(10)&B2
この数式は、「&（アンパサンド）」をつかった文字結合です。
CONCAT関数をつかった、
=CONCAT(A3,CHAR(10),B3)
という数式でもOKです。
ポイントは、CHAR(10)という、CHAR関数。
この関数は、該当する番号の文字を算出してくれます。
「10」というのは、改行コードの番号なので、CHAR(10)とすることで、文字結合の途中で改行することができるというわけです。
数式なので、オートフィルで数式をコピーすれば、先程の、Alt+Enterよりも早く処理をすることができます。
なお注意が必要な点があります。
最後の改行のケースは、表示形式の改行です。
次のような日付と曜日が表示されいる表。
m"月"d"日"(aaa)
と設定することで、日付と曜日が表示されるようにしています。
次のように、日付と曜日を2行で表示するようにすれば、列幅をコンパクトにすることができます。
表示形式で、任意の箇所に改行をしたい時は、「Ctrl + J」というショートカットキーを入力します。Ctrl + Jは、表示形式内での改行を行うことができます。
「(」の前に、Ctrl + J と入力して確定しましょう。
実行後、見た目に変化はありません。
Excelは、改行の方法がアレコレ変わりますので、機会があるときに、覚えていくといいかもしれませんね。
大したことない処理ほど、イチイチ設定するのは、かえって面倒に感じるものです。
まして、頻繁にその処理を行うとなると、より一層面倒に感じたりします。
例えば、次のようなデータを読み込んだとします。
そして、できれば、次のような「0（ゼロ）」付の連番にしたい。
連番は、オートフィルで設定したら、表示形式のユーザー定義で、ゼロ付数値に表示するようにすればいいわけですね。
だけど、これが、マクロをつくることで、すぐに設定できるならば、楽になります。
では、どのようにしたら、ゼロ付数値の連番を設定することができるのでしょうか？
Excel VBAのプログラム文をつくってみましょう。
Sub zero連番()
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "b").End(xlUp).Row
For i = 2 To lastrow
Cells(i, "a").NumberFormat = "000"
Cells(i, "a").Value = i - 1
Next
End Sub
実行して、確認してみると、A列のNOにゼロ付数値の連番が作成することができました。
このプログラム文を説明していきます。
最初はお馴染み、変数の宣言です。
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "b").End(xlUp).Row
B列のデータの最終行番号を取得するための行ですね。
lastrowという変数に代入させています。
そして、連番を設定するので、繰り返し処理を作っていきます。
それが、For i = 2 To lastrow ～Next です。
データそのものは、2行目から始まっているので、「2」から開始させています。
そして、
Cells(i, "a").NumberFormat = "000"
このプログラム文が、今回のポイントとなる行です。
NumberFormatプロパティをつかうことで、表示形式を設定することができるようになります。
ゼロ付数値にしたいわけですから、通常のExcelの表示形式と同じように、「000」とすることで、ゼロ付数値で表示することができるわけです。
Cells(i, "a").Value = i - 1
この行は、連番を設定するための一行です。
1からの連番にしたいので、「i-1」とした数値を代入させています。
今回は、
Cells(i, "a").NumberFormat = "000"
Cells(i, "a").Value = i - 1
という順番でしたが、逆にしても、ゼロ付数値で連番を設定することができます。
なお、ゼロ付数値にしない場合には、
Cells(i, "a").Value = i – 1
でOKです。
NumberFormatプロパティは、データを読み込むプログラム文に組み込んで使うことで、データを読み込んだ後の表示形式の設定をしないで済みます。
使ってみると結構便利なプロパティではないかと思います。
現在使用しているプログラムに組み込んでみると、作業効率化より改善できるかもしれませんね。
このBLOGの記事を、カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。
Excel。変動率を比べるグラフを作るには、対数目盛を使うのがGOOD!
折れ線グラフって、推移はわかるけど、
実際にどのぐらい改善されたりアップしていたりするのがわかりにくいんだよね。
とのこと。
＜続きはこちら＞
Excel。変動率を比べるグラフを作るには、対数目盛を使うのがGOOD!
https://infoyandssblog.blogspot.com/2015/11/excelrateofchangegood.html
Excel。折れ線グラフの間を塗りつぶしたいけど、どうしたらいいの？
折れ線グラフが2本あって、その間の【差】をわかるように塗りつぶしたい
＜続きはこちら＞
Excel。折れ線グラフの間を塗りつぶしたいけど、どうしたらいいの？
https://infoyandssblog.blogspot.com/2015/12/excelgraph.html
Excel。データの行を非表示にしても合計値が変わらないので、合致するようにしたい。
行を非表示にしても、合計値が連動して変わってほしい
＜続きはこちら＞
Excel。データの行を非表示にしても合計値が変わらないので、合致するようにしたい。
セルの結合は設定するのが簡単なのですが、結合したセルの値を別のセルにコピーするとか、一覧表を作る場合、意外と大変というか、困ってしまうことがあります。
例えば、次の表のような処理をする場合で、説明していきます。
A列には、セル結合された担当者名が入力されています。
入力規則のリストなどで使うために、D列のように担当者一覧を作りたいとします。
今回は、4名なので、コピー&ペーストでも、どうにかなりますが、件数が多かったらコピー&ペーストでは大変です。
Excel VBAでマクロをつくってもいいのですが、マクロを作成すること自体も面倒です。
今回のような、結合セルの値から、一覧表をつくるには、ちょっとした関数を組み合わせれば、可能になります。
では、早速作っていきましょう。
C2に、次の数式を設定して、オートフィルで数式をコピーします。
=IF(A2<>"",ROW(A1),"")
このように、算出されます。
縦方向にセルが結合されている場合、結合されている一番上のセルに値が設定されいます。
値が入っているか、どうかを判断したいので、IF関数を使います。
論理式は、「A2<>""」。
A2が空白ではなかったらと、問い合わせます。
真の場合は、「ROW(A1)」。
ROW関数で行番号をつかって、数値を算出させます。
義の場合は、空白 とします。
これによって、値があるセルの場所がわかりました。
続いて、D列にまとめる作業を行います。
D2に次の数式を設定します。
=IFERROR(INDEX($A$2:$A$10,SMALL($C$2:$C$10,ROW(A1))),"")
数式を説明していきます。
IFERROR関数を使うのは、このあと、オートフィルで数式をコピーすると、抽出データがないセルに、「#NUM!」というエラーが表示されるので、その防止のために使います。
INDEX関数は、INDEX（参照,行番号,列番号）という引数をもっています。
参照は、担当者名を算出したいわけですから、$A$2:$A$10。
オートフィルで数式をコピーしますので、絶対参照を設定します。
行番号は、
SMALL($C$2:$C$10,ROW(A1))
SMALL関数は、その数値の順位の数値を算出します。
このために、先程のIF関数で算出したわけです。
SMALL関数は、SMALL（配列,順位）という引数をもっています。
配列は、IF関数で算出した範囲の、$C$2:$C$10
絶対参照も忘れずに設定します。
順位は、ROW(A1) A1を指定することで、「1」という数値を算出することができます。
オートフィルで数式をコピーすると、A2・A3とかわることで「2」「3」とスライドすることができます。
列番号は、今回複数列ではないので、省略します。
すると、このような結果になりました。
最後に、C列の数値を表示してあると、カッコ悪いので、非表示にしますので、C2:C10を範囲選択して、セルの書式設定ダイアログボックスを表示します。
表示形式の「ユーザー定義」にして、種類に「;;;」（セミコロン×3）と設定します。
Facebookページで【書いてみた】ワンポイントです。
2月15日
Excel。TTEST関数。
読み方は、ティーテストで、t検定の確立を算出
2月16日
Excel。T.TEST関数。
読み方は、ティー・テストで、t検定の確率を算出 Excel2010以降
2月17日
Excel。TRANSPOSE関数。
読み方は、トランスポーズで、縦横を入れ替えた表をする
2月18日
Excel。TREND関数。
読み方は、トレンドで、重回帰分析による値を予測する
2月19日
Excel。TRIM関数。
読み方は、トリムで、不要なスペースを削除する
2月20日
Excel。TRIMMEAN関数。
読み方は、トリムミーンで、異常値を除いて平均値を算出
2月21日
Excel。TRUE関数。
読み方は、トゥルーで、TRUEを返す