今回は、LARGE関数～LENB関数までをご紹介しております。
LARGE関数
ラージ
指定した○番目に大きい値を算出します。
LARGE(配列,順位)
LCM関数
エルシーエム
整数の最小公倍数を算出します。
LCM(数値1,[数値2])
LEFT関数
レフト
文字列の左端から文字を取り出せます。
LEFT(文字列,[文字数])
LEFTB関数
レフトビー
文字列の左端から指定バイト数の文字を返す
LEFTB(文字列,[バイト数])
LEN関数
レン
文字列の文字数を返す
LEN(文字列)
LENB関数
レンビー
文字列のバイト数を返す
LENB(文字列)
何気ない操作も繰り返す回数が増えると、面倒以外の何物でもありませんね。
例えば、フォルダー内に各店舗のファイルがあって、それぞれのファイルにあるシートを、一つのファイルに集めたい・コピーしたいというのも、その一つだと思います。
ファイルを開いて、シート名の上で右クリックして、シートのコピーをしていく作業をたった3回かもしれませんが、面倒ですし、ファイル数が増えれば増えるほど、面倒から【苦痛】へと変わっていきます。
全店舗ファイルを開いて、現状を確認しておきます。
各店舗のデータも確認しておきましょう。
ますは、プログラム文を確認しておきましょう。
Sub ブック結合()
Dim f_name As String
Dim path_name As String
Dim i As Long
For i = 1 To 1
path_name = Workbooks(i).Path
Next j
f_name = Dir(path_name & "￥*.xlsx")
Do While f_name <> ""
Workbooks.Open path_name & "￥" & f_name
Workbooks(f_name).Worksheets(1).Copy before:=ThisWorkbook.Worksheets(1)
ThisWorkbook.Worksheets(1).Name = Replace(f_name, ".xlsx", "")
Workbooks(f_name).Close False
f_name = Dir()
Loop
End Sub
説明の前に、実行してみます。
このように、あっという間に、一つのファイルに集めることができました。
プログラム文を確認しておきましょう。
Dim f_name As String
Dim path_name As String
Dim i As Long
お馴染みの変数宣言ですね。
For i = 1 To 1
path_name = Workbooks(i).Path
Next j
For～To～Next文で繰り返しをしていますが、何をしているのかというと、ファイルがあるパス名を取得するためです。
ファイル名だけでは、処理をしてくれません。
そのファイルが、どこのフォルダーにあるのかという情報のパスを取得する必要があるわけです。
変数名のpath_nameは、そのパスを代入するものです。
Workbooks(i).Path
で、そのファイルのあるパスの情報を得ることができます。
確認を続けていきます。
f_name = Dir(path_name & "￥*.xlsx")
先程取得した、パス名と拡張子名を結合させることで、収集元のファイル名を変数名f_nameに代入させます。
なお、マクロファイルの場合には、拡張子は「xlsm」と変更します。
拡張子を設定しておかないと、フォルダー内に関係ないファイルがある場合には、それも該当してしまうので、エラーになってしまいます。
なお、「¥（円マーク）」ですが、半角だと「円マーク」で表示してくれないので「全角の￥」にしておりますので、ご注意ください。
Do While f_name <> "" ～ Loop は、該当するファイルがある間繰り返し処理をするという意味です。
Workbooks(f_name).Worksheets(1).Copy before:=ThisWorkbook.Worksheets(1)
コピー元のシートを、コピー先の1枚目のシートの「before（前：左側）」にコピーします。
ThisWorkbook.Worksheets(1).Name = Replace(f_name, ".xlsx", "")
シート名の重複というわけにはいきませんので、今回は、コピーしたシートのシート名をファイル名に置換する（Replace）作業をしております。
Workbooks(f_name).Close False
開いたファイルを閉じます。
現場には、簡単な処理でも、量が増えると面倒な作業は多いと思いますので、マクロを作ってみるといいかもしれませんね。
Facebookページで【書いてみた】ワンポイントです。
3月29日
Excel。範囲選択で、広い範囲を選択するときは、Shiftキーを押しながらクリック技が便利ですね。
3月30日
Excel。範囲選択の表現は、A1:B5と書きますが、これは、A1～B5までを範囲選択という意味ですね。
3月31日
Excel。範囲選択で、行を選択する場合は、行番号をクリックすると行選択できますね。
4月1日
Excel。範囲選択で、列を選択する場合は、列番号をクリックすると列選択できますね。
4月2日
Excel。範囲選択で、隣接しているセル・行・列は、Ctrlキーを押すのではなく、ドラッグ操作ですね。
4月3日
Excel。範囲選択で、広い範囲の列を選択する場合は、最初の列番号をクリックして、最後の列番号のところで、Shiftキーを押しながらクリックすると選択できますね。
4月4日
Excel。範囲選択で、広い範囲の行を選択する場合は、最初の行番号をクリックして、最後の行番号のところで、Shiftキーを押しながらクリックすると選択できますね。
大量のデータがある場合、どこにあるのかわかりやすくしたいので、該当するデータの行全体に対して塗りつぶしを設定したいのですが、どのようにしたらいいのでしょうか？
次のデータがあります。
B2:B11を範囲選択して、ホームタブの「条件付き書式」にある「上位/下位ルール」の「上位10項目」をクリックします。
しかしながら、これは、あくまでも該当の「セル」に反映されるだけです。
やりたいのは、行全体を塗りつぶしたいわけです。
条件付き書式に用意されているリボンのメニューだと、セルが対象になっているだけで、行全体が対象になっていません。
このような場合は、条件付き書式の設定に数式を使うことで対応することができます。
では、改めて、A2:B11を範囲選択して、ホームタブの「条件付き書式」にある「新しいルール」をクリックします。
ルールの種類を「数式を使用して、書式設定するセルを決定」にして、次の数式を満たす場合に値を書式設定のボックスに、次の数式を設定します。
=$B2=max($B2:$B11)
数式の説明はあとにまわします。
書式ボタンをクリックして、塗りつぶしを設定しましょう。
あとはOKボタンをクリックします。
このように、最大値のデータの行全体に塗りつぶしを設定することができました。
=$B2=max($B2:$B11)
セル番地のすべてが列固定の複合参照にします。
列固定の複合参照にすることで、行全体が設定対象になります。
条件付き書式をつかって行全体を塗りつぶししたいということでしたら、行固定の複合参照と覚えておくといいですね。
B2の値が、B2:B11の中でのMAX（最大値）の値と同じだったらば、条件付き書式が反省されるという意味の数式です。
これで、大きなデータであっても、瞬時にデータ内の最大値を含むデータであることが一目でわかるようになります。
条件付き書式は、数式を使ったルールをつかうことで、視覚的にわかりやすい資料をつくることができますので、色々試してみるといいかもしれませんね。
皆様に閲覧していただいた項目の2021年2月TOP10をご紹介させていただきます
1位
Excel。セル内の一部の文字が合致したらそのデータの行全体を塗りつぶしたい。
https://infoyandssblog.blogspot.com/2021/03/excelfill-line.html
条件付き書式をつかえば、セル内の文字に条件と合致するデータが含まれていれば、そのセル自体に書式を設定することは簡単です。
2位
Excel。月間や四半期などの期間計算をするにはどうしたらいいの？
https://infoyandssblog.blogspot.com/2021/03/excelperiod-calculation.html
データからこの期間の合計値を算出したいということは多々あるケースではありますが、意外とトラブルを起こすというか、上手くいかないことがあるようなので、確認してみましょう。
3位
Excel。そこに関係性はあるのか？ないのか？相関関係はCORREL関数で一発算出できます。
https://infoyandssblog.blogspot.com/2021/03/excelcorrelcorrelation.html
データを集めただけでは、ただの数字の情報にすぎません。
しかしながら、こちらの数値とあちらの数値には関係性があるなど、Excelには、ちょっとした関数をつかうことで、今まで見えなかった角度から、仮説をみつけることができたりもします。
4位
Excel。移動平均グラフを作りたいけど、横軸を5日おきにしたい。
https://infoyandssblog.blogspot.com/2021/03/excel5moving-average.html
5位
Excel。シートを大量に追加して、指定したシート名に変更するのが面倒なので楽したい。
https://infoyandssblog.blogspot.com/2021/03/exceladd-sheets.html
6位
Excel。100%積み上げ横棒グラフのデータラベルに、値とパーセントを表示したい
https://infoyandssblog.blogspot.com/2021/03/excel100data-label.html
7位
Excel。一日のタイムスケジュールを管理する24時間横棒グラフを作ってみる
https://infoyandssblog.blogspot.com/2016/03/excel24hour-schedule24.html
8位
Excel。一行おきの数値の合計を簡単に算出したいけどどうしたらいいの？
https://infoyandssblog.blogspot.com/2020/12/exceltotal-every-other-line.html
9位
Excel。料金量がわかりやすい階段グラフの作り方
https://infoyandssblog.blogspot.com/2017/09/excelstaircase-graph.html
10位
Excel。折れ線グラフの間を塗りつぶしたいけど、どうしたらいいの？
様々な条件でクエリをつくって、それをエクスポートするなど、作業をするわけですが、抽出するにあたり、抽出方法が色々あって、ちょっと知っているだけで、簡単に抽出することができたりします。
例えば、次のようなテーブルがあります。
簡単そうに思えますが、一筋縄ではいきません。
日頃から五十音順というのが頭の中にあるので、カキクケコだなと認識はできますが、実際抽出しようとすると、考えてしまいます。
仮に、左から一文字目が「カ」だったら抽出対象のような条件を作るとしたら、最低でも5回繰り返さないといけません。
とても面倒です。
Excelの抽出でも、今回の「カ行」のような五十音順の行抽出は面倒です。
ところが、Accessだと、ワイルドカードの「*」をつかうことで、とてもシンプルな抽出条件で、クエリをつくることができます。
それでは、クエリを作っていきます。
作成タブの「クエリデザイン」をクリックします。
五十音順に並べるとわかりやすいので、「並び替え」条件に「昇順」の設定をします。
確定すると、Likeが自動的につきます。
自分で入力してもいいのですが、入力ミスをする可能性がありますので、Accessがやってくれるところは、任せてしまうほうがいいですね。
今回の抽出条件のポイントは2つあって、1つ目のポイントは、Like "[カ-ゴ]*"。
「”（ダブルコーテーション）」で囲んであげないとメッセージが表示されたり、パラメータクエリとして認識されたりと、ちょっとした入力ミスで、実行することができませんので、注意しましょう。
そして、もう一つのポイントは、ア行のように、「濁点」がなければ、「あ～お」とするだけでいいのですが、今回のカ行だと、「か～こ」としてしまうと、「濁点」が付いている「ゴ」は抽出対象から外れてしまうので、「濁点」のことも考慮して抽出条件とする必要があります。
Accessでのワイルドカードをつかった、抽出方法は、色々ありますので、確認してみると、意外と現場レベルでつかえる方法があるかもしれません。
使うことは滅諦にない関数というのも結構ありまして、ADDRESS関数というのもその一つではないでしょうか？
ADDRESS関数は、行番号と列番号で指定したセル番地を構成することができる関数なんです。
ADDRESS関数の読み方はそのものズバリ「アドレス」です。
ADDRESS関数の引数も確認しておきましょう。
ADDRESS（行番号,列番号,[参照の種類],[参照形式],[シート名]）
次の表を使って確認してみましょう。
ADDRESS関数も手入力するほうが楽な関数です。
というのも、入力途中で引数のフォローが入ってくれるからです。
1の絶対参照は、$B$4
2の行（絶対参照）/列(相対参照)は、B$4
3の行（相対参照）/列(絶対参照)は、$B4
4の相対は、B4
と設定できます。
今回は、「1」の絶対参照を採用しました。
R1C1形式化、A1形式かを選択することができます。
今回は、「1」のA1形式を採用します。
シート名はこのシートとしますので、省略します。
完成した数式は、
=ADDRESS(4,2,1,1)
結果を見てみると、「$B$4」
えっ、B4の100が表示されるのでは？と思ったかもしれませんが、このADDRESS関数は、あくまでも、セル番地を構成するだけの関数なので、セルの内容を表示させるものではありません。
セルの内容を表示させるには、INDIRECT関数の力を借りる必要があります。
A1の数式を次のようにINDIRECT+ADDRESS関数のネストに変更してみます。
=INDIRECT(ADDRESS(4,2,1,1))
Excel VBAでつかうというのもいいですし、アイディアによって、使えそうな関数がExcelにはたくさんありますので、色々調べたり、組み合わせみると、現場で使えるものも沢山ありそうですね。