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

「重複の削除」
リストの中で、重複するレコード(行)
を削除する機能。
で、これを使うと、
「担当者」だけ、「取引先」だけ
などを取り出すことができます。
例えば、売上表があって、
担当者の人数だけ、
何人か調べたいとき
ピボットテーブルを使うと、出ますが、
単純に担当者を抜き出したいとき、
「重複の削除」を使うと便利です。
こんな売上表から
b0186959_8541563.jpg

担当者は何人かなぁ…
1.まず担当者の列をコピーして、
 新しいシートに貼り付け、
b0186959_9413726.jpg

2.貼り付けた状態から
 データ→「重複の削除」をクリック。
b0186959_9425212.jpg

3.こんなメッセージが出て
b0186959_9435847.jpg

4.結果、102人の中から
 担当者は「9人」でした~~(^^♪
b0186959_9474057.jpg

同様に、取引先は
b0186959_950251.jpg

3件か~~。
ちょっと、便利に使えるんじゃない(^_-)-☆
[PR]
by school-pc | 2014-10-23 09:46 | Comments(0)

そうか、こんな手があったか~~
今更ですが^_^;
一度に複数の名前の定義が出来るワザを紹介。

1.名前の定義をしたい複数列の範囲を選択。
b0186959_19383719.jpg

2.数式タブ→定義された名前
 →選択範囲から作成
b0186959_19411331.jpg

3.以下に含まれる値から名前を作成
 →上端行にチェック→OK
b0186959_19414224.jpg

4.名前の管理をクリックして確認
→4つの範囲が名前定義されています!
b0186959_19432789.jpg

一度に複数の名前定義したいとき
便利かも(^_-)-☆
[PR]
by school-pc | 2014-07-23 23:36 | Comments(0)

2013から便利な機能「フラッシュフィル」
2014-6-20の記事

氏名に分けるだけならいいが、
こんな場合、
会社名、支店名、部署名
に分けたいとき、
支店や、部署がない
空白の場合は、
フラッシュフィルでは
うまく取り出すことができません。
b0186959_1432206.jpg


そこで、Excelには、
良い機能があります。
「区切り位置指定ウィザード」
1.区切りたいデータを選択、
2.データ→区切り位置
3.「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」
  にチェック→次へ
b0186959_14535828.jpg

4.「スペース」にチェック→次へ
b0186959_21162177.jpg

5.列のデータ形式「G標準」.
 表示先を表示したいセルを選択→完了
b0186959_14542698.jpg

6.「既にデータがありますが、置き換えますか?」
  と表示されるが、「OK」
b0186959_14501065.jpg

b0186959_1447414.jpg

データのないところは、空白になってますねぇ~
Goodな機能です。
[PR]
by school-pc | 2014-07-21 14:48 | Comments(0)

かなり、苦戦したので、備忘録
生徒さんからの質問で…
「日々入力するデータを1月ごとに、
事業所別に、該当するデータを集計したい!
ダイレクトに集計表に表示したい!」
マクロを使わずに…
フィルタも使わずに…
ピボットテーブルも使わずに…

簡単ではなかった…(^_^;)
ネットでググると、
同じような事をしたい人がかなりいて、
解答事例もたくさん有り。
その中に「imogasi方式」なるものを発見。
imogasi氏曰く
>「もう回答は出尽くしていますが、
いつも気になることを書きます。
この種の質問は週に3度ぐらい出ます。
タイプは「条件付き抜き出し問題」です。
しかし関数でやるのは、
1セル当たりでも非常に長い式で、
関数の5式以上の組み合わせになる式になります。
エクセルには検索に適した関数が作られてないことが原因です。
初心者には、自分で生み出せる式でなく、
式の意味もわからないと思う。
コピペして出来ましたと、それで良い人は使ったら良い。
しかし質問者の場合への修正も一苦労。
解法のタイプは
(1)A 作業列を使う
   B 使わない
(2)A 配列数式を使う
   B 使わない(通常の式)
(3)A ユーザー定義関数を使うー(一種のVBA)
   B 使わない。
(4)フィルタ
ですから、私は
(1)操作又は、フィルタ
を使うことをすすめます。
(2)VBA
(3)特別のソフト(フリーソフト)
(ただし見つけるのが難しい)
を考える人もあるでしょう。
私は長年「imogasi方式」と
いうのを書いてきました。
上記で言うと(1)のAにあたります
Googleで「imogasi方式」で照会すれば、
沢山の質問回答の例が出て、
私以外の、タイプの回答も毎回出ています。……」


ということが書いてあり、
色々ほかのやり方を試してみたが、…
データ量が増えると上手くいかなかったり…
かなり苦戦した(・へ・)

で、結局、
「imogasi方式の作業列を使ったやり方」
で作ることにした。

例題)
日々のデータを入力しているシートから
該当する講師ごとに、
レッスン日、コース名、レッスン名、時間の行
を取り出し、1か月分のレッスン時間,講師料を集計する。
講師がやめたり、入ったり、講師数の変動あり。
<入力データシート>A列~G列まで7列
b0186959_11474027.jpg

