2012年11月01日

はじめに-上級

このサイトでは、実践レベルの問題を用意しました。
実務上の問題に利用、または応用できるように作ってあります。

本解答例を暗記しても何にもなりません。
実際に考え、頭から煙が立ち昇るまで考え抜いてください

解答が思いつけば万々歳!。
どうしても分からない時のみ解答例を参考にしてください。
考えに考え抜いた後にはすっきりと理解でき、身に付くはずです。
特に「
応用編」は、数時間、場合によっては数日掛かる問題もあります。

どうぞ自由にダウンロードしてお使いください。


補足1:それぞれの問題はエクセルのシート形式になっています。
    ほとんどは、
複数のシートで構成されていますので、シートの移動は、画面下側のシート見出しをクリックして下さい。
補足2:
回答の正否判定やシートの移動などにマクロを使用(内容が赤文字の問題)している場合があります。
    
必要な場合は、「マクロを有効」にして下さい。

初級問題 中級問題 上級問題 VBA問題

知りたい機能別/項目別の一覧もあります。ご利用下さい。

基礎編

名称 概要 見本/DL
並べ替え 並べ替えの基礎練習
 ・宝くじの番号を下二桁で並べ替える
 ・誕生日を月と日で並べ替える
 ・数字以外の文字で始まる連番を昇順に並べ替え
それぞれ、単純な並べ替えの前に一工夫が必要
見本と
ダウンロード
表:一行おきに色塗り 大きな表の場合、表の中央を見ていると、どの行を見ていたのかわからなくなることがあります。そのような場合、表が1行おきに色分けされていると見やすくなります。行の追加/削除に対応して自動で色分けされるようにします。
見本と
ダウンロード
表:空白のある行全体に色を塗る 入力必須の表の場合、空白セルだけでなく、その行全体がハイライト表示されると未入力行を見つけやすくなります。

見本と
ダウンロード
重複しない項目リストを作成 重複する項目が多数ある表から、重複を排したリストを作成する。


見本と
ダウンロード
関数を使って並べ替え 文字列としての項目がある表を並べ替える。元の表での並び順を考慮して、同姓同名がある場合でもエラーが出ない容易にする
見本と
ダウンロード
生年月日を考慮した並べ替え
フリガナによる並べ替え → 同じフリガナの場合指名漢字を考慮 → 同フリガナ同姓同名の場合、生年月日を考慮→同姓同名同生年月日の場合、元の表での並び順を考慮
見本と
ダウンロード

重複リスト 既存データから重複している項目の件数を算出、重複を除いた項目リストを作成、JISコード昇順への並べ替え2つの表から重複している項目の検出を算出
見本と
ダウンロード
名前を使った動的な表参照 範囲に「名前」を付けることによって、入力区分 A~C に応じた動的な入力規則の選択リストを作成(INDIRECT関数)。
VLOOKUPによる表引きも名前を使って、対象の表を自動で選択する。
複数の表を切り替えて参照する場合は必須の機能
見本と
ダウンロード
売上高合計 指定された条件を満たす製品の売上高の合計を求める。ここでは、
 ・売上高で指定順位までの合計
 ・売上高比で指定累計上位までの合計を求める。
 ・売上高からABC分析表を作成する
見本と
ダウンロード

プロジェクト
進捗状況表
難しく思える問題をステップを分ける事により簡単化
複合参照IF関数の中での関数の使用方法を学習
 ・ステップ1-単一プロジェクト
 ・ステップ2-複数プロジェクト
 ・ステップ3-四捨五入した進捗状況表
 ・ステップ4-進捗度によりグラフ記号を変える
見本と
ダウンロード

工程管理表 前工程が終わらないと次の工程が始まらない工程管理表を作成する。
作業開始日と、必要な工程の「所要日数」を順番に入力するだけで、作業全体の予定がカレンダーに表示されるような工程管理表を作成。工程表作成の基礎
見本と
ダウンロード

うるう年判定 年を入力すると、その年が閏年かを判定する。
これ自体では大した用途は無いが、カレンダーなどの作成時に必須の技となる。
複数の条件を満たす解を求める方法を会得。
IF/MOD/AND/OR関数の使い方
見本と
ダウンロード

