≪目次≫
いきさつ
ある会社の運行管理者？ の方から Excelに関して相談を受けた。
- 上のような Excelデータ（架空のデータ）を作っている。
- 1ヶ月毎に、車番ごとの実績（距離、荷重等）を電卓を叩いて計算している。
- 距離は運賃計算上、全てに入力しているが、
例えば 2月1日の車番 35は 125km、2月2日の車番 3333は 120km‥‥ で集計する。
- 距離は運賃計算上、全てに入力しているが、
Excelで最後まで集計出来ないか‥‥ というもの。
以下は独り言‥‥
- グループ インディケーション かぁ～
- これは印刷時に見易くするための手法であって、シート内でやられたのでは集計は出来ませんょ...
- 「車番」にエラー インジケーターが付いているが‥‥
- 一般的には数字だけど（セルの書式が）「文字列」になっているのは、過去の経験からだろう‥‥ で、理解できる。
- 「距離」は「日付・車番」で同じ値が入っている‥‥
- というが、入力ミスが見つかった。Ctrl＋Ｄ は使っていないのかな？
- マクロ（VBA）を使えば難しくは無いが、後の管理が出来ないだろうし...
以下、Excel2013で作成した。
§１．車番単位の距離「走行距離」列を作る
- 後述するが「車番」は集計のキーなので、全レコードに必要。
- 車番の空白が埋まった後は、「距離」から「月日・車番」単位の距離を求めることができないだろう。（たぶん）
- そこで、車番を埋める前に「月日・車番」の距離「走行距離」（名前は任意）を作る。
- 「走行距離」の場所（列）は、本来ならば車番の右、手入力のし易さでは「距離」の 右または左 であろう。
- 後者であれば、走行距離入力後、カーソルが距離に移ったら、Ctrl＋R が使える。
- 今後は手入力するものとする。
- 「走行距離」の列を挿入する。
- 上図では E列。
- セルE2 に "=IF(B2="","",F2)" を入力し、
- 関数の説明：車番が空であれば空値を、そうでなければ車番を入れる。
- データの最終行（17行）まで、フィルハンドルでコピーする。
- データができたら、計算式を削除しておく
∵車番の空白が埋まると、「距離」と同じになってしまうため。- セルE2～E17 を選択し、
- コピーする。（Ctrl＋C）
- セルE2 を選択し、
- 貼り付け、
- ［貼り付けのオプション］の「値」を選択する。
§２．車番の空白を埋める
VBAでいいのであれば直ぐにでも出来るが、関数で行う方法は知らないので、Google先生に聞いた‥‥
- 空白セルに上のセルの値を一括コピーしたい（今日を乗り切るExcel研究所 2017-05-11）
◆同上空白セル
親切なのか「同上」なセルが空欄になっている Excel シートがよくあります。
このようなシートをデータとして使う（CSVなど）には、空白セルを上の方にあるデータで補完させなければなりません。数か所までならコピー＆ペーストや下方向にコピー(Ctrl + D)を黙々とやればいいのですが、何か所もあったり行数も多かったりすると行ったり来たりでやっていられません。
一括で空白セルを埋める方法はないのでしょうか。◆手作業で空白セルを埋めるには
一括で空白セルを埋めるには、二つの操作の合わせ技を使います。- 「選択オプション」ダイアログによる「空白セル」の選択
- Crtl + Enter による一括入力
こんな方法をよく思いつくなぁ～～～
§２.１．車番の列の書式を「文字列」以外にする
車番の列のセルの書式が文字列であると関数がそのまま入力されてしまい、関数が使えない。
関数を入力するために‥‥ ここでは「標準」にする。（違うところで問題が出るかも知れないが...）
- B列を列選択し、
- ホーム タブ/数値 グループの「表示形式」を「文字列」にする。
- Ctrl＋１ ⇒ 表示形式 タブの「分類」を「標準」にしてもよい。
§２.２．空白のセルのフォントの色を灰色にする
空白のセルを埋めると、「チェックし難い‥‥」というのは目に見えている。そこで、フォントの色を灰色にしよう‥‥ というもの。
- 空白を埋めるセルを選択する。
- セルB2～B17を選択する。
- ホーム タブ/編集 グループの［検索と選択］⇒ 条件を選択してジャンプ をクリックする。
- 【選択オプション】ダイアログボックスの ◉空白セル を選択し［OK］。
- 空白のセルのみが選択されたら Ctrl＋１。
- 【セルの書式設定】ダイアログボックスが出るので、［フォント］タブの「色」を 灰色 にし［OK］。
- 色および灰色の程度は任意。
- 選択を解除して終わる。
§２.３．車番の空白を埋める
- 空白を埋めるセルを選択する。
- セルB2～B14を選択する。
- 必ず B2 から選択する。B14～B2 で選択すると結果が正しく得られないかも...
- ホーム タブ/編集 グループの［検索と選択］⇒ 条件を選択してジャンプ をクリックする。
- 【選択オプション】ダイアログボックスの ◉空白セル を選択し［OK］。
- セルB2～B14を選択する。
- 空白のセルのみ選択されので‥‥
- アクティブセルが B3 になっているのを確認し、
- "=b2"（アクティブセルの直上のセル）を入力（数式バーの方が入力し易い）し、Ctrl＋Enter（アクティブセルを動かさない）する。
- 空白セルが埋まる...
- 選択を解除して終わる。
車番別集計表
車番別の集計を行うには［小計］機能を使うのが簡単だが‥‥
- 車番で並べ替える必要がある。
- 元の順番に戻すには、例えば「入力順」という項目（列）を作る必要がある。
等の条件がある。ここでは幸い、シートが 月別 になっているので、これを前提にすれば‥‥
- 車両数が少ないのであれば、運行記録の上に作るのが個人的にはお勧めかも...
詳細は省略するが、- 車番別集計表を上にすることにより、その月のデータ数に関わらず位置が固定される。
- 車番の入力を「リスト」からにすることにより、集計漏れを無くせる‥‥ はず。
- 月々のデータ数は異なるが、［ウィンドウ枠の固定］を使えば、関数の修正も容易になる‥‥ はず。
疑問・質問等があれば、iPS-BB（掲示板）を使って欲しい...
追伸（2020-02-20）
- §２３．で作成したデータ、空白を埋めた車番は「上のセルの値をコピーしている」ので、車番で並べ替えると値が変わってしまう可能性がある。（と、懸念している）
並べ替えるのであれば車番列をコピーし、同じ場所に「値」を貼り付ける必要がある。 - いっその事、(1)セルの書式を「標準」にし、(2)入力されている（文字列の）車番を数値に変換したら支障が出るかなぁ～ っと思ったが面倒なので止めかけたが、簡単な方法が見つかった。今回はパスしたが、覚えておくと使えそう‥‥
- 文字列形式の数値を数値形式に修正する
方法 1: エラー チェック機能を使用して文字列形式の数値を変換する
- ワークシートで、左上隅にエラー インジケーターが表示されている 1 つのセルまたはセルの範囲を選びます。
- 選んだセルまたはセルの範囲の隣に表示されているエラー ボタンをクリックします。
- メニューの [数値に変換する] をクリックします。 日付を変換せずに単にエラー インジケーターを非表示にするには、[エラーを無視する] をクリックします。
- これは後から気が付いたことだが、数値に変換するのであれば データ タブ/データツール グループの［区切り位置］が簡単で良さそう...
- 文字列形式の数値を数値形式に修正する
