茨木市ののパソコン教室です。南茨木駅から徒歩3分。
初心者OK。資格取得OK。持込みOK。託児OK。
何かと自由な教室です。



ぱそこんる〜む ブログです。
パソコン教室での日々の出来事や、Tips・解説や、お知らせなど、思いついたままつづっています。教室ホームページへはリンクからどうぞ。
こねこ時計


  毎月15日・末日お休みです。

Profile
Links
おすすめサイト

かわいいイラストのあるサイト
イラストわんパグ

最高に使いやすい家計簿のあるサイト
やさしい家計簿

キャンペーン中


Comment
Category
NewEntry
Archives
Other
Mobile
qrcode
エクセル家計簿ーサブカテゴリーの抽出表示

茨木市のパソコン教室 ”ぱそこんる〜む”おすすめのずぼら家計簿 

 

ランダムに入力するだけで集計からグラフまでお任せだけど、作成するのはそれほど難しくありません。

集計に便利なように、項目は入力規則を使ってリストに登録した項目をクリックで選ぶ方法をとっています。

 

右側に入力されているデーターが選択できるように設定。

 

生徒さんのご要望は「項目に対する内訳が絞り込まれて表示されるように」とのこと。

アクセスだとすぐ思いつくのですが、エクセルでできたっけ・・・?

 

グーグル先生が教えてくれました。

入力規則の参照先を名前の範囲にすればいい!

indirect(参照関数)を使います。

 

 



read more...
Excel comments(0)
グラフを2種類一度に作るマクロ

「マクロって何が便利なんですか?」

エクセル受講中の生徒さんからの質問です。

 

いや、すっごい便利ですよ!っと思うのですが

テキストで練習される課題は既定の範囲のデーターが削除されるマクロ

「選んでデリートするのとそれほど手間が変わらないように思うのですけど・・・」

同じ範囲を毎回選びなおす手間がないだけでも便利だとは思うのですが、

1回しか練習しないので同じ行為の繰り返しの手間が省ける実感や、

作業工程が少ないとマクロの便利さへの驚きが少ないですよね。

 

で、便利そうに見えるマクロを作ってみました。

サンプル用に集計表を作成

データーを入力して

グラフ作成ボタンを押すと

サイズと位置の整えた2種類のグラフができます。

コードはこんなの↓

-----------------------------

Sub グラフ作成()

' グラフを作る


'横棒グラフ

    ActiveSheet.ChartObjects.Add 0, 180, 407, 200  ’←グラフの位置とサイズを指定してます
    ActiveSheet.ChartObjects(1).Chart.ChartType = xlBarStacked  ’←グラフの種類
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Range("Sheet1!$B$5:$E$10")  ’←グラフのデータ範囲
   
'円グラフ    
    ActiveSheet.ChartObjects.Add 0, 400, 407, 300
    ActiveSheet.ChartObjects(2).Chart.ChartType = xlPie
    ActiveSheet.ChartObjects(2).Chart.SetSourceData Source:=Range("Sheet1!$B$6:$B$10,Sheet1!$F$6:$F$10")
    ActiveSheet.ChartObjects(2).Chart.HasLegend = False  ’←凡例の非表示
     With ActiveSheet.ChartObjects(2).Chart.SeriesCollection(1)  

        .HasDataLabels = True   ’←データラベルの表示
        .DataLabels.ShowPercentage = True
        .DataLabels.ShowCategoryName = True
        .DataLabels.ShowValue = False
        
    End With
   
End Sub

-------------------------------

 

もちろん、別の月のデーターを入力するために、クリアボタンも

 

-------------------------------
Sub クリア()

' データ消去


    ActiveSheet.ChartObjects(2).Activate
    ActiveSheet.ChartObjects(1).Activate
    Selection.Delete

    
    Range("C5:E10").Select
    Selection.ClearContents
   
  
End Sub

 

--------------------------

もとの空データに戻ります。

 

作成したのはグラフ作成だけのマクロですが、

元の売上データー一覧から

表を作成、

グラフを作った後、印刷プレビューまでマクロにすれば

むちゃくちゃ効率いいですよね。

 

繰り返し作業の多い仕事場ではすごく効率アップになるのですが、

ホームユーザーでは、

マクロ作ったのですごく便利になった!

ていう、事例が思いつきません。

 

ホームユーザーだって、こんな時に便利だよ。って事例ご存知の方!

ぜひ教えていただきたい!

 

 



Excel comments(0)
セル参照の条件式

Excelで集金管理の生徒さん

「任意の期間分で集計したいんです。」

 

はい!

いついつからいついつまでのデーターなら集計する。

集計に条件が付いてますよね。

SUMIFの出番です。

ですが今回、条件が複数ですよね。

SUMIFSの登場です。

 

SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, 条件範囲 2, 条件 2, ...)

[合計対象範囲]は合計したいデータのある範囲

[条件範囲1]は条件をつけたいデータのある範囲

[条件1]は合計したいデータの条件

2つ目、3つ目の条件は一つ目に沿って同じように指定していきます。

最大127個まで条件設定できます。

 

生徒さんの場合

[合計対象範囲]は集金データのある範囲

[条件範囲1]は集金日データのある範囲

[条件1]は集計したい最初の日以降

[条件範囲2]は集金日データのある範囲

[条件2]は集計したい最後の日以前

 

たとえば7月2日から7月9日までという条件なら

条件1は ">=2016/7/2"

条件2は "<=2016/7/9"

 

でもそうすると、日にちを変えるたびに式を書き直す必要が出てきます。

それは大変。

最初の日と最後の日を入力するセルを作っておきましょう。

