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

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

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

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

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

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

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

参考まで。

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

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

しばらく眺めてから、依頼者に訊いてみた。（幸い客先名称を管理する
客先名称コードがあったので、）こんな感じに直しても良いですか？

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

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

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

というメリットもある。

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

参考まで。

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

Excelで作られた管理表Ａから日付を抽出し、別の管理表Ｂで再利用したい。
そんな相談を受けたことがある。管理表Ａの日付は、こんな感じだった。

しばらく眺めてから、依頼者に訊いてみた。管理表Ａを、こんな感じに
直してもいいですか？

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

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

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

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

参考まで。

OR の 代わりに Like を使う

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

＜条件＞

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

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

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

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

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]" が「ＡまたはＢまたはＣ」を意味することを
用いれば、こんな書き方が可能だ。

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

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

ということで、改めて考えてみた。

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

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

１．ピボットテーブルを選択し、挿入タブからピボットテーブルを選択。

２．テーブルの横に、ピボットテーブルを作成。

３．行に「社名」を、値に「商品Ａ～Ｃ」をセット。

４．ちょこちょこっと書式を整えて、出来上がり。

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

参考まで。

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

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

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

私の過去記事では、

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

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

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

結果は、以下のとおり。

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

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

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

参考まで。

50円単位で切り上げる（失敗談）

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

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

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

しばらく考えて、ふと思いついた。
そうだ、あらかじめ２倍して切り上げてから、２で割ればよいのでは？

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

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

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

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

参考まで。