« 2012年10月 | メイン | 2013年03月 »

2012年11月10日

EXCELアドイン 休日関数

Excelアドイン関数 休日・営業日 Ver1.13 フリーソフト 2012/11/10  ダウンロード

 本ソフトには休日関係の二つの関数がアドインとして含まれています。

 休日 :指定日が休日であればその名前を返却
 営業日:指定日からn日後の営業日を返却
 ここで、休日とは
  ・「国民の祝日に関する法律」で指定された日
  ・土日(オプションで指定可)
 とします。
【関数の説明】
 ・休日
   日付から休日名を表示
    ・祝日は1901年1月1日以降の明治/大正/昭和戦前/昭和戦後/平成に対応
    ・春分/秋分の日は計算で求めた天文学上の日付で対応
    ・2007年以降の「昭和の日」に対応
   関数パラメータ
    日付  :調査する日付を指定
    土日表示:True - 土日を表示
         False - 土日を表示しない

 ・営業日
   指定日からn日後の営業日を表示
    (当日を0日とします)
   関数パラメータ
    日付  :調査する日付を指定
    経過日数:正の整数 - n日後の営業日
         0    - 当日が営業日の場合、当日の日付
                当日が休日の場合、空白
         負の整数 - n日前の営業日

<休日>アドインの詳細
<ダウンロード>

変更履歴

2012/11/10 2032/9/21 国民の休日不備
2008/05/03 5/4(国民の休日→みどりの日)
2007/10/28 天皇誕生日の不具合(2007~)
       アドイン開放時のエラー対応
2007/07/13 振り替え休日の不備対処
       2007年以降、5月4日に「みどりの日」が移動したことにより、火曜日以降にも
       振り替え休日が発生することへの対処
       2008/5/6(火)、2009/5/6(水)、2014/5/6(火) 
       などは月曜日以外の日が振り替え休日

投稿者 mt-soft : 08:00 | コメント (3)

2012年11月01日

はじめに-上級

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

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

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

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


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

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

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

投稿者 mt-soft : 10:00

基礎編

名称 概要 見本/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関数/配列数式
見本と
ダウンロード
小技集
(入力チェック)
入力規則を使用したチェック:日付範囲、時刻刻み、入力不可文字(全半角スペース)、リスト入力、重複チェック
見本と
ダウンロード

投稿者 mt-soft : 09:00 | コメント (0)

実践編

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

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

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

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

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

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

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

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

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

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

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

投稿者 mt-soft : 08:00 | コメント (0)

応用編

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

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

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

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

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

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

投稿者 mt-soft : 07:00 | コメント (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番目の区切り文字位置
見本と
ダウンロード

投稿者 mt-soft : 06:00 | コメント (0)

VBA中級

名称 概要 見本/DL
渦巻き エクセルシートに、右回転(渦巻き) して数値を表示していくプログラム。
右隣が空いていたら右折する方式と、n番目の渦巻き点の座標を直接計算する方式を記載。事項の「素数あれこれ」で「ラウムの螺 旋」を描画するときにも使用する
見本と
ダウンロード
各種演算 最大公約数、 最小公倍数、べき剰余などをユーザー定義関数を作成して求める。 見本と
ダウンロード
旧暦変換 西暦から、旧暦日付六曜日干支閏月判定の取得を行う 見本と
ダウンロード
ループ数 規定の操作の繰り返しで元の数字に戻る不思議な数字
カプレカ定数カプ レカ数、平方数字根、べき乗数字根階乗数字根ウラム・コル ビットのループ(3n+1ループ)
見本と
ダウンロード
素数あれこれ  エラトステネスの篩」による素数一覧表の作成、与えられた整数の素数判定、素因数分解、不思議な図形「ラ ウムの螺旋」など、素数のいろいろ。また、フェルマーの小定理と その応用について 見本と
ダウンロード
巨大桁数演算

100桁の整数同 士の加減乗除、2の2000乗、2の1兆乗を13で割った余り、な ど、通常では計算できない桁数の演算を行う。
ただし、整数のみ
見本と
ダウンロード
素数テスト

フェル マー・テストを使用した確率的素数テスト、レプユニット(1 が連続した)素数、メルセンヌ数のうち素数となる数値をリュカ数列でテスト
見本と
ダウンロード
セル・オートマトン(一次元) セルの値は左右のセルの値で決まる」 という、非常に単純化した一次元のセル・オートマトンをシミュレートする。
決定のルールは自由に変えられるようにチェックボックスに連動した 表形式としているので、さまざまなルールにより複雑なパターンが描かれるのを簡単に確認でできる。
見本と
ダウンロード
ライフゲー ム ラ イフゲームとは、「セル・オートマトン(cellar atutomaton)」の一種で、が生命の誕生、進化、淘汰などのプロセスを簡易的なモデル化のために考案されたシミュレーションゲーム。
単純なルールにもかかわらず、複雑なパターンを描き出す。
見本と
ダウンロード
森林火災 森林の成長、火災発生、延焼、回復のパターンをセル・オートマトンモ デルを使用したシミュレーション。 見本と
ダウンロード
迷路を作成 壁を伸ばしていく方法で迷路を作成
乱数を使用しているので毎回異なる迷路が作成される
見本と
ダウンロード
迷路を解く セル・オートマトンを利用して迷路を解く
数式により行き止まりを調べ、順次仮壁として詰めて行くことで袋小路を全て経路から排除して行く。
見本と
ダウンロード
迷路を解く 2 再帰プログラムを用いて迷路を解く
経路を探索し、行き止まりになったら動けるところまで(再帰を)戻ることで全経路を探索する。
見本と
ダウンロード

投稿者 mt-soft : 04:00 | コメント (0)

VBA上級

名称 概要 見本/DL
マンデルブロ集合 フラクタル図形の一種、「マンデルブロ集合」を画像化
エクセル2003版および、より広域画像のエクセル2010版を用意。
選択域拡大、選択域細密化、ひとつ戻る機能、
解像度、白黒/カラーの指定可
見本と
ダウンロード
ジュリア集合 フラクタル図形の一種、「マンデルブロ集合」が漸化式の定数値を変化させたのに対し、漸化式の初期値を変化させて描いたフラクタル図形。奇妙で複雑、そして美しいパターンが描き出される。 見本と
ダウンロード
IEタブ制御 IE(InternetExplore)のタブを制御するVBAプログラム
MSAA(MicrosoftActiveAccessiblity)インタフェースを使用して以下のようなIE.ウィンドウおよび、タブの操作を行う。
・IEウィンドウ一覧
・IEタブ一覧、タブ情報表示
・IEタブの切り替え-指定タブをアクティブにする
・IEタブを閉じる。
見本と
ダウンロード

投稿者 mt-soft : 03:00 | コメント (0)

はじめに-中級

中級はExcelが用意しているツールの使い方の練習です。
表の検索で基本となる
LOOKUP関数から、条件付書式/入力規則/データベース/ピボットテーブル/マクロ機能などを学習。
ここでも全て
問題形式なのでスキルを確認しながら能力を高めていきます。


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


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

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

投稿者 mt-soft : 01:00

LOOKUP関数

投稿者 mt-soft : 00:59 | コメント (0)

条件付書式と表示形式

投稿者 mt-soft : 00:58 | コメント (2)

入力規則

投稿者 mt-soft : 00:57 | コメント (4)

データベース

投稿者 mt-soft : 00:56 | コメント (5)

分析ツール

投稿者 mt-soft : 00:55 | コメント (2)

マクロ機能

投稿者 mt-soft : 00:54 | コメント (0)

シミュレーション

投稿者 mt-soft : 00:53 | コメント (8)