<講師別集計シート>
b0186959_1385890.jpg


1.準備として、
 ★範囲名に名前の定義
 範囲名を4つ作ります。
 まず、<講師名簿シート>
b0186959_12571647.jpg

 ここで、Excelワザ!
 講師の増減がある場合、
 いちいち講師名リストを選択するのは、面倒なので
 OFFSET関数を使います。
=OFFSET(基準セル、移動する行数、高さ(行数)、幅(列数)
=OFFSET(講師名簿!$B$2,0,0,COUNTA(講師名簿!$B:$B)-1,1)
b0186959_1343416.jpg
 
 講師名の入っているB2を基準セルとし、
 移動する行、列は、「0」
 高さはCOUNTA($B:$B)を指定します。
 これは、B列に何か値が入力されたセルの数を数え、
 見出し行分を含めないように-1引きます。
 幅は、講師列だけなので、「1」を指定します。
同様に、入力データシートの
 範囲名:講師名
 =OFFSET(入力データ!$D$2,0,0,COUNTA(入力データ!$D:$D)-1,1)
 範囲名:入力データ
 =OFFSET(入力データ!$A$2,0,0,COUNTA(入力データ!$A:$A)-1,7)
 範囲名:作業列
 =OFFSET(入力データ!$H$2,0,0,COUNTA(入力データ!$H:$H)-1,1)
これで、4つの範囲名作成。
2.講師別集計表のC1に
 リストから講師名が入るように入力規則を設定。
b0186959_13342317.jpg

 すると、リストから選択できるようになります。
b0186959_13525950.jpg

3.入力データシートの作業列,H2に
 =IF(D2=講師別集計!$C$1,COUNTIF($D$2:D2,講師別集計!$C$1),"")
 (条件と一致した行に連番を入れる式です。
  D2の講師名と講師別集計表のC2の講師名が同じなら、
  D2から数えて、何個目かを表示します。同じでないなら、空白)
 この式を下方向へコピーしておきます。
 すると、一致する講師の作業列に1,2,3、
 と連番が振られます。
b0186959_13505550.jpg

この連番を利用して、
4.講師別集計シートの
 セルB5~F5に式入力
(右方向に式をコピーし、
  INDEX関数の中の列番号を修正)
b0186959_14425845.jpg

6.B列は「m/d」(日付)に
 F列は「h:mm」(時刻)表示にする。
 時間の合計欄は「[h]:mm」にしておく。
出ました~~~!
b0186959_1422393.jpg


 
  
[PR]
by school-pc | 2014-07-07 11:48 | Comments(0)

Excel2013から追加された機能
「フラッシュフィル」
データベースとして
エクセルの表を使いたいとき
なかなか便利です。
例)
氏名を「姓」と「名」に分けたいとき
姓、名の列に1人分だけ入力、
b0186959_17193033.jpg

データ→フラッシュフィル
とするだけで、すべてのセルに入ってくれます。
b0186959_17214143.jpg

素晴らしい!!