そしてそのセルを参照させば日にちを入力しなおすだけです。

 

仮に最初の日をA2に、最後の日をB2に入れたとします。

その場合

条件1は ">="&A2
条件2は "<="&B2

”の位置に注意して、&を忘れずに。

 

うん!便利ですね。

 

 

 

 



Excel comments(0)
条件に合う数値を合計する・・・Excel SUMIF関数
Excelを受講中の生徒さん

お仕事の集金表を作成されていて
A4サイズの用紙に月別に印刷して
バインダーに挟んでチェックする目的。
現金集金?
いえいえ、銀行振り込み
そして、集計もするということ。
なぜ、紙ベースで管理を?

もちろん計算得意なExcelさんに自動集計してもらうことにしました。
1シートで12か月、1年分を管理。シートコピーして何年分も一気に作ることができます。

入金があればその日付を記入して、日付を入力すればその分だけ合計する。
そう、SUMIF関数です。

SUMIF(範囲,検索条件,合計範囲)
[範囲]は条件に合うデーターを探す範囲
[検索条件]は文字通り、合計したいデータの条件
[合計範囲]は合計したいデータのある範囲

生徒さんの場合
[範囲]は集金日を入れるセル範囲
[検索条件]は集金日が入力されていること
[合計範囲]は集金金額の入力されているセル範囲

検索条件が○などのチェックマークなら
検索条件のところにそのマークを入れる
もしくは空白なら ”” を入力するのですが
今回の場合、入るデータはばらばら
だから、空白でなければという条件に。

空白の表記は
””
空白でないの表記
”<>”

備忘録も兼ねて・・・




 


Excel comments(0)
Excelで分数を入力するには
はい、茨木市のパソコン教室「ぱそこんる〜む」お悩み相談室です。

「Excelで分数を入力して計算をしたいのに、日付表記になってしまうんです〜」
ですよね。
Excelはハイフンやスラッシュで区切られた数値はまず日付ではないかと思います。
なので12分の1にしたくても「1/12」と入力すると「1月12日」と表記されるわけです。
もちろん日付ではありえない数値、「32/153」とかのときはそのまま表記されます。

分数表記を計算対象にしなくてもいいときはセルの書式設定を文字列にしてから入力すればOK。
文字列表示 ←計算対象になってない証拠に左に寄ってますね。

計算対象にするときはセルの書式設定画面を出して、分数表記に設定してください。
分数表示

入力するときは例えば1/2なら、0.5でもOK
もちろん計算式が入っていても計算結果が分数表記で表示されます。
分母の桁数に合わせて設定してくださいね。

はい!解決です!手



 


Excel comments(0)
Excel 便利小技!
日経PC21を読んでいて
超便利なセル移動の方法に思わず声が!

今まで入力セルの一番端には
コントロールキーを利用してセル移動していたのです

Ctrl+Home A1セル
Ctrl+End 最終セル
Ctrl+矢印キーでデーターの入力されてるそれぞれ端っこのセル
その
Ctrl+矢印キーと同じことがマウスでできるなんて!

入力データーのセルの端っこをダブルクリックで飛んで行けるんです!!


ポインタ(白い矢印)の先が4方向に向いた矢印になっているところに注意して。
上の図だと
セルの下部をダブルクリックでB5
セルの右部をダブルクリックでC3  にジャンプです。

あ〜、知らなくて損してた〜。
大きい表ならとっても便利
おすすめです。


Excel comments(0)
住所録づくりに役立つ関数 EXCEL
Excel関数学習中の生徒さん
文字列を結合するCONCATENATE関数で県名とそれ以降の住所を結合。
WEBからの入力データーを処理するときなんかに便利そうです。

実際手入力で名簿を作るときには郵便番号で住所変換行うことが多いと思います。
でも、郵便物出す時に都道府県名入れたくない場合もありますよね。
そんな時、郵便番号で住所変換した住所を使って都道府県とそれ以降に分けてみましょう。

B列に郵便番号を入力して変換で住所にします。

A列ではPHONETIC関数で郵便番号を表示

C列ではIF関数を使って”県”の場所によって取り出す文字数を変えています。
B列の文字列の4文字目の一文字が”県”だった場合、左から4文字、
そうでない場合は左から3文字取り出す。
神奈川県・和歌山県・鹿児島県の3県以外は都道府県みな3文字なんです。
ちなみに、大阪都に変わったとしても使えます(^_-)-☆

D列ではB列文字列の文字数を数え、右から取り出す文字数を決めています。
B列の文字数を数え、C列の文字数分を引いた文字数分を右から取り出す。

いかがですか?


Excel comments(0)
Excel 和暦の二けた表示にしたい時には・・・
Excelで日にちを入力(4/7)すると4月7日と表示されますよね。
何年まで表示したい時は
セルの書式設定→表示形式→日付→種類 年の入った書式を選びます。
西暦になってますがカレンダーの種類のところで和暦を選べば年号で表示されます。
でも桁数が違うと、名簿作成したときに文字数が一緒にならないんです。
凸凹しちゃっていやだなーと思ったとき
ユーザー定義で1と桁の年も二けた表示に設定しましょう。

月の二けた表示(07月)は mm
日の二けた表示(08日)は dd
年号の二けた表示は
漢字表記だと(昭和08年)gggee
アルファベット表記なら(S08)gee
eを二つ続けてくださいね

設定画面はこんなかんじ↓Excel 和暦の二けた表示

これで字面がそろってすっきりです


Excel comments(0)
1/1PAGES


(C) 2017 ブログ JUGEM Some Rights Reserved.