タグ:Excelワザ ( 164 ) タグの人気記事

園児の満年齢を調べる

10月1日現在の園児の年齢を調べたい!
ということで…
b0186959_10205687.jpg

Datedif関数を使いましょう。
1.セル【F2】に10月1日の日付を入力しておきます。
2.セル【F4】に式
  =DATEDIF(E4,$F$2,"y")
  セル【G4】に式
  =DATEDIF(E4,$F$2,"ym")
  セル【H4】に式
  =DATEDIF(E4,$F$2,"md")
すると、10月1日現在の年齢が
◯歳◯ヶ月◯日と出ます。
b0186959_1025133.jpg

これが出来ると、
オートフィルタを使って、
3歳児だけ、4歳児だけとかを
抽出することが出来ます。
b0186959_1028413.jpg


Countif関数を使うと
セル【F24】の式→=COUNTIF($F$4:$F$23,E24)
セル【F25】の式→=COUNTIF($F$4:$F$23,E25)
セル【F26】の式→=COUNTIF($F$4:$F$23,E26)
b0186959_1551627.jpg

I様、これ使えそう?
[PR]

by school-pc | 2015-09-29 10:28 | Comments(0)

Aで始まる国は…

English Classでのゲームで
「A」で始まるCountry、
「B」で始まるCountryっていうのをして…
…じゃあ、調べてみましょう。

1.まずネットから国名一覧をコピー
  
b0186959_1042775.jpg

2.Wordにテキストのみ貼り付け。
 このサイトでコピーしたものは、
 日本語表記、( 、英語表記、国際電話の国番号、)
 の順になっています。
 
b0186959_1181740.jpg

 最終的には、Excelで処理したいので、
 Wordの置換機能で、処理しやすい形に直します。
