ワード・エクセル・PDF
2013年12月10日
Excel ドロップダウンリストの作り方−直接入力
「エクセルで、ドロップダウンリストを作成して、リストから選択してデータ入力できるようにしたいのですが」
といった要望を、ちょっとした業務アプリを作りはじめた方からよくいただきます。
ちなみに「ドロップダウンリスト」ではなく、「プルダウンリスト」とおっしゃる方もいらっしゃいますが、「pull」(引く)という動作はしていないので、私は「ドロップダウン」のほうが適切な呼び方だと考えています。
Excelでドロップダウンリストからデータ入力できるようにするには、いくつかのやり方がありますが、入力規則機能を使うのが一番簡単でしょう。
▼操作手順:入力規則機能でドロップダウンリストを作成する
※「東京」「大阪」「名古屋」の3項目がリストに表示されるようにする例
メニュー[データ]−[入力規則]をクリック
↓
[データの入力規則]ダイアログ−[設定]タブをクリック
↓
[条件の設定]欄
−[入力値の種類]コンボボックスから「リスト」を選択
↓
[元の値]欄にリスト表示させたい
「東京,大阪,名古屋」と入力
↓
[データの入力規則]ダイアログ−[OK]ボタンをクリック
上記の設定を行ったセルをアクティブにすると[▼]ボタンが表示され、クリックすると「東京」「大阪」「名古屋」と表示され、選択することでデータが入力できるようになります。
[元の値]欄には、リスト表示させたい値を「,」(カンマ)で区切って入力するわけですが、必ず半角カンマで区切ってください。
「東京,大阪,名古屋」と全角カンマで区切って入力すると、「東京」「大阪」「名古屋」と個別の値としてではなく「東京,大阪,名古屋」という一つの値としてドロップダウンリストに表示されてしまいます。
Excelにある程度慣れた方だと、こういった入力のときに文字列は「"」(ダブルクォーテーション)でくくるものだと考え、「"東京","大阪","名古屋"」と入力してしまうこともあるようですが、「"」は不要です。「"」でくくった場合、「"東京"」「"大阪"」「"名古屋"」と「"」もデータの一部として表示されてしまいます。
▼サンプルファイル(001190.xls 20KByte)ダウンロード
上記の設定をしたセルではリストにない値を入力できなくなりますが、リスト以外の値も入力できるようにする方法については、「入力規則設定時、リスト以外の値も入力可能にしたい」というネタをご参照ください。
2013年11月14日
エクセル入力「10ワザ」 仕事の時間が短くなる!
集計表や顧客リストをエクセル 2007 で管理する際には、ひたすらデータを入力することになるでしょう。そんなときに知っておくと便利な「入力ワザ」を 5 つピックアップ! あっという間に「入力達人」になれること間違いなしです!
初期設定では、データを入力して Enter キーを押すと、カーソルは下に進みます。つまり、下方向にどんどんデータを入力しやすいようになっているのです。ところが、大きな表にデータを入力する際には、横 (右) 方向に次のデータを入力していきたいことがあるでしょう。そんなときには、データ入力後に Enter キーではなく、Tab キーを押すとカーソルが右に進んで便利です。
データ入力後に Enter キーを押すと、カーソルは下に進みます。ところが、入力後に Tabキーを押すとカーソルが右に進みます。
ポイント
標準状態の移動方向を決める
Enter キーを押した後にカーソルを動かす方向をユーザー指定で決定することもできます。常に右方向に進めた方が便利な方は、ここで設定しておきましょう。
[Office ボタン] をクリックし、[Excel のオプション] をクリックします。
ダイアログが開いたら、左ペインで [詳細設定] をクリックし、[編集設定] の [Enter キーを押した後にセルを移動する方向] で 4 方向から好みの進み方を選びます (この際に、[Enter キーを押した後にセルを移動する]にチェックが入っていることをご確認ください)。
例えばこの表では、1 月のデータを上から入力していったら、次は 2 月の 1 段目を入力することでしょう。しかし、普通に作業するとカーソルは入力の必要がない下に進んでしまいます。こんな時にはあらかじめ表の範囲を指定しておくだけで「1 月の終端」の次に「2 月の先頭」へとカーソルが移動するのです!
普通に作業していると、入力後に Enter キーを押すとカーソルは下に進みます。
あらかじめ入力範囲を指定しておくと、表の下端の次に右上のセルへとカーソルが自動でジャンプ! 矢印キーやマウスを使わずにどんどん入力できます。この作業をする際には、矢印キーを使うと範囲指定がクリアになりますから気をつけてください。
Ctrl + スペースを押すとカーソルのある列、Shift + スペースでカーソルのある行が選択できます。その状態で入力していくと、カーソルは選択した方向に常に移動します。連続してデータを入力する際には、抜群にスピードアップできます。
Shift + スペースでカーソルのある行が選択できました。カーソルの移動方向が下になっていても横方向に入力できます。同じように Ctrl + スペースでは列を選択できます。
入力を終えたあと、よくする作業として書式設定があります。ここで、Ctrl + 1 キーを押すと [セルの書式設定] ダイアログが開きます。右クリックよりも早く希望の書式に設定できます。
ポイント
まずは、入力作業をした後に表の整形を行うことをお勧めします。全体感が見れたなかでのバランスをとれた書式設定ができます。
入力作業を終え、Ctrl + 1 を押すと [セルの書式設定] ダイアログが開き、素早く書式設定ができます。
この表のように、所々に空白のセルがあって穴を埋めるように入力するのは、セルの選択やカーソルの移動だけでも大変に面倒です。そこで、自動的に空白セルだけを選択した状態にしておき、次々に入力できるテクを身につけておくのがおすすめです。
空白が所々にある表です。空白のデータを穴埋めのように入力します。
表のデータを入力する範囲をドラッグで選択しておき、Ctrl + G を押します。[ジャンプ] ダイアログが開くので [セル選択] をクリックします。
[選択オプション] ダイアログで [空白セル] にチェックを付けます。
空白のセルだけが選択された状態になりました。この状態でデータを入力して、Enter を押すと、カーソルが次の空白セルへとジャンプしていきます。
データの入力ワザの次はシートを使いこなしてみましょう。面倒なシートの複製などが素早くできます。
複数のシートを利用している際に役立つ機能を 2 つ紹介します。特にシートを移動するショートカットは便利なことこの上ありません。また、シートの複製は前の月のデータを使い回すようなときには超手抜きができます。
シートを移動するには、Ctrl + Page Up を押すと、いま選択しているタブの左側にあるシート、Ctrl + Page Down で右側のシートを選択できます。
シートを丸ごと複製すると便利です。先月作成した集計表を今月も利用するなど、表を丸ごと使い回したいときに役立つ機能です。シートのタブを右クリックしても良いのですが、Ctrl キーを押しながらタブをドラッグするだけで、複製できます。この方法なら、あっという間に複製できます。
シートを複製するには、Ctrl キーを押しながらタブをドラッグするだけです。
シートが増えても楽々作業ができるようになりました。次は、オートフィルを使いこなして、連続しているデータを簡単に入力する方法を説明します。
オートフィルを使うと、日付や連番などが簡単に入力できます。例えば、1 ヶ月分の日付を入力するだけでも、手作業では大変な負担です。オートフィルを使いこなすと、圧倒的に時間が節約できるのです。
1 週間の曜日や 1 ヶ月の日付で表の見出しを作る際には、まず 1 日目を入力します。あとは、ドラッグするだけで希望の見出しが完成します。
最初に連続するデータの 1 つめを入力し、セルの右下を見出しに必要なセルまでドラッグします。
連続するデータが自動的に入力できました。
ほかに、曜日でも同じように入力できます。
日付などは自動的に連続したデータになります。ところが数字の連番を入力するときには、最初の数字をドラッグすると単なるコピーになってしまいます。そんなときには、コピー後に [オートフィル オプション] で連番にすることができます。
1 から連番を入力しようと考えて、最初に 1 を入力してドラッグしました。ところがコピーになってしまいます。こんな時には、[オートフィル オプション] のプルダウン ボタンをクリックして [連番] を選びます。
連番が入力できました。
住所録や名簿では、人の名前を入力するときには、漢字を入力しますから IME をオンにします。ところが、数字だけを入力する郵便番号では IME はオフの方が便利です。このような時には、[入力規則] を利用すると、IME のオン・オフなどを自動で切り替えられます。大きな表ほど、設定しておく価値ありです!
ここでは、氏名のセルで IME をオンにする設定を進めていきます。
(3-1) IME をコントロールしたいセルを選択します。
(3-2) [データ] 窶錀 [データ ツール] 窶錀 [データの入力規則] 窶錀 [データの入力規則] をクリックします。
[データの入力規則] ダイアログが開きます。
(3-3) [日本語入力] タブをクリックします。
(3-4) [日本語入力] のプルダウンで [オン] を選びます。
これで、入力の際、自動的に IME がオンになります。
入力規則で日本語入力をオフにした出席番号のセルでは、IME がオフになり、氏名のセルではオンになっていることがわかります。いちいち切り替える手間が省略できるのです。
2013年11月12日
Excelでも計算間違いをすることがあります。
実は、Excelは小数点を含む数値の計算が苦手なんです。
だからExcelで小数を含む計算をする場合には、気を付けてください
思わぬ結果になることがあります・・・^_^;
なぜ、そのようなことが起こるのでしょう???
ひとことで説明すると、コンピュータの計算は、2進数が基本となっているからです。
たとえば、1/10 という分数は、10 進法では 0.1 と表現できるのに対し、
同じ数を 2 進数で表現すると、無限に繰り返される循環 2 進数になるのです。
これを決められた領域内で表現するためには、
どこかで数字を丸めることになるのですが
そこで、誤差が発生するのです。
とにかく、小数点以下の数、時間のシリアル値、パーセント、15桁を超える数を扱うときは
気を付けてくださいね。
誤差を避けるには・・・
誤差が発生することはMicrosoftも認識しているみたいです。
Excel で浮動小数点演算の結果が正しくない場合がある
そして、誤差対策は、
浮動演算での丸目のエラーを修正する方法
で、2つの方法を上げています。
でも、上記のサイトの説明はちょっとわかりにくいですね。
実際に、誤差を避けようとするには
- 整数化
- 微小値を使う
- ROUND 関数で数をそろえる
日経PC21のサイトに詳しい説明が掲載されていますので
参考にしてみてくださいね。
“達人”芳坂和行氏に学ぶ、エクセル(Excel)「演算誤差」対策講座
2013年08月31日
Wordワードでもグラフを作れる
フリーソフトの「Microsoft Mathematics Add-in 2013」をインストールすれば、ワードWordでも簡単にグラフを作れる。
マイクロソフト社は2013年8月27日、「Microsoft Word(以下、Word)」「Microsoft OneNote(以下、OneNote)」に数式の計算機能などを追加するアドイン「Microsoft Mathematics Add-in 2013」を公開した。Windows Vista/Server 2008/7/Server 2008 R2/8/Server 2012上の「Word」2007/2010/2013および「OneNote」2010/2013に対応するフリーソフトで、現在同社のダウンロードセンターからダウンロードできる。なお、動作には.NET Framework 4が必要。
「Microsoft Mathematics Add-in 2013」は、「Word」や「OneNote」へ高度な数式の計算機能やグラフの作成機能を追加できるアドイン。インストールすると、「Word」や「OneNote」へ[数学]リボンが追加され、数式の入力や計算、グラフの作成が可能になる。
[数学]リボンからは、累乗根や対数、三角関数、微分積分、行列演算、複素数、統計関数、等式・不等式の計算に加え、因数分解や代数式の簡約・展開も可能。また、デカルト座標・極座標の2次元グラフや、デカルト座標・円柱座標・球面座標での3次元グラフの作成もできる。
ダウンロードファイル名:edumathaddin_ja-jp_32bit.exe
インストール方法:http://www.microsoft.com/ja-jp/download/confirmation.aspx?id=36777
2013年08月07日
PDFを右クリックメニューから編集や変換が行える「Debenu PDF Tools Pro」
「Debenu PDF Tools Pro」は、PDFファイルの右クリックメニューからさまざまな編集や変換が行えるソフト。64bit版を含むWindows XP/Vista/7/8に対応しており、作者のWebサイトからダウンロードできる。59米ドルのシェアウェアだが19日現在、Facebookで“いいね!”をすることで無償で利用できるキャンペーンが開催されている。
本ソフトが備える主な機能は以下の通りで、インストールするとファイルの右クリックメニューに追加される[Debenu PDF Tools Pro]サブメニューから項目を選択することで、さまざまな編集や変換が行える仕組みとなってる。
・PDFファイルの表示・印刷
・プロパティ情報の編集
・パスワードの付加・除去
・複数PDFファイルの結合
・PDFファイルの分割
・PDFファイルから画像・画像ファイルからPDFファイルへの変換
・PDFファイルからTXTファイルへの変換
・PDFファイルからの画像やテキストの抽出
・ページの切り抜き・回転・挿入・削除
・PDFファイルへのファイルの添付や、添付されたファイルの抽出・削除
・埋め込み画像の抽出
・埋め込まれているフォントの名称を表示
・ブックマークの抽出・削除
・フォームデータなどの抽出
このほか、JavaScriptの付加・除去・編集機能やデジタル署名の付加機能なども備える。
無償で利用するには、まずキャンペーンページで名前やメールアドレスを登録し、メール経由でインストーラーのアーカイブをダウンロードする。次に、本ソフトをインストールして[Debenu PDF Tools Pro]サブメニューの[Application Info]-[Register License Key]項目から、名前とインストーラーと同じフォルダにある“debenu_pdf_tools_pro_license_key.txt”に載されているライセンスキーを入力すればよい。
2013年07月10日
Excelエクセルデータ検索・抽出らくらく フリーソフト
・エクセルが不得意な方
・就職先を探しているがパソコン(エクセル)のスキルに自信のない方
・エクセルをマスターしたいけど何から手をつけたらいいか分からない方
・エクセルのスキルが低いのでどうにかしたいと考えている方
・単純な作業や面倒な作業の時間を減らしてもっと効率的に仕事をこなしたい方
・「Excelが使えるかた」という条件がある求人に自信を持って応募したい方
・エクセル勉強が嫌いな方、Excelに拒否反応がある方
・Excelの膨大な作業に追われてうんざりしている方
職場では「仕事が早い、仕事がよくできる」と評価されたいあなた、エクセルを学習するために、高いお金を払ってパソコン教室なんか行かなくても済むのです。
パソコン使ったデスクワークは正確にかつスピーディー、効率的にこなすにはExcelエクセルの十分なスキルが必要なわけですが、一方、そのスキルを高めるのにもあんまり時間もお金もかけてられないわけです。
「今まで何冊もエクセル関係の本を購入して、パソコン教室にも通ったのに、どうしてもエクセルと仲良くなれない」
「本をいくら読んでも仕事で困ってるエクセルの使い方が分からない」「周りの方にエクセルの質問もできなくてひそかに悩んでいる」とこのようなお悩みの声をよく聞きました。
本やパソコン教室が悪いわけではないですが、万人向けにエクセルの機能全般を広く浅く網羅して取り扱っているからです。
本当に仕事に必要なことは何かが各職場の実情に沿った解説はないからです。
上記のような方々のために、汎用型フリーエクセルソフトを用意できました。
●Excelスタンプ
電子印鑑、PDF作成、カレンダー入力、シート間比較、パスワード解除など可能にするエクセルソフト
●Excel使い方上手
定型句をワンクリックで入力、カレンダー入力、電子押印、PDFファイル作成、パスワード解除が簡単にできるソフト
●エクセル見積作成らくらく
エクセルでお見積書・請求書・納品書・受領書・注文請書を簡単に作成できるExcelソフト
●エクセルパスワード瞬時解除ForExcel2007/2010/2013
エクセルシート(非表示を含む)及びワークブックパスワード紛失時の瞬時解除できるアドインソフト
●Excelデータ検索・抽出らくらく
フィルタオプションの設定ではオートフィルタでできない詳細な検索が可能になります。
複数条件によるデータ抽出し、別シートへの転記できるソフト。
下記は「Excelデータ検索・抽出らくらく」 一部画面です。ぜひご利用してみたらいかかでしょうか。
◆抽出条件の入力方法◆ | ||||||
抽出条件は、下記のように入力できます。抽出条件は、複数のセルに入 | ||||||
力するとAND(かつ)という関係になります。 | ||||||
データベースのデータには2つの種類があります。文字データと数値データです。 | ||||||
文字データでは、含むか、一致するか、一致しないかの3つの選択肢しかありません。 | ||||||
数値データでは、+、=、>、<、>=、<=、*、/などの演算子の使用ができます。 | ||||||
データベースの項目1つ1つが、数値項目なのか文字項目なのかを把握して、その条件に合う | ||||||
設定をしていくことです。 | ||||||
条件は,以下の例では先頭が田中で始まるデータで、数量が1000個以上2000個以下のものを探し出す設定です。 | ||||||
NO | 日付 | 担当者 | 送信先 | 商品 | 数量 | 数量 |
田中* | >1000 | <2000 | ||||
◆文字データの場合 | ||||||
* :任意の数の文字(例:*日本* →「日本」を含むデータ) | ||||||
? :任意の1文字 (例:日本?? →「日本○○」というデータ) | ||||||
<>:以外 (例:<>日本 →「日本」以外のデータ) | ||||||
= :完全一致 (例:="=日本" →「日本」というデータ) | ||||||
(「日本」は「日本*」と同じ意味になります) | ||||||
◆数値や日付データの場合 | ||||||
> :より大(例:>1000 → 1000より大きなデータ) | ||||||
< :より小(例:<2005/9/25 → 2005年9月25日より前のデータ) | ||||||
>=:以上 (例:>=<2005/9/25 → 2005年9月25日から後のデータ) | ||||||
<=:以下 (例:<=1000 → 1000以下のデータ) | ||||||
<>:以外 (例:<>1000 → 1000以外のデータ) | ||||||
皆の仕事の効率を上げることが出来ればと願っております。
ダウンロードは
2013年07月08日
【Acrobat XI】 ビジネス文書に「社外秘」「禁複写」などのロゴを入れて利用を制限するには
そんなときは、Adobe Acrobat XI Proの透かし機能を使うと便利だ。方法は[表示]→[ツール]→[ページ]でツールパネルウィンドウを開き、メニューにある[透かし]から[透かしを追加]を選択する。設定画面が表示されるので、表示したいテキスト、例えば「社外秘」と入力したのちにフォントの種類やサイズ、角度、不透明度、ページの前面/背面のいずれに配置するかなどを設定してOKをクリックする。これにより、元の文書に透かしのテキストが追加された状態になるはずだ。
ただしそのままの状態では、PDFをもらった相手がAcrobatなどの編集ソフトを所有していた場合、簡単に透かしを削除できてしまう。そのため、文書のプロパティで編集時にパスワードが必要なようにセキュリティの設定を変更しておく必要がある。これにより、パスワードを入手しない限り透かしが削除できなくなる。
ちなみに透かしはテキストだけではなく、ビットマップやJPGなどの画像、さらにはほかのPDFファイルを挿入もできる。企業のロゴを入れるなどの応用が可能なので、自社で定番のフォーマットを定めておくとよいだろう。
2013年07月07日
Excel(エクセル):複数条件によるデータ抽出 マクロ
次のデータ表を使って説明します。
- 1行に1データとなるようにする
- データの抽出は行単位で行われるので、同じデータ群が複数行にわたっていては不都合です。
- データの先頭行に項目行(列見出し)を作成する
- データリストの先頭行が項目行と認識されます。下図では2行目の部分。
- 列見出しは重複しない固有のものにする
- 例えば、担当者が複数の場合、列見出しは担当者1、担当者2のように区別できるようにします。
B | C | D | E | F | G | H | |
2 | 伝票№ | 日付 | 担当者 | 型番 | 単価 | 数量 | 売上金額 |
3 | 1001 | 2006/1/1 | 岡田 | A-001B | 5,000 | 15 | 75,000 |
4 | 1002 | 2006/1/1 | 上村 | A-001W | 5,000 | 22 | 110,000 |
5 | 1003 | 2006/1/1 | 相沢 | C-105W | 8,000 | 14 | 112,000 |
6 | 1004 | 2006/2/1 | 井上 | B-022B | 6,000 | 24 | 144,000 |
7 | 1005 | 2006/2/1 | 相沢 | A-001B | 5,000 | 15 | 75,000 |
8 | 1006 | 2006/2/1 | 岡田 | A-001W | 5,000 | 12 | 60,000 |
9 | 1007 | 2006/3/1 | 上村 | B-022B | 6,000 | 13 | 78,000 |
10 | 1008 | 2006/3/1 | 岡田 | C-105B | 8,000 | 12 | 96,000 |
11 | 1009 | 2006/3/1 | 相沢 | A-001W | 5,000 | 16 | 80,000 |
12 | 1010 | 2006/4/1 | 上村 | B-033W | 7,000 | 14 | 98,000 |
13 | 1011 | 2006/4/1 | 相沢 | C-105B | 8,000 | 24 | 192,000 |
14 | 1012 | 2006/4/1 | 岡田 | B-033W | 7,000 | 22 | 154,000 |
15 | 1013 | 2006/5/1 | 井上 | A-001B | 5,000 | 13 | 65,000 |
16 | 1014 | 2006/5/1 | 上村 | C-105W | 8,000 | 23 | 184,000 |
17 | 1015 | 2006/5/1 | 井上 | B-033W | 7,000 | 14 | 98,000 |
18 | 1016 | 2006/6/1 | 岡田 | C-105B | 8,000 | 18 | 144,000 |
19 | 1017 | 2006/6/1 | 相沢 | B-022B | 6,000 | 19 | 114,000 |
フィルタオプションの設定(詳細な条件設定での抽出) Topへ
- オートフィルタの条件設定では2条件までです。それ以上の条件のときはフィルタオプションの設定が使えます。
- 抽出したデータを他のセルやシートに書き出す場合にも便利です。
(別シートへ書き出す時は、別シートがアクティブな状態で実行しなければなりません)
一つの条件で抽出する
- 【問題】データ表から「担当者」が『井上』のデータをL2セル以降に抽出しなさい。
- 【解答例】
- データ範囲の外側に抽出条件を入力します。
- J2セルに列見出し「担当者」を入力します。
J3セルに「井上」と条件を入力します。
- Excel2002以外のバージョンでは「="=井上"」と入力すると完全一致での検索条件になります。
下図はExcel2007での例です。
- Excel2002以外のバージョンでは「="=井上"」と入力すると完全一致での検索条件になります。
- J2セルに列見出し「担当者」を入力します。
- メニューバーの【データ】→【フィルタ】→【フィルタオプションの設定】を選択します。
- リスト範囲にデータ表のセル範囲 『B2:H19』
検索条件範囲に『J2:J4』 を入れます。
指定した範囲にチェックを入れて、抽出範囲に『L2』とします。 - (ダイアログでは絶対参照になります)
- リスト範囲にデータ表のセル範囲 『B2:H19』
- データが抽出されました。
- データ範囲の外側に抽出条件を入力します。
複数条件で抽出する [AND条件,OR条件の書き方]
- 検索条件は同一行はAND条件での検索になります。
- 担当者が「岡田」かつ 売上金額が10万以上のデータを抽出したい場合
- 下のように、同一行に条件を並べます。
- 異なる行とはOR条件での検索になります。
- 担当者が「岡田」 かつ 売上金額が10万以上
または 「井上」 かつ 売上金額が10万以上 のデータを抽出したい場合- ANDとORを使って書きなおすと
担当者が「岡田」 AND 売上金額が10万以上
OR
担当者が「井上」 AND 売上金額が10万以上
- ANDとORを使って書きなおすと
- 下のように、別の行に条件を並べます。
- 担当者が「岡田」 かつ 売上金額が10万以上
期間の条件の書き方
- 【問題】データ表から「日付」が2月のデータをL2セル以降に抽出しなさい。
- 【解答例】
- 2月とはデータの値が日付になっていますので、2006/2/1以上 かつ 2006/3/1未満とすればよいでしょう。
- この条件はANDになりますので、同じ行に条件を書きます。
同じ列見出しでの条件なので2列に同じ見出し(ここでは「日付」)を並べます。- J2:K2セルに列見出し「日付」と入力。
J3セルに「>=2006/2/1」 K3セルに「<2006/3/1」と入力します。
- J2:K2セルに列見出し「日付」と入力。
- 細かいことですが、2006/2/28以下としないのは日付のシリアル値では2006/2/28は午前0時までですので、
もし2006/2/28 9:00というようなデータがあったら、これらのデータは無視されてしまうからです。
2006/2/28と表示されていても時刻部分が無いとは限らないと考えているわけです。
- 【解答例2】作業列を利用する
- 作業列を利用してもよいならば、I3セルに=MONTH(C3) と入力して、I19セルまでコピーします。
- J2:J3セルに抽出条件を書きます。 下図では1月分のデータ抽出になっています。2月分ならJ3セルに「2」と入力します。
L2:R2セルに抽出する列見出しを書き出しておきます。(ここでは作業列を除いています)
抽出範囲を列見出しを入力したL2:R2とするのがポイントです。
- 作業列を利用してもよいならば、I3セルに=MONTH(C3) と入力して、I19セルまでコピーします。
条件設定に数式を使う方法
- 条件を羅列するのは面倒・・・、見た目をすっきりさせたい・・・という場合は、数式で抽出条件を書けばよいでしょう。
- 【ポイント】条件式を入力するセルの上には空白セルが必要です。
- ただし、列見出しと同じでない文字列、例えば「条件」とか文字列なら入力してあってもよさそうです。
- 担当者が「岡田」かつ 売上金額が10万以上のデータを抽出したい場合の例です。
- 【例】
- 条件を数式で入力します。
データリストの一番上の行のデータに関する条件式を書きます。- ここの例では3行目がデータリストの最上行になっていますので、数式ではD3セル、H3セルの条件を書きます。
- J3セルに =AND(D3="岡田",H3>=100000) とします。
- または、論理式で=(D3="岡田")*(H3>=100000)としてもよいでしょう。
- 検索条件範囲には上の空白セルを含めて、「J2:J3」とします。(ダイアログでは絶対参照となります。)
- 以下のような結果になります。
- 条件を数式で入力します。
フィルタオプションで別シートへの抽出/重複データの削除 Topへ
- フィルタオプションを使って、別シートに重複しないデータを抽出する方法です。
- 別のシートに抽出する場合には、抽出先をアクティブにした状態で操作します。
- 【問題】Sheet1の住所録で重複した5,7行目を除いて、Sheet2へ抽出します。
B C D 2 氏名 住所 電話番号 3 会田一郎 鹿児島市山下町 123-1234 4 井上次郎 鹿児島市易居町 223-2233 5 会田一郎 鹿児島市山下町 123-1234 6 上田浩一 鹿児島市名山町 334-3344 7 会田一郎 鹿児島市山下町 123-1234 - 【解答例】Sheet2への抽出結果
B C D 2 氏名 住所 電話番号 3 会田一郎 鹿児島市山下町 123-1234 4 井上次郎 鹿児島市易居町 223-2233 5 上田浩一 鹿児島市名山町 334-3344 - 【操作手順】
- Sheet2を表示し、A1セルを選択します。
(ポイント:別のシートに抽出する場合には、抽出先をアクティブにした状態で操作します。) - 【データ】→【フィルタ】→【フィルタオプションの設定】を選択します。
- (注1) この時複数のセルを選択した状態ですと警告メッセージが表示されます。
一つのセルを選択した状態だと、警告は表示されません。
- (注2) また下記の「列見出しを含むリスト・・・」の警告が表示されることもあります。
この時は、気にせず「OK」をクリックすると先に進めます。
- (注1) この時複数のセルを選択した状態ですと警告メッセージが表示されます。
- 【フィルタオプションの設定】ダイアログで設定をします。
- リスト範囲 : Sheet1!$B$2:$D$7
抽出先の『指定した範囲』にチェックを入れます。
抽出範囲 : Sheet2!$A$1 とします。
『重複するレコードは無視する』にチェックを入れます。 - 【OK】ボタンをクリックします。
- リスト範囲 : Sheet1!$B$2:$D$7
- Sheet2に重複していないデータが抽出されました。
B C D 2 氏名 住所 電話番号 3 会田一郎 鹿児島市山下町 123-1234 4 井上次郎 鹿児島市易居町 223-2233 5 上田浩一 鹿児島市名山町 334-3344
- Sheet2を表示し、A1セルを選択します。
- 【操作手順】
フィルタオプションで特定の項目(列)を抽出する Topへ
- 上記ではデータ表の全項目を抽出しましたが、特定の項目(列)のデータを抽出できます。
- 【例】B2:D7のデータから重複しないデータの氏名と電話番号をB9:C9以降に抽出します。
- 【操作手順】
- 抽出する項目(列見出し)をあらかじめ抽出先に入力しておきます。
この例では B9セルに『氏名』、C9セルに『電話番号』と項目を入力しています。 - 【データ】→【フィルタ】→【フィルタオプションの設定】を選択します。
- リスト範囲 : B2:D7 (項目行を含めてデータ範囲を指定します)
『指定した範囲』にチェックを入れます。
抽出範囲に抽出したい項目が入力されている『$B$9:$C$9』とします。
『重複するレコードは無視する』にチェックを入れます。 - 【OK】ボタンをクリックします。
- リスト範囲 : B2:D7 (項目行を含めてデータ範囲を指定します)
- B10:C12にデータが抽出されました。
B C D 2 氏名 住所 電話番号 3 会田一郎 鹿児島市山下町 123-1234 4 井上次郎 鹿児島市易居町 223-2233 5 会田一郎 鹿児島市山下町 123-1234 6 上田浩一 鹿児島市名山町 334-3344 7 会田一郎 鹿児島市山下町 123-1234 8 9 氏名 電話番号 10 会田一郎 123-1234 11 井上次郎 223-2233 12 上田浩一 334-3344
- 抽出する項目(列見出し)をあらかじめ抽出先に入力しておきます。
フィルタオプションの設定をVBAで処理する Topへ
- データを追加するたびに、毎回フィルタ オプションの設定を実行するのは面倒です。
- VBAを作成して、ボタンをクリックするだけで実行できるようにしてみます。
- なお、ここで使用しているのはExcel2002です。
くどいようですが、上記に書きましたようにこのバージョンは完全一致での検索となり、その他のバージョンでは前方一致となりますことをお断りしておきます。
VBAの作成例
- データおよびコードを利用しやすいように修正しました。
- 某掲示板にてコードが動かないとの相談がありましたので、入力ミス等が起きかねないことからデータを表で書き改めました。
さらに、コードもコピー&貼り付けができるように画像から表での表記にしました。(2010年2月)
- 某掲示板にてコードが動かないとの相談がありましたので、入力ミス等が起きかねないことからデータを表で書き改めました。
- Sheet1の元データ例
B C D E F G H 2 No 月日 項目名 収入 支出 概要 購入店名 3 1 2010年1月1日 繰越 120,000 4 2 2010年1月1日 交際費 12,000 お年玉 5 3 2010年1月2日 食費 12,000 はいから亭 6 4 2010年1月3日 教育・教育費 2,300 金海堂 7 5 2010年1月3日 食費 2,400 惣菜 タイヨー 8 6 2010年1月4日 教育・教育費 230 金海堂 9 7 2010年1月4日 食費 500 セブンイレブン 10 8 2010年1月5日 食費 625 お菓子 クッキー 11 9 2010年1月8日 教育・教育費 1,000 春苑堂 12 10 2010年1月8日 食費 1,200 はいから亭 13 11 2010年1月10日 教育・教育費 570 金海堂 - Sheet2の抽出データ例
VBA(マクロ)作成手順
- 【操作手順】
- 抽出先のSheet2を選択し、抽出条件をB2に「項目名」、B3に「食費」と入力します。
- [ツール]→[マクロ]→[新しいマクロの記録]を選択し、「マクロの記録で[OK]ボタンをクリックします。
- Sheet2のセルを選択した状態(Sheet2がアクティブな状態)で実行します。
- (注1) この時複数のセルを選択した状態ですと警告メッセージが表示されます。
一つのセルを選択した状態だと、警告は表示されません。
- (注2) また下記の「列見出しを含むリスト・・・」の警告が表示されることもあります。
気にせず「OK」をクリックすると先に進めます。
- [データ]→[フィルタ]→[フィルタオプションの設定]を選択し、設定を入力します。
[OK]ボタンをクリックすると、Sheet2にデータが抽出されます。
- 自動記録を終了するため、[記録終了]ボタンをクリックします。
または、メニューバーの[ツール]→[マクロ]→[記録終了]を実行します。
- [ツール]→[マクロ]→[Visual Basic Editor]を選択し、VBEのウィンドウを開きます。
「標準モジュール」の「Module1」をダブルクリックすると、右側に記録作成されたコードが表示されます。
以下のようなコードが記録されます。Sub Macro2()
'
' Macro1 Macro
' マクロ記録日 : 2010/2/13 ユーザー名 : owner
'
Sheets("Sheet1").Range("B2:H13").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B2:B3"), CopyToRange:=Range("B5"), Unique:=False
End Sub - このままのコードでは、抽出先Sheet2のB5以下の行をクリアしないとうまく動きません。
また、データが増減した場合にも対応できないかもしれませんので、コードをちょっと変更します。Sub Macro2()
Dim myRow1 As Long, myRow2 As Long
'----Sheet1とSheet2のB列で最終行を捜します。
myRow1 = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
myRow2 = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
'----Sheet2のB5以下が入力されていたらクリアします。
If myRow2 >= 5 Then
Sheets("Sheet2").Range("B5:H" & myRow2).ClearContents
End If
'----フィルタオプションの設定で抽出します。
'----元データはSheet1、抽出条件はSheet2のB2:B3、抽出先はSheet2のB5です。
Sheets("Sheet1").Range("B2:H" & myRow1).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet2").Range("B2:B3"), _
CopyToRange:=Sheets("Sheet2").Range("B5"), _
Unique:=False
End Sub- Excel2007で最大行が増えましたので、最終行を捜す部分を
myRow1 = Sheets("Sheet1").Range("B65536").End(xlUp).Row
↓
myRow1 = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
と修正しました。
- Excel2007で最大行が増えましたので、最終行を捜す部分を
- [表示]→[ツールバー]→[フォーム]を選択し、フォームツールバーを表示します。
[ボタン]をクリックし、Sheet2にボタンを作成します。
- 「マクロの登録」ダイアログが開くので、先ほど作成したマクロ「Macro2」を選択し、[OK]ボタンをクリックします。
- Sheet2の[ボタン1]にマクロが登録され、ボタンをクリックするとマクロが実行されます。
- ボタンを右クリックすると、ボタン名を変更できますので、「データ抽出」としてみました。
B3セルに「教育・教養費」と入力し、「データ抽出」ボタンをクリックするとデータが抽出されました。
2013年07月06日
エクセル(Excel)複数条件や数値などでデータを抽出する関数
次のページから解説するExcelブックは下記よりダウンロードできます。
なお、本文中サンプルデータに入力する数値や数式は、カギカッコで囲んで表示します。
◎サンプルデータ (zipファイル 11KB) >>ダウンロード
関数を学習時間がない方はこちらの● をダウンロードし、使ってみたらどうですか。Excelデータ検索・抽出らくらく
VLOOKUP関数
VLOOKUP関数は、指定した条件に対応した値を一覧表から抽出する関数です。
VLOOKUP関数を使って、“氏名”をオーダー表に入力した際、“連絡先”をあらかじめ用意されている“お客様情報”の中から抽出して表示してみましょう。
- セルB3を選択する。
- [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。 ▼ 図02-01 セルB3を選択し、[関数の挿入]ボタンをクリック
【Excel2007の場合】
Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。
▼ 図02-02 Excel2007の[関数の挿入]ボタン - [関数の分類]ボックスから[検索/行列]を選択する。
- [関数名]ボックスから[VLOOKUP]を選択する。
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。 ▼ 図02-03 [関数の挿入]ダイアログボックス
- セルA3をクリックし、[検索値]ボックスに「A3」と表示されていることを確認する。
- [Tab]キーを押して[範囲]ボックスにカーソルを移動する。
※ [範囲]ボックス内をクリックしてカーソルを移動することもできます。 - セルF3からI8をドラッグし、[F4]キーを押して絶対参照にする。
※ 数式をコピーしても参照範囲が変更されないよう[F4]キーを押して絶対参照にします。 - [列番号]ボックスにカーソルを移動し、「3」と入力する。
※ 範囲内の左から何列目のデータを抽出したいかを指定します。 - [検索の型]ボックスにカーソルを移動し、「false」と入力する。
※ 検索の型には検索値が完全一致の場合にデータを返すなら「false」、近似値を検索してデータを返すなら「true」を指定します。
※ Excel 2007では、[検索方法]ボックスです。
- [OK]ボタンをクリックする。 ▼ 図02-04 [関数の引数]ダイアログボックス
セルB3に連絡先が表示されます。作成した数式をコピーし、住所も表示させましょう。
▼ 図02-05 数式をコピーして住所を表示
式がコピーされますが、エラー値「#N/A」が表示されてしまいました。原因は、オートフィル機能により検索値がセルA3からB3に自動的に変わってしまったためです。
もう一度[関数の挿入]ダイアログボックスを表示して、式を修正しましょう。
- セルC3を選択し、[関数の挿入]ボタンをクリックする。
- [関数の挿入]ダイアログボックスが表示される。
- [検索値]ボックスの値をセルB3からA3に修正する。
- [範囲]が$F$3:$I$8であることを確認し、[列番号]ボックスは「4」に変更する。
- [検索の型]が「FALSE」になっていることを確認し、[OK]ボタンをクリックする。 ▼ 図02-06 [関数の挿入]ダイアログボックス
数式が修正され、住所が正しく表示されます。
DGET関数
次に、複数の条件で合致したデータを抽出するDGET関数を紹介します。
お客様情報リストには、「田中正美」という同じ名前の人が二人存在しています。このような場合、VLOOKUP関数では正しく抽出を行うことができません。DGET関数を使用すれば、「氏名」と「生年月日」の二つの条件から正しいデータを特定できます。
- “オーダー表2”シートを表示する。
- セルC3を選択する。
- [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。 ▼ 図03-02 セルC3を選択し、[関数の挿入]ボタンをクリック
【Excel2007の場合】
Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。
▼ 図03-03 Excel2007の[関数の挿入]ボタン - [関数の分類]ボックスから[データベース]を選択する。
- [関数名]ボックスから[DGET]を選択する。
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。 ▼ 図03-04 [関数の挿入]ダイアログボックス
- セルF2からI8をドラッグして[F4]キーを押し、[Database]ボックスに「$F$2:$I$8」と表示されていることを確認する。
※ データの参照先は変更されないように絶対参照にしておきます。
※ Excel 2007では[データベース]ボックスです。 - [Tab]キーを押して[フィールド]ボックスにカーソルを移動する。
※ [フィールド]ボックス内をクリックしてカーソルを移動することもできます。 - 抽出したいデータの項目名が入力されているセルH2をクリックする。
- [Criteria]ボックスにカーソルを移動し、セルA2からB3をドラッグして指定する。
※ [Criteria]ボックスには抽出条件となるセル範囲の項目名を含めて選択します。
※ Excel 2007では[条件]ボックスです。 - [OK]ボタンをクリックする。 ▼ 図03-05 [関数の引数]ダイアログボックス
氏名と生年月日の2つの条件から連絡先が抽出されました。
DGET関数の使い方は理解できましたか?
VLOOKUP関数では1つの条件から、DGET関数では複数の条件からデータを取り出すことができます。状況に応じて、使い分けるようにしましょう。
CHOOSE関数
ここまではシートの中にあらかじめ用意されたリストの中からデータを抽出してきましたが、CHOOSE関数を使えば、リストをシート中に作らなくても、式の中でリストを指定できます。
では、“オーダー№”を入力したときに“種別”を表示するようなCHOOSE関数の式を作ってみましょう。
- “オーダー表”シートに切り替える。
- セルB6を選択する。
- [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。 ▼ 図04-01 セルB6を選択し、[関数の挿入]ボタンをクリック
【Excel2007の場合】
Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。
▼ 図04-02 Excel2007の[関数の挿入]ボタン - [関数の分類]ボックスから[検索/行列]を選択する。
- [関数名]ボックスから[CHOOOSE]を選択する。
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。 ▼ 図04-03 [関数の挿入]ダイアログボックス
- [インデックス]ボックスに「A6-100」と入力する。
※ [インデックス]ボックスに指定された数値が「1」ならば値1を、「2」ならば値2を返します。今回はセルA6に入力されているオーダーNoが101から開始しているため、「セルA6から100」を引いた数値がインデックス番号となります。 - [Tab]キーを押して[値1]ボックスにカーソルを移動する。
※ [値1]ボックス内をクリックしてカーソルを移動することもできます - 「デスクプリント」と入力し、[Tab]キーを押して[値2]ボックスにカーソルを移動する。
※ [値1]ボックスにはインデックス番号が「1」の場合に表示する値を指定します。 - 「ラベルプリント」と入力し、[Tab]キーを押して[値3]ボックスにカーソルを移動する。
- 「デザインプリント」と入力し、[OK]ボタンをクリックする。 ▼ 図04-04 [関数の引数]ダイアログボックス
“オーダー№”の値に応じて、CHOOSE関数の中で設定された“種別”が表示されます。
“オーダーNo.”を「102」や「103」に変更し、“種別”が変更されることを確認しましょう。
SUMPRODUCT関数
最後に、対応する要素同士の積を合計する『SUMPRODUCT関数』を紹介します。
文章での説明は、少し難しいので実際に数式を作成しながら解説をしていきます。
“オーダー表”シートの右下にある「オーダー内容」を参照し、「オーダー表」に入力した“仕上サイズ”と“刷色”から料金を表示させます。
- セルC8を選択する。
- [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。 ▼ 図05-01 セルC8を選択し、[関数の挿入]ボタンをクリック
【Excel2007の場合】
Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。
▼ 図05-02 Excel2007の[関数の挿入]ボタン - [関数の分類]ボックスから[数学/三角]を選択する。
- [関数名]ボックスから[SUMPRODUCT]を選択する。
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。 ▼ 図05-03 [関数の挿入]ダイアログボックス
- [配列1]ボックスに「($F$12:$F$19=A8)*($G$12:$G$19=B8)」と入力する。
- [配列2]ボックスにセルH12からH19を選択し、[F4]キーを押す。
- [OK]ボタンをクリックする。 ▼ 図05-04 [関数の引数]ダイアログボックス
オートフィル機能を使用して、他のセルにも同じように関数を挿入します。
最後に、もう一度SUMPRODUCT関数を使用し、“料金”と“枚数”の値を使用して“オーダー料金”を算出しましょう。
- セルD13を選択する。
- [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。 ▼ 図05-06 セルD13を選択し、[関数の挿入]ボタンをクリック
【Excel2007の場合】
Excel2007では[数式]タブの[関数の挿入]ボタンをクリックしても[関数の挿入]ダイアログボックスが表示できます。
▼ 図05-07 Excel2007の[関数の挿入]ボタン - [関数の分類]ボックスから[数学/三角]を選択する。
- [関数名]ボックスから[SUMPRODUCT]を選択する。
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。 ▼ 図05-08 [関数の挿入]ダイアログボックス
- [配列1]ボックスにセルC8からC12を選択し、[C8:C12]と表示されたことを確認する。
- [配列2]ボックスにセルD8からD12を選択し、[D8:D12]と表示されたことを確認する。
- [OK]ボタンをクリックする。 ▼ 図05-09 [関数の引数]ダイアログボックス
これで数式は完成しました。枚数を入力し、オーダー料金が計算されるかどうか確認してみます。
料金×枚数の合計が“オーダー料金”に反映されたでしょうか?
今回は、台帳の管理などデータを扱う場合に活用できる関数をいくつかご紹介しました。用途に応じてこれらの関数を組み合わせて使いこなすことで、Excelでの処理をどんどん楽にできます。そのためには、今回使用したサンプルの引数や条件などを自分なりに変更し、関数の使い方やどの値が表示されるのかなどをしっかりマスターしておきましょう。
2013年06月19日
はがき、年賀状、宛名印刷はエクセル・ワードを活用
「エクセル(Excel2013)で住所録を作ったのに宛名印刷の方法がわからない」という話をよく聞きます。住所録を作るのはエクセルの得意分野ですが、はがきの宛名印刷はワード(Word2013)に任せましょう(図1)。画面の指示に従うだけで印刷できます。
宛名の印刷に必要な住所録は、作表機能が充実しているエクセルであらかじめ作成しておきます。その際、図2のような項目名で住所録を作っておくと、ワードでスムーズに読み込めます。住所は1行に印刷しきれないことが多いので、「住所1」と「住所2」に分けます。連名にする場合は、連名に付ける敬称も入力しておきましょう。
図2 エクセルの住所録を用意する。1行目の項目名は、この表のように左から「姓」「名」「連名」「敬称」「会社名」「郵便番号」「住所1」「住所2」と設定しておくと、正しく読み込める
エクセルの住所録を読み込むには、ワードの「差し込み文書」にある「はがき印刷」を使います(図3)。
画面の指示に従ってレイアウトやフォント(下掲の「用語解説」を参照)を設定したら、読み込むエクセルのファイルを指定します(図4~図12)。
ファイルを開いたらレイアウトで住所のデータを確認しましょう(図13)。宛名印刷では、「フィールド」(下掲の「用語解説」を参照)と呼ばれるレイアウト用の枠内に、住所録のデータを読み込んで表示します(図14)。
【用語解説】
フィールド データベースを構成する項目のこと。住所録であれば、氏名、住所、電話番号、メールアドレスなどがフィールドとなる。
レコード データベースに登録されている1組のデータを指す。住所録の場合、名前、郵便番号、住所、電話番号などの情報が一つのレコードになる。
プレビュー 特定の操作をする前に、その結果を画面上で確認すること。またはその画面のこと。
「連名」と「敬称」のフィールドを追加することで、連名が入力されている場合のみ、連名が印刷されるように設定できます(図15~図18)。 宛名が正しく表示されていることを確認したら、印刷しましょう(図19、図20)。
[上]図18 「差し込み文書」をクリックしたら(図中の赤丸1)、図15の要領で「様」の後ろで改行する(図中の赤丸2)。「差し込みフィールドの挿入」の下半分をクリックし(図中の赤丸3)、「敬称」をクリックする(図中の赤丸4)
[下]図19 「結果のプレビュー」をクリックし(図中の赤丸1)、「次のレコード」をクリックして(図中の赤丸2)、住所録データを順番に確認していくと、連名がある場合のみ、連名と敬称が表示される(図中の赤丸3)
2012年12月28日
PDF to Word (Win版)今なら無料で入手できます。
簡単、高精度でPDFファイルをWordに変換!
日常業務などでデジタル文書形式のPDFや、Webサイトからテキスト情報が必要な時、どのようにしていますか?
PDFをうまく使いこなすって難しいですよね?そんな時に、WondershareのPDF to Wordが便利です。
このツールを使えば、PDFを簡単にWord文書に変換することができます。
- ●PDF to WordはPDFファイルをWord形式のファイルに変換する専用ソフトです。
- ●仕事の効率アップにつながる便利な機能も多数搭載しています。
- ●操作は簡単。わずか3ステップでPDFファイルをWordに変換できます。
- ●バッチ処理で1分間に100ページのPDFファイルを変換します。
- ●多言語インターフェース(日本語、英語、ドイツ語、ポルトガル語、スペイン語)搭載。
- ●PDFファイルの元の内容、レイアウトと書式設定を保持します。
- ●超簡単操作!シンプル設計でパソコン初心者でも無理なく使いこなせます。
*PDF to Word (Win版)は現在、無料プレゼントとしてご提供しております。キャンペーンの詳細は
メールアドレスを入力するだけで人気製品を入手!
Adobe Acrobat X Standard for Windows アドビ アクロバット(2台インストール可能) ¥29,800円 ¥7,800円 |