ハッピーマンデー ある年の「成人の日」「敬老の日」「体育の日」を求める
第n月曜日の論理的な求め方の問題。DATEWEEKDAYを使用

見本と
ダウンロード

春分・秋分の日 春分・秋分の日を天文データから算出。INTVLOOKUPだけで1851年~2150年までの春分・秋分の日を求める。 見本と
ダウンロード

簡易カレンダー 年と月を指定すると1月分のカレンダーを自動作成。日付関連の関数DATEWEEKDAYを使用。 条件付書式により前月/来月を目立たない灰色にし、入力規則およびフォームで使いやすいカレンダーにする。 このカレンダーをベースに、下記で登場する様々なの技を組み合わせて高機能化 見本と
ダウンロード

50年祝日カレンダー 簡易カレンダーをベース
2000年~2050年までの祝日表を元に、祝日名を表示する。
今月以外の日付は灰色、また、今日の日付はオレンジで塗り潰す。
見本と
ダウンロード

旧暦カレンダー 現行歴(グレゴリオ暦)と旧暦(天保歴)との対応表。2001年~2030年の日付対応と六曜日日の干支(十干十二支)、閏月
旧暦の「ついたち」朔月(新月)と西暦の対応表から変換
見本と
ダウンロード
小技集
(日付と時刻)
第n曜日の日付、nヵ月後の最初の指定曜日の日付、最終営業日、曜日別集計、週ごとの集計


見本と
ダウンロード
営業日数 指定期間の日数。土日を除いた 営業日数土日および祝日を除いた営業日数WORKDAYNETWORKDAYS関数を使用して、土日や指定リストの日を除外した営業日のみを対象として、その日数、指定日数後の営業日の日付などを取得することが出来る。 見本と
ダウンロード

金種計算 各種の金種計算(最小支払い枚数演算)の問題。
支払いに要する紙幣と硬貨の枚数の和が最少となる支払方法は?
社員に現金でボーナスを支給するとき、用意すべきそれぞれの紙幣、硬貨の枚数合計は?
見本と
ダウンロード

不一致検索 2つのリストがあり、片方に無い項目を検索。
ACCESSの不一致クエリのようなものをEXCELで実現
条件付き書式の中でCOUNTIFVLOOKUPなどの関数を使うと実現可。
見本と
ダウンロード

計算式の選択 リストから「合計」「平均」「最大」「最小」などを選択することで、自動的に計算方法が変化する表を作成。subtotalの練習問題 見本と
ダウンロード

Match練習 Match関数は、指定の範囲を検索して見つかった位置を与える関数。 表の検索には欠かせない関数で、特にIndex関数と組み合わせるとVlookup関数より融通の利く処理を行える。 見本と
ダウンロード

配送料金計算 配送先、重量、および日祝割引、個数で決まる小包配送料金を計算。 各項目をリストから選択することで配送料金を自動計算。Macth,Index関数の使い方を練習 見本と
ダウンロード

小技集
(検索・カウント)
検索やカウントに関する小技集
・文章中の指定文字列の個数
・特定範囲の数値が入力されているセル個数
ワイルドカードを使用して指定文字列が入力されているセル個数
・文字列を数字位置で分割
都道府県名の取り出し
重複を排除したリストの項目数
見本と
ダウンロード

小技集
(順位)
順位付けに関する小技集
記号で評価、同一記録を除いて順位を付ける
見本と
ダウンロード
小技集
(文字列整形)
2013 年 8月7日のように1桁の場合は空白を入れて揃える/20130831 を日付形式 2013/08/31に変換/郵便番号と住所を改行で1つのセル内に連結/余分な空白を削除/追加・削除しても崩れない連番を作成/50円単位に切り 上げ
見本と
ダウンロード
小技集
(文字使用頻度)
文字列中で一番多く使われている文字は?2番目は?
都道府県名のような文字列で使われている文字、およびその使用個数を個数の降順、読みの昇順でリストを作成します。
COUNTIF関数/MODE関数/配列数式
見本と
ダウンロード
小技集
(入力チェック)
入力規則を使用したチェック:日付範囲、時刻刻み、入力不可文字(全半角スペース)、リスト入力、重複チェック
見本と
ダウンロード

