表の作成あれこれ ③ 取り消し線で訂正しない

Excelで作られた定価表で、面倒な編集を何とかしたい。
そんな相談を受けたことがある。実際の定価表は、こんな感じだった。
f:id:Infoment:20210418221648p:plain

これは、手書きの表をExcelで忠実に再現した、よく見かけるパターンのやつじゃ
ないか。しばらく眺めてから、依頼者に訊いてみた。
こんな感じに直しても良いですか?
f:id:Infoment:20210418222500p:plain

もともとの表では、いくつもの問題点があった。

  1. 各値(価格や日付)が全て文字列となっていて、再利用できない。
  2. 最新情報がセル内のどの行でも成立する(取り消し線次第)。
  3. 日付の書式がバラバラで統一感がない。

一方でテーブルとして、情報ごとに項目分けすれば、まず見た目が揃う。
また、誰が入力しても同じ品質の結果が得られるし、何より編集が楽だ。
最新情報だけ欲しいなら、ピボットテーブルを利用して、こんな感じで
どうだろう。
f:id:Infoment:20210418223419p:plain

このときは提案を受け入れてもらえたので、こちらで表の再作成のお手伝い
をした。長い目で見れば、再作成に掛かる工数などは全然大したことないと
思ったから。お勧めです。

参考まで。

表の作成あれこれ ② コード管理で揺らぎを抑制

Excelで作られた販売管理表をもとに、売り上げを集計したい。
そんな相談を受けたことがある。管理表の顧客名は、こんな感じだった。
f:id:Infoment:20210414214852p:plain

しばらく眺めてから、依頼者に訊いてみた。(幸い客先名称を管理する
客先名称コードがあったので、)こんな感じに直しても良いですか?
f:id:Infoment:20210414215853p:plain

人の目で見れば、「コーヒー」と「コーヒー」は同じもの。しかし、Excel
とってはそうではない。これは、文字のベタ打ちで頻発する問題と思われ。
また「株式会社コーヒー」と「(株)珈琲」が別会社だった日には、混乱は
不可避と思う。

一方でコード管理とすれば、元表のような表現の揺らぎは、簡単に抑制する
ことができる。またコードを打ち間違えた場合、

  1. 既存コード  ⇒ まったく別の名称となり気づき易い
  2. 未設定コード ⇒ エラーとなるため気づき易い

というメリットもある。

このときは提案を受け入れてもらえたので、マクロを作成するまでもなく、
ピボットテーブルで簡単に解決することができた。お勧めです。

参考まで。

表の作成あれこれ ① 日程は開始日と終了日で表現

Excelで作られた管理表Aから日付を抽出し、別の管理表Bで再利用したい。
そんな相談を受けたことがある。管理表Aの日付は、こんな感じだった。
f:id:Infoment:20210412232221p:plain

しばらく眺めてから、依頼者に訊いてみた。管理表Aを、こんな感じに
直してもいいですか?
f:id:Infoment:20210412232642p:plain

特に、A社のように1日しかない場合、開始日と終了日に同じ日を入れることに
抵抗を感じる人もいるかもしれない。しかしどちらかを空欄にした場合、

  1. 意図して空欄にしているか
  2. 入れ忘れで空欄になったか

の区別がつかないという問題がある。

このときは提案を受け入れてもらえたので、その後のマクロ作成が
とてもやり易くなった。お勧めです。

参考まで。

OR の 代わりに Like を使う

最近のお気に入りは、OR演算子の代わりに、Like演算子を使うこと。
使える場面は限られるが、例えば下記のような場合。

<条件>

  • 文字列の一文字目がAまたはBまたはCのとき、〇を返す。
  • 上記以外の場合、×を返す。

昔は、こんな風に書いていた。

Function LikeTest(str As String) As String
    If Left(str, 1) = "A" Or Left(str, 1) = "B" Or Left(str, 1) = "C" Then
        LikeTest = "〇"
    Else
        LikeTest = "×"
    End If
End Function

テストしてみると、こんな感じになる。

Sub test()
    Debug.Print LikeTest("A123")
    Debug.Print LikeTest("D456")
    Debug.Print LikeTest("C789")
End Sub

f:id:Infoment:20210407230507p:plain

或いは、こんな風に書くこともできる。

Function LikeTest(str As String) As String
    Select Case Left(str, 1)
        Case "A", "B", "C"
            LikeTest = "〇"
        Case Else
            LikeTest = "×"
    End Select
End Function

さらに、Like "[ABC]" が「AまたはBまたはC」を意味することを
用いれば、こんな書き方が可能だ。

Function LikeTest(str As String) As String
    If Left(str, 1) Like "[ABC]" Then
        LikeTest = "〇"
    Else
        LikeTest = "×"
    End If
End Function

せっかくLike演算子を用いるのだから、ワイルドカードを用いたこちら
の記述の方が、より良いかもしれない。