3.「ア行[編集]」のような行を削除します。
 置換の画面で、検索文字列を「^?行[編集]^p
 と入力します。(「行[編集]」の部分はコピー&ペースト)
 (最初の「^?」の部分は、ア~ワの任意の1文字部分、
 最後の^pは段落記号、
 で、ア~ワ行[編集]段落記号の行を削除する。
 という意味になります。
b0186959_12163068.jpg

4.これをテキストファイル「国名一覧」として保存します。
b0186959_12173075.jpg

4.次にExcelからデータをインポートします。
 データ→外部ファイルの取り込み→テキストファイル
 テキストファイルのインポート→「国名一覧」選択
 →インポート
 →テキストファイルウィザード1/3→「カンマやタブによって……」チェック次へ→
b0186959_1233288.jpg

 →区切り文字を「カンマ」と「その他に(」を入力。→次へ
b0186959_12353433.jpg

→完了
b0186959_12405326.jpg

インポートできました。
b0186959_1242254.jpg

5.C列は、いらないので、削除。
6.1行目にタイトルを入力。
 A1:B1を選択し、Shift+Ctrl+↓を押すと、全データが
 選択出来るので、罫線を引きましょう。
b0186959_12472770.jpg

7.A列とB列を入れ替えます。
 B列を選択し、Shift+ドラッグで、移動挿入。
b0186959_12492740.jpg

8.A、B、Cの順に国名を並べ替えます。
 A列の任意のセルをアクティブ、昇順に並べ替えのボタンクリック。
 A~Z順に並び変わりました。
b0186959_12522121.jpg

9.次に関数で、頭文字A~Zで始まる国は何件か調べます。
10.COUNTIF関数を使います。
  D列に「A~Z」まで入力しておきます。
 E列に式「=COUNTIF(A:A,D2&"*")」
b0186959_12582217.jpg

 条件に(D列の文字にあと何か文字、という意味の*(ワイルドカード)
 を「&」でつなぎます。
 すると、「15」件と出ました。
 式をコピーします。
b0186959_1314481.jpg

「X」で始まる国はないですね(^_-)







 
[PR]

by school-pc | 2015-07-31 13:02 | Comments(0)

Excelワザ_方眼紙にする方法2

<方法1>
以前のEXCELのシートを方眼紙にする方法は
行の高さ
既定値は、13.5(ポイント)
18ピクセルに合わせて、
b0186959_167642.jpg

全セル選択し、
列の幅
(既定値は、8.38文字分)
これを「1.63(18ピクセル)」
に合わせていました。
b0186959_168103.jpg


b0186959_1611580.jpg


<方法2>
☆表示をページレイアウト表示にします。
行も列もcm(センチ)で
表示されるので、
単位が同じでわかりやすいです。
既定値
行は「0.48cm」
b0186959_16233968.jpg

列は「1.75cm」
b0186959_16381895.jpg

同じ単位なので、
合わせたいセンチにします。
全セル選択し、
例えば、「1cm」に設定。
すると、
b0186959_16301355.jpg

標準表示に切り替えると、
b0186959_16321920.jpg

1センチの方眼紙になりました。
0.5cmでこんな感じ。
b0186959_16352447.jpg

こちらの方がわかりやすいかも。
地図を描いたりするとき便利かな~~(^-^
[PR]

by school-pc | 2015-07-10 16:11 | Comments(0)

Excelワザ_写真付きメニュー

写真付き社員証の応用編。
時間帯ごとに3つのメニュー
(モーニング、ランチ、ディナー)
が切り替わり、
写真も同時に変わります!


☆6:00~12:00はモーニングメニュ―
b0186959_17141483.jpg

☆12:00~17:00はランチメニュー
b0186959_1723113.jpg

☆17:00~22:00まではディナーメニュ―
b0186959_17232366.jpg


手順
1.メニュー表シートにリスト作成
b0186959_12523738.jpg

b0186959_12524972.jpg

2.名前の定義
 メニュー一覧:=メニュー表!$B$15:$E$26
 モーニングメニュ― :=メニュー表!$B$15:$E$18
 ランチメニュー:=メニュー表!$B$19:$E$22
 ディナーメニュー:=メニュー表!$B$23:$E$26
 セット名:=メニュー表!$C$15:$C$26
 金額:=メニュー表!$D$15:$D$26
 写真:=メニュー表!$E$15:$E$26
 写真が入っているE列のそれぞれのセルに
 写真の内容のメニュー名を定義します。
 トーストセット:=メニュー表!$E$15
 ワッフルセット:=メニュー表!$E$16
 パンケーキセット:=メニュー表!$E$17
 サンドイッチセット:=メニュー表!$E$18
 ……のように12種類すべての名前を定義
b0186959_12542289.jpg

3.メニュ―シートの作成
 方眼紙にし、(列幅1.63)
 メニューを作成。
b0186959_1736941.jpg
 
4.式を立てていきます。
 ●時刻を出すセルAC5:
 =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
 ●メニュー種類を出すセルAP5:
 =IF(OR(AC5<メニュー表!$B$2,AC5>メニュー表!$B$5),"CLOSED",VLOOKUP(AC5,メニュー表!$B$2:$C$5,2,TRUE))
b0186959_18154991.jpg

 ●セット名を出すセル
F12:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E12,2,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
F13:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E13,2,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
F14:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E14,2,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
F15:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E15,2,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
 ●金額を出すセル
P12:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E12,3,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
P13:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E13,3,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
P14:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E14,3,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
P15:=INDEX((モーニングメニュ―,ランチメニュー,ディナーメニュー),$E15,3,MATCH($AP$5,メニュー表!$C$2:$C$4,0))
b0186959_18272731.jpg

 ●数をリストから選択出来るようにしておきます。
(MAX10)
セルAD11~AD15
セルAM11~AM15
セルH30、V30、AJ30、AX30
それぞれ、リスト入力できるようにします。
b0186959_18422617.jpg

 ●合計をsum、乗算で。
セルAW12:=SUM(AD11:AD15,AM11:AM15)
セルBA12:=IF(AW12="","",400*AW12)
セルL30:=IF(H30="","",P12*H30)
セルZ30:=IF(V30="","",P13*V30)
セルAN30:=IF(AJ30="","",P14*AJ30)
セルBB30:=IF(AX30="","",P15*AX30)
セルL35:=IF(AND(H30=0,V30=0,AJ30=0,AX30=0),"",SUM(L30,Z30,AN30,BB30))
セルL36:=IF(AW12=0,"",BA12)
セルL37:=SUM(L35:Q36)
5.式を立ててセット名が出るようになった
 セルF12に「セット1」という名前を定義。
 同様に、
 セルF13に「セット2」
 セルF14に「セット3」
 セルF15に「セット4」
 とそれぞれ名前を定義。
6.名前の定義のダイアログを出し、
 名前の定義の新規作成
 「セット1画像」という名前を定義し、
 セル参照範囲ではなく数式を入力。
 「=INDIRECT(セット1)」
 同様に、
 セット2画像:=INDIRECT(セット2)
 セット3画像:=INDIRECT(セット3)
 セット4画像:=INDIRECT(セット4)
7.メニュー表シートの1番目の写真
(トーストセット)をコピーします。
 セルF20、T20、AH20、AY20
 に貼り付けます。
8.F20に貼り付けた画像を選択した状態で、
 数式バーに
 「=セット1画像」と入力。
 同様に
 T20:=セット2画像
 AH20:=セット3画像
 AV20:=セット4画像
すると、セット名に対応した写真が表示されます。
9.あとは、ヘッダーに
 画像(クローバーのフレーム)
 を入れて、バランスを整えます。
b0186959_19461134.jpg

10.コンピュータの時計
 日付と時刻の設定で、
 12:00~の時刻や、
 17:00~の時刻にして、F9を押し、
 メニューが切り替わるか、確認してみましょう。
b0186959_1950590.jpg


 






 
[PR]

by school-pc | 2015-03-16 17:23 | Comments(0)

Excelワザ_写真付き社員証の作成Excel2013

コメントが付く記事を再upします。

写真付き社員証の作り方を
Excel2013で作ります。

1.社員名簿シートに
 A列社員コード
 B列社員名
 C列所属部署名
 D列写真
 のリストを作成。
b0186959_11332056.jpg

2.名前の定義
 社員が増減しても対応できるように
 OFFSET関数を使って、範囲を可変にします.
 社員コードは
 =OFFSET(社員名簿!$A$2,0,0,COUNTA(社員名簿!$A:$A)-1,1)
 社員名は
 =OFFSET(社員名簿!$B$2,0,0,COUNTA(社員名簿!$B:$B)-1,1)
 所属部署名は
 =OFFSET(社員名簿!$C$2,0,0,COUNTA(社員名簿!$C:$C)-1,1)
 写真は
 =OFFSET(社員名簿!$D$2,0,0,COUNTA(社員名簿!$D:$D)-1,1)
 社員名簿表はA列~D列を選択し定義しておきます。
 =社員名簿!$A:$D
 写真が入っているD列の、
 それぞれのセルに社員名を名前定義します。
 (今回は、10人分)
 セルD2はイチロー、D3は錦織圭…のように。
b0186959_14272994.jpg

3.写真証シートの作成
 Excelシートを方眼紙に
 全セル選択→列幅1.63に
 適当にデザインし、社員証を作成。
b0186959_12543874.jpg

4.社員コードがリストから選択して
 入るようにしておきます。
b0186959_12565819.jpg

 リストから選択するだけでOK。
b0186959_12583298.jpg

5.社員名と所属部署名は
 IF関数とVLOOKUP関数で
 =IF(H5="","",VLOOKUP(H5,社員名簿表,2,FALSE))
 =IF(H5="","",VLOOKUP(H5,社員名簿表,3,FALSE))
6.社員証の必要な分だけコピーします。
 とりあえず10名分作ってみます。
b0186959_13144088.jpg

7.リストからコードを選ぶと社員名が出るセルH7に
 「個別社員名01」という名前を定義。
 同様にコピーしたセルAB7に「個別社員名02」
 のようにコピーした分の個別社員名を定義。
b0186959_14391114.jpg

8.名前の定義の新規作成で
 「社員画像01」という名前を定義し、
 セル参照範囲でなく数式を入れます。
 =INDIRECT(個別社員名01)
b0186959_14361452.jpg

 同様に
 社員画像02は=INDIRECT(個別社員名02)
 のように10人分定義しておく。
9.社員名簿の1人目イチローの写真を
 コピーします。
10.社員証の写真の位置で貼り付け。
  コピーした分の社員証にも貼り付け。
 (イチローが10人分貼り付きました。)
11.社員証1人目の写真が選択されている状態で、
 数式バーに
 「=社員画像01」
 と入力。
 2人目の社員証のイチローの写真を
 選択した状態で、数式バーに
 「=社員画像02」と入力。
 同様にコピー分入力。
すると、社員と合致した写真になります。
b0186959_14132012.jpg

社員コードをリストから選択しなおすと、
写真も変わります。
b0186959_14162964.jpg

コメント欄に質問があって、
もう一度EXCEL2013バージョンで
作ってみましたが、うまくいきました。
2007でもできるはずです。
挑戦してみてください。
[PR]

by school-pc | 2015-03-09 14:16 | Comments(3)

Excelワザ_フォームの利用1

Excelの開発タブの中に「フォーム」という
便利な機能があるので、
利用方法の一つを紹介。
b0186959_10561126.jpg


例 半年間の部門別売上表があります。
これを月別に部門ごとの構成比をみるグラフを作りたい。
b0186959_10583770.jpg

1.開発タブ→挿入→リストボックス
2.リストボックス右クリック→コントロールの書式設定
 入力範囲に1月~6月のセル範囲
 リンクするセルにE10 単一選択
b0186959_113328.jpg

3.グラフ作成用のデータを抽出するために
  部門ごとの数値が出るようにINDEX関数を使って
  電 化:=INDEX($B$2:$F$7,$E$10,1)
  食料品:=INDEX($B$2:$F$7,$E$10,2)
  衣 料:=INDEX($B$2:$F$7,$E$10,3)
  家 具:=INDEX($B$2:$F$7,$E$10,4)
  雑 貨:=INDEX($B$2:$F$7,$E$10,5)
b0186959_11134558.jpg

4.さらに元の表のどのデータか、行に色が付くと
  わかりやすいので、条件付き書式を使って
  セル範囲A2~G7を選択し、
  条件付き書式
  
b0186959_11204976.jpg

  該当する月の行に色が付きます。CONCATENATEでもOK。
5.グラフは、C12~G13を選択し、円グラフに。
  凡例とタイトルを消して、
  データラベルの
  分類名とパーセントを表示します。
 A15には、=CONCATENATE(E10,"月")
        =E10&"月" でもOK・
 月が表示できるようにしておきます。
b0186959_11301861.jpg

6.月を選択しなおすと
b0186959_11312967.jpg

月別に該当データに色が付き、月別、部門別の
構成比グラフが、月を選択するだけで出来ます(^_-)-☆
[PR]

by school-pc | 2015-02-25 11:31 | Comments(0)

Excelワザ_名前の間にスペースを入れる

余分なスペースは
Trim関数で取れるけど
「井上」を「井 上」のように
一文字ずつスペースを入れる場合は……
T先生、これでどうでしょう?

b0186959_20353786.png


セルA2に名前
セルB2に式
=CONCATENATE(MID(A2,1,1)," ",MID(A2,2,1)," ",MID(A2,3,1)," ",
MID(A2,4,1)," ",MID(A2,5,1)," ",MID(A2,6,1)," ",MID(A2,7,1)," ",
MID(A2,8,1)," ",MID(A2,9,1)," ",MID(A2,10,1)," ")

苗字がとりあえず4文字の人まで実験完了。
[PR]

by school-pc | 2015-02-20 20:37 | Comments(0)

Excelワザ_計算結果を表示

b0186959_18114216.jpg

A1に計算式
(=は、いらない)
この計算結果を
B1に表示させます。

1.名前の定義
 数式→名前の定義
 「名前→計算結果表示」
 「参照範囲に
 =EVALUATE(A1)」と入力。→OK。
b0186959_1871715.jpg

2.表示させるB1に
 「=計算結果表示」と入力。
b0186959_18143747.jpg

3.すると、
b0186959_18154394.jpg

出ました!
4.式を変えてみると、
b0186959_18285132.jpg

※このファイルは「マクロ有効ブック」
にしないとだめです。
一般機能の関数の中には「EVALUATE]
という関数はありません。
[PR]

by school-pc | 2015-02-06 18:16 | Comments(0)

Excelワザ_重複データを除いたデータの数をカウント

さて、ウォーミングアップの次は、
Mさんから頂いた課題。
「データがどんどん増えていき、
重複する町内名が出てくるが、
重複データを除いた実際の町内の数はいくつか?」
という課題。
昨日のブログの可変する範囲名のつけ方。
一昨日のブログのSUMPRODUCT関数を使った
(Sumproductの補足も)
複数条件のデータの取り出し。
これらを使って、やってみましょう。

その前に、
いろいろなやり方を検証してみよう。
b0186959_9394871.jpg


準備として、
A列の番号は、自働的にふれるようにしておく。
セルA2に「1
セルA3には「=IF(B3="","",A2+1)
という式で、連番をつけておく。

簡単なものから
<方法1>
1.別シートに必要な列だけ(A列とB列)コピー。
2. データ→重複の削除
3. 町内名にチェック→OK
b0186959_9523980.jpg

b0186959_10393473.jpg

これで、重複を除いた町内は「7」ということが分かる。

<方法2>
ピボットテーブルを使う。
1.挿入→ピボットテーブル
→新しいシート
行エリアに町内名
値エリアに町内名(会員名でも可)
b0186959_1041746.jpg

行ラベルに表示された町内名を
COUNTA関数で出すと、「7」
データの総数は「25」ということが分かる。
それぞれの町内の数もわかる。

さて、方法1,2どちらも
データが変化しない場合です。
データが増えたり、減ったりする場合は、
いちいち範囲を選択しなおしたり、
データ更新をしないとだめです。
そこで、OFFSET関数。
そして、SUMPRODUCT関数。

<方法3>
1.町内名を名前定義。
b0186959_10162064.jpg

2.例、セルG2に式
=SUMPRODUCT(1/COUNTIF(町内名,町内名))
b0186959_1029796.jpg

この式で、町内名が増減しても
一意の町内の数が出てくれます。
例)№7~下のデータを削除すると、
b0186959_10434425.jpg


[PR]

by school-pc | 2015-01-30 09:39 | Comments(0)

Excelワザ_可変する範囲の名前の定義

過去ブログにも載せましたが…
名前の定義を
範囲が増減しても対応できる方法。
<例>
b0186959_11354571.jpg

店舗名が増えたり、減ったり
するたびに範囲を指定しなおすのは面倒。
で、範囲を可変にするために
Offset関数を使います。
1.名前の定義→新しい名前
 →「店舗名」
 →参照範囲に
 「=OFFSET($B$1,0,0,COUNTA($B:$B)-1,1)」と入力。
b0186959_166516.jpg

2.利用方法
 ①例えば、店舗名一覧リストから
 選択するだけで、店舗名が入るようにするには、
 店舗名を入力したい範囲を選択
 →データ→データの入力規則
 →入力値の種類をリストにし、
 →元の値に「=店舗名」とする。
 
b0186959_1695686.jpg

 ②入力したいセルの右に下向き三角がつくので、
  それをクリックすると、一覧から選択する
  だけで、店舗名が入る。
b0186959_16135330.jpg

 ③店舗が増えると、
  リストの店舗名も増えるし、
  減るとリストから削除される。

次の段階…
重複する町内の数を調べるには…
[PR]

by school-pc | 2015-01-29 11:36 | Comments(0)