| コメント (0)

実践編

名称 概要 見本/DL
セル内グラフ 文字列を利用したセル内グラフ。正負の値に対応。EXCELのグラフ機能を使わなくても、簡単なグラフは関数で即作成。REPT/LEFT/RIGHT関数を使用 見本と
ダウンロード

連続データ 空白以外の同じデータが3個以上連続するセルの色を塗り潰す。
また、データを指定する場合も考える。条件付き書式とCOUNTIF関数で実現
見本と
ダウンロード

同姓同名は入力注意 名簿リストに同姓同名の入力時は注意メッセージを表示する。
また、氏名の入力は全角のみ(半角不可)に制限する。
見本と
ダウンロード

勤務時間計算表 通常勤務、夜間勤務が混在する場合の勤務時間、及び給与の計算、夜間時間の開始判定、午前零時をまたいだ場合の時間の考え方 見本と
ダウンロード

勤怠管理表 早退/遅刻の日数、曜日によって異なる給与を計算
日付をまたいだ残業代を計算
見本と
ダウンロード

出勤管理表 簡単な給与計算が付いた出勤管理表を作成。入力規則/条件付書式を利用して入力支援を行う。また、日付と時刻の関数を駆使して、勤務日数/勤務時間/勤務分を計算。 見本と
ダウンロード

勤務チャート シフト表からその日の勤務時間帯チャートを表示。また、1月分のシフト表から日付を選択すると、その日の勤務時間帯のチャートを表示。VLOOKUPを主体に、OFFSET関数などを使用して発展性のあるチャートを作成。 見本と
ダウンロード

参照表の切り替え INDIRECT関数を使用して複数の参照表を切り替える 見本と
ダウンロード

計算誤差 Excelは有限の桁数で計算しているので、計算結果には誤差が含まれることがある。これをIFなどで判定する場合、不思議な結果をもたらすことがある。ここでは、計算誤差の例と対処方法を考える 見本と
ダウンロード

計算誤差演習 得点から順位を算出する問題。計算誤差が発生するのを合わせて解決する。 見本と
ダウンロード

最適値問題 条件から最適の値を求める。ここでは、おでんだねセットを題材に、たねの在庫数とセットに必要なたね数から作成可能な最大セット数を求める。また、ソルバーを使って、たねの余りを最少にするセットの組合せを求める 見本と
ダウンロード

| コメント (0)

応用編

名称 概要 見本/DL
便利屋さんの受注簿 2日以上の未対応の受注をピンクで強調して管理しやすい表を作成
条件付書式日付の演算を駆使する
見本と
ダウンロード

百年カレンダー 祝日を考慮したカレンダーを作成。1949年~2150年の全祝日及び振替休日に対応。ハッピーマンデー/春分・秋分/国民の休日および即位の礼などの単年休日にも対応。年による祝日名変更、2007年以降の昭和の日も考慮 見本と
ダウンロード

受講管理 学生の授業の受講管理を行う。授業は、「Ⅰ:入門」「Ⅱ:初級」「Ⅲ:中級」「Ⅳ:上級」の4段階。① 授業は「入門」から順番に受ける。② 一日に何項目受けてもよい(最高4個まで)管理がしやすく、誤入力を防ぐ受講管理プログラムを作成 見本と
ダウンロード

ボーリングスコア計算 ボーリングのスコア計算は結構面倒な計算を必要とする。スペアストライクは単なる累計ではなく、未来のピン数まで加算するため厄介だ。また、10フレーム目も特殊な計算方法になる。。 見本と
ダウンロード

ゴルフハンデ計算 ゴルフのハンデキャップ計算を行う。使う計算式は 新ぺリア方式(ダブルペリア)、キャロウェイ方式
VLOOKUP,SMALL,LARGE,INDEX,LANKなどの関数を駆使して計算
見本と
ダウンロード

ゴルフハンデ計算_2 同上計算。ただし、使用する表と数式を、もう少しシンプルにするためにVBを使ったユーザー定義関数を作成する。VBへの入り口となる。 見本と
ダウンロード