で、これを2010までのバージョンで
するなら……
やはり、関数でしなければなりませんねぇ…
「姓」を取り出すセルには…
=LEFT(B3,FIND(" ",B3,1)-1)
(姓名の入っているセルの
左から(LEFT関数)
空白部分まで(Find関数で空白を見つけ)
空白分1文字引いた数
の文字数を取り出す。)
「名」を取り出すセルには…
=RIGHT(B3,LEN(B3)-FIND(" ",B3,1))
(姓名の入っているセルの
右から(Right関数)
全文字数(Len関数)から
空白までの文字数(Find関数)を引いた
数の文字数を取り出す。)
で、出来ます。
b0186959_1854225.jpg


バージョンによって、出来ることと
出来ないことがありまして…
良くなった点や、悪くなった点が
色々でてくるし…
そうそう、グラフも……続く…
[PR]
by school-pc | 2014-06-20 17:18 | Comments(0)

あまり使わないかもですが…
レーダーチャートのグラフは
ふつうはこんな感じ…
b0186959_1784760.jpg

これに、ドーナツグラフを
付け加えることにより
円形のレーダーチャートになる。
b0186959_17115150.jpg

[PR]
by school-pc | 2014-06-18 17:08 | Comments(0)

多角形-2

レーダーチャートを使うと
任意の多角形が出来ました。
(6月12日記事)

ちなみに…
360角形にすると…
ほぼ…円…(^_^;)
b0186959_1705495.jpg

[PR]
by school-pc | 2014-06-17 23:57 | Comments(0)

オートシェイプの基本図形の中には、
四角形、五角形、六角形、
七角形、八角形、
十角形、十二角形
はあります。
b0186959_1733379.jpg

で、九角形はない。
十一角形もない。

では、Excelで作りましょう!
まず九角形
EXCELのワークシートに
1.セルA1~A9に数値「1」
 を入れます。
b0186959_1782833.jpg

2.A1~A9を選択し、
 挿入→グラフ→レーダー→塗りつぶしレーダー
b0186959_1726556.jpg

3.系列1と値軸と項目ラベルを削除。
4.すると、頂点が9つある
 九角形ができました!
b0186959_17345582.jpg

色はお好みで、
グラデーションもできます(^_-)
グラフエリア、プロットエリアの
塗りつぶしをなしにすると、
九角形だけのように見えます。
b0186959_17413074.jpg

あとは、同じ要領で
十一角形は二行分増やして、
こんな感じ
b0186959_17433079.jpg

[PR]
by school-pc | 2014-06-12 17:26 | Comments(0)

You Tube見てて
ほっしゃんが
500円玉をコツコツ貯めてたら
6年間でなんと234万円にもなっていた…005.gif
というのを見て…

100万円貯めるには
1年間だと、毎日何円
貯めなければならないか?
と、ふと思ったので(^-^;

ゴールシークを使って出してみよう!
1.
b0186959_13361979.jpg

 スタート日を2014年6月1日にして
 1年後を 2015年6月1日にして
 セルC3に式 =D3*(B3-B2)を立てておく。
 (日付データもそのまま計算に使えるので。)
2.
b0186959_1340818.jpg

 
b0186959_13402345.jpg

3.
b0186959_13411698.jpg

 
b0186959_13412378.jpg

 収束値が出ました。というメッセージが出て、
4.「2739.729」という結果が出ました。
毎日2740円か~~ウーム(-_-;)
では、2年では…?
 
b0186959_1341301.jpg

5.
b0186959_1349299.jpg

 「1367.98」
100万円貯めるのに、1日1400円ずつ2年間、
これならできますか~~~?(^_^;)
[PR]
by school-pc | 2014-05-26 13:51 | Comments(0)

Int関数とMod関数を使って…
5000個のリンゴは
何コンテナ、何パック、何箱、何個
になるか…
生徒さんの中に
商品を納めるのに、
そんな計算がいる事が
あるらしいので…

INT関数:整数を返す関数
(正の数なら、ROUNDDOWNと
同じ結果が返るので、
桁数指定がいらないので
便利に使える。)
MOD関数:割り算の余りを返す。

この2つの関数を使うと、例えば
b0186959_11445366.jpg

金種ごとの枚数が出せます(^_-)-☆
この計算を使って…
では、5000個のリンゴを
何コンテナ何パック何箱何個で出すには…
b0186959_11463286.jpg

[PR]
by school-pc | 2014-03-24 11:44 | Comments(0)