Function LikeTest(str As String) As String
    If str Like "[ABC]*" Then
        LikeTest = "〇"
    Else
        LikeTest = "×"
    End If
End Function

数珠繋ぎにORを書くことに飽いた方は、ぜひ一度お試しください。
なお、最後にどの用法に落ち着くかは、時と場合と各位のお好みで。

参考まで。

社名をキーに、各商品の合計額を求めるアレコレ の続き

前回、こちらの記事を作成した。
infoment.hatenablog.com

ところが、よくよく確認してみると、テーマの趣旨を取り違えていた。
各社各商品の売り上げ記録を合計せねばならぬところ、売上表を価格表と
取り違えてましたm(_ _)m。

ということで、改めて考えてみた。
f:id:Infoment:20210404174639p:plain

↓のような表があるとする。
各社向けに各商品がいくら売れたか、その売り上げを記録した表だ。
f:id:Infoment:20210404174803p:plain

ここから、各社向け各商品の売り上げ合計額を知りたい。
さて、これを求める場合、もっとも簡単なアプローチは何か。
おそらく、マクロではなく、ピボットテーブルではないかと思う。

1.ピボットテーブルを選択し、挿入タブからピボットテーブルを選択。
f:id:Infoment:20210404175136p:plain

2.テーブルの横に、ピボットテーブルを作成。
f:id:Infoment:20210404175235p:plain

3.行に「社名」を、値に「商品A~C」をセット。
f:id:Infoment:20210404175333p:plain

4.ちょこちょこっと書式を整えて、出来上がり。
f:id:Infoment:20210404175524p:plain

ピボットテーブル、お勧めです。

参考まで。

社名をキーに、各商品の合計額を求めるアレコレ

コロ子さんのブログに書き込みしたところ、過去記事を紹介していただいた。
koroko.hatenablog.com

念のため、おさらい。
↓のような表に於いて、社名ごとに商品の合計額を知りたい。
f:id:Infoment:20210331220005p:plain

私の過去記事では、

  1. 辞書に、全商品の金額を格納した配列をセットするパターン
  2. 辞書を配列にして、商品毎に金額をセットするパターン

を紹介しているが、コロ子さんのブログでは、後者が採用されていた。

そこで今回こちらでは、辞書に配列を格納するパターンをやってみようと思う。
まず、項目名で列挙体を作成する。

Enum 項目名
    en社名 = 1
    en商品A
    en商品B
    en商品C
End Enum

次に、社名ごとに表をスライスし、行を抜き取る。

Property Get Dict() As Object
    Dim tempDict As Object
    Set tempDict = CreateObject("Scripting.Dictionary")
    Dim SourceArray As Variant
        SourceArray = Range("A1:D5")
    Dim i As Long
        For i = 2 To UBound(SourceArray)
            tempDict(SourceArray(i, 項目名.en社名)) = _
                WorksheetFunction.Index(SourceArray, i, 0)
        Next
    Set Dict = tempDict
End Property

これで、準備完了だ。それでは、A社の商品A~Cの合計を求めてみよう。

Sub test()
    Dim 価格 As Object
    Set 価格 = Dict
        
        Debug.Print 価格("A社")(en商品A) + _
                    価格("A社")(en商品B) + _
                    価格("A社")(en商品C)
    
End Sub

結果は、以下のとおり。
f:id:Infoment:20210331220543p:plain

ちなみに、毎回必ずA~Cすべてを合計するなら、話はもっと早くなる。
単純に、SUM関数で足してあげればよいわけで。
※文字列である社名は足し算に於いて無視される。

Sub test2()
    Debug.Print WorksheetFunction.Sum(Dict("A社"))
End Sub

そしておそらく、私が思いつけていないだけで、世の中にはもっと良い方法が、
きっとあるに違いない。どの方式を採用するかは、時と場合と各位のお好みで。

参考まで。

50円単位で切り上げる(失敗談)

かつて(10年以上前に)、このような相談を受けたことがある。

Excelで、金額を50円単位で切り上げたい。

例えば、34円を切り上げて50円にしたい。
しかし、十の位を切り上げれば100円に、一の位を切り上げれば40円と
なって、50円には届かない。さて、どうしたものか。
f:id:Infoment:20210323230652p:plain

しばらく考えて、ふと思いついた。
そうだ、あらかじめ2倍して切り上げてから、2で割ればよいのでは?
f:id:Infoment:20210323230910p:plain

上手くいった。
これは、なかなかの閃きではないかと、思わず自画自賛

そして時は流れて今日、遅ればせながらCEILING関数の存在を知った訳で。
support.microsoft.com
f:id:Infoment:20210323231204p:plain

こんな簡単なことだったとは。でも、頭を悩ませたあの時間は、
決して無駄ではなかったと思いたい。

それにしても、これは色々と使えそうです。

参考まで。