| コメント (0)

その他

名称 概要 見本/DL
項目リスト作成 既存の表から重複しない項目リストを生成する。オートフィルタ機能などは使わず、関数のみで表から 項目をリスト化し、入力規則のリストとする。
通常はリストを先に作成しておくが、既に手入力済みの場合や、新項目が次々に発生する場合などに有効.。新項目を既存リストからではなく、手入力すると、自動的にドロップダウンリストに追加される。
見本と
ダウンロード

配列数式 基礎 配列定数とは、配列定数を利用した表引きの例、九九の掛け算表
見本と
ダウンロード
配列数式 配列数式を使ったシフト表の曜日別集計を行う。補助表を作成すれば通常の方法でも可能だが、配列数式を使うことでシンプルに、しかもブックサイズを増やすことなく計算式が実現できる。 見本と
ダウンロード
出席番号 名簿の読み(フリガナ)の小さい順に出席番号を割り振る。
さらに、読みが同じ場合は、生年月日の若い順に番号を振る。
見本と
ダウンロード
最適値取得BMI 歴代大統領の中で、最適のBMI値を持つ人は誰でしょう?
配列数式を用いて、最適なBMI値(体格指数-肥満度)を持つ人を検索
見本と
ダウンロード
配列数式 重複チェック 2つの表から重複している項目の数、および、重複項目リストを作成
見本と
ダウンロード
非重複リスト 重複した項目のある表から、重複を排除したリストを作成する。配列数式と作業表を多用する通常の方法。これを応用して、追加/変更/削除が自動で反映される入力規則用のリストを配列数式と関数で作成する。 見本と
ダウンロード
クロス集計 SUMPRODUCT関数、および配列数式を用いたクロス集計 見本と
ダウンロード
配列数式 検索 配列数式を使った各種検索
本日の予定一覧、最新の予定、最後の予定、来訪者一覧、最多来訪者
見本と
ダウンロード
配列数列 集計 配列数列を使用した各種集計。AND条件カウント、合計、ワイルドカード、県別集計、上位3名の平均値
見本と
ダウンロード
配列数列 日付・時刻 頻度集計月、平均年齢、平均年齢_条件付、最初の金曜日、最も多い曜日、曜日集計、直近の上映開始時刻、事故区別カウント、指定時刻カウ ント
見本と
ダウンロード
配列数式 応用 少し風変わりな配列数式の応用
・九九の掛け算表を配列数式で作る
・文字列中の数字で始まる部分を分離
・n番目に小さい値(重複あり昇順リスト)
見本と
ダウンロード
重複チェック 数字のリストから、重複(同じ数字が複数存在)するかを、作業表を用いる方法と、配列数式を使用する例を揚げる。
チェックする表として、数独を使用
見本と
ダウンロード
最下行データ  データベースから最下行のデータを取得する。
通常、最下行にあるデータベースの最新データを、データベースの上部に表示する。エクセルのバージョン( INFO)からワークシートの最大行数を求め、入力されているデータの個数 (MATCH)から最下行のデータを取得(OFFSET/COLUMN)。
見本と
ダウンロード
新しいウィンドウで開く-エクセル2003  エクセル2003以前のバージョンでWEBページを作成する場合に、「新しいウィンドウで開くハイパーリンクを設定する。このバージョンの設定項目には指定が無いので、 HTMLファイルを、なるべく簡単に編集する方法を考える。 見本と
ダウンロード
各種情報取得 OS/ブック/シート/セルについての各種情報取得。
INFO関数,CELL関数,OS情報,ブック情報,シート情報,メモリ容量,ファイルパス,カレントフォルダ,範囲先頭アドレス,列幅
見本と
ダウンロード
ファイル名取出し ファイルパス/URLからファイル名だけを取り出す方法
・空白置換とTRIM関数を利用、SUBSTITUTE関数パラメータを利用
・任意の区切り項目を取り出す
配列として処理し、ファイルパスからファイル名のみを取り出す
・配列として処理し、最後の区切り文字位置、n番目の区切り文字位置
見本と
ダウンロード

| コメント (0)