highdy の気まぐれブログ

ご訪問ありがとうございます。ジャンルがメチャメチャの気まぐれブログ。パソコン関連記事を中心に時間を見つけて・・・。

「,」で数を数える技(わざ)

2022年05月28日 | パソコン



  

  


上記の目次はすべてを反映していないものがあります。

全自動の Excel のシステム
 今回の例はどのクーポンも1日に1枚使えるもので、その使用日を入力するだけでその回数も自動的に数え、各個人ごとに1か月分の使用状況をクーポン種類別に集計・寄付額も計算し、全自動で使用者名別伝票を作成、[Ctrl] + 「P」、[Enter] で印刷もしてくれる Excel のシステムです。沢山クーポンを使う(恩恵に与る)ほど、寄付金も多くなるシステムです。
Excel が使える方なら誰でもできそうな、入門者向けの計算式や関数しか使われていないとても簡単な表ですが、意外な落とし穴があります。簡単だと馬鹿にしてはいけませんよ!

入力画面
 その元となるデータの入力画面は、以下のようになっており、入力するのは使用者名使用日のみです。
入力ミスをしないように、文字やセルの色分けがしてあります。



上記に示す各セルには、以下のような計算式・関数を使いました。
① =IF(D6="","",D5*F6) 単純な掛け算の値。
② =IF(G6="","",G5*I6) 同上。
③ =IF(J6="","",J5*L6) 同上。
④ =IF(M6="","",M5*O6) 同上。
 =SUM(D7:N7) 横方向の合計を求めます。 注記1
⑥ =IF(T6="","",100*T6) 交通費を求める掛け算。「100円」は文字列なので、
計算式に「100」を入れています。
⑦ =IF(T6="",P6,P6+R6) 小計と交通費を加えた値。
=IF(D6="",0,LEN(D6)-LEN(SUBSTITUTE(D6,",",""))+1) 注記2
=SUBTOTAL(9,P6:P38) 最下行で合計を求める場合も、SUBTOTAL関数で範囲はP6を始点に指定します。自動的に小計(SUBTOTAL)を飛ばして計算してくれます。
=SUBTOTAL(9,R6:R38) 上記に全く同じ。
=SUBTOTAL(9,U6:U38) 上記に全く同じ。
⑫ =F6+I6+L6+O6 数が多い場合は、COLUMN関を使いましょう。


「,」で数を数える
 今回の最大のポイントは、半角の「,」(カンマ)の数で回数を数える点です。従って、日数を一つだけ入力してカンマが無い場合でも「1」として数える必要があります。(セル D8 や G8 の例)
数を数える方法ですから、どんな文字列(例えば、りんご,ミカンなど)でも構いませんが、区切りに半角の「,」が使ってあれば、いろんな場面で応用ができます。


落とし穴にご注意!
注記1
 ここで横方向にSUBTOTALを使用すると、最下行で合計を求める場合に(エラー表示はしませんが、)エラーになり、計算値が「0」となります。「計算しない」が正しい表現。)
理由は、最下行の SUBTOTAL は、合計を出すために途中の SUBTOTAL(小計)を飛ばして計算するのです。従い、すべてが SUBTOTAL だとすべて計算を飛ばすので「0」を沢山足すのと同じことになり本来のエラーではないのです。
注2)
この式で、IF(D6="",0の部分を IF(D6="",""と書くと、回数は後で「数値」として扱うもので、「空白」の意味ではないので好ましくありません。
(もし、「””」を使った場合、空白の文字列×数字の計算をさせたことになり、数字がないときは結果がエラー表示になります。)
なお、「0」なのにゼロが表示されていないのは、ファイルのオプションで「ゼロ」を表示させないように指定しているためです。

注記1)は多くの方がハマって苦労する代表例で、気をつけていましたが、注記2)に見落としがありました!
highdy も作業を終了してできた!」と大喜び、安心したのも束の間、コーヒーを飲んで休息、テスト運用してみて気づき大慌てで修正しました。





本日もご来訪いただきありがとうございました。

記事の保存には、画面上で右クリック → 印刷 →  PDFに保存 
することにより、すべてのリンクも活用できます。
 

   スマホでどうぞ 


コメント (4)    この記事についてブログを書く
« 都合によりしばらくお休みを・・・ | トップ | 一つ飛ばしのコピー、不規則... »

4 コメント

コメント日が  古い順  |   新しい順
こんばんは (KEI)
2022-05-28 21:52:31
こんばんは。
ちょくちょくお伺いして、勉強させていただいています。
すべて無料のボランティアされているんですね。
分からないことはコメントで質問させてもらってもよろしいでしょうか?
今後ともよろしくお願いします。
返信する
判らないことはお気軽に・・・ (highdy)
2022-05-29 08:02:18
KEI さん

いつもご訪問ありがとうございます。
私にお答えできることは何でも対応しますのでお気軽に質問をしてください。
メール、オーナーにメッセージ、コメント欄などいずれでも結構です。
無料の生徒募集はやめましたが、無料登録会員を適時限定で認めています。登録会員の方はSNSや電話による音声のサポート、リモートのサポートもしています。
返信する
私にはちょっと難しいかな?・・と思いました。 (fumiel-shima)
2022-05-29 08:50:29
highdyさん、おはようございます。

正直言ってエクセルを頻繁に使うことから離れて久しい私にはちょっと難しいかな?・・と思いました。
先日のテーマは何とか理解できたのですが・・
でもゆっくりと時間をかけてやってみると難しさだけではなく便利さや効率の良さなどが少しずつでも理解できるのかもしれませんね。
返信する
ご心配は無用です! (highdy)
2022-05-29 13:06:55
fumiel-shima さん

いつもご訪問ありがとうございます。
Shima さん、ご心配は無用です!
関数は初めは書いてある使い方を真似て覚えるもので、いきなり考えて使おうと思っても意の外に難しいもので、先ずは慣れることです。
慣れてくると、計算しなかったり、エラーが出てくる理由が理解できます。つまり、使い慣れた関数の性質が解るからです。
この例では、SUBTOTAL関数は、指定範囲内にあるSUBTOTAL関数を除外して加算する性質があります。従って、横の合計を算出するのに、すべてSUBTOTAL関数で求めると、すべて除外されて「合計=0」という結果になります。
Excel においては、小計(SUBTOTAL関数で求める)を含む合計を求める場合にも「SUBTOTALを使うのが鉄則」になっています。
返信する

コメントを投稿

パソコン」カテゴリの最新記事