エクセル（Excel）の技。知ってましたか・・・。

この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ





【閑話休題】

先日職場の方より「簡単なスケジュール表を作ってほしい」という依頼があり。

作ってみました。

・１ヶ月ごとにＡ４で予定を書き込めるように枠を大きく。というのが条件です。

まあ毎月手動で日付を入れても構わないのですが、いいチャンスなので様々な

技を使ってスケジュール表を作ってみたので掲載いたします。


・レイアウトのイメージです。



Ｋ１に作成したい月の年月日（日は１日）を入力して、下の「カレンダーの作成」ボタンを

押すとその月のカレンダーが作成されるという仕組みです。

これを作成する際に私が検討した内容を思いついたまま書いてみます。

・Ａ１には年月を記載・・・Ｋ１の内容をコピーして表示設定を「年・月」にする。

・その月の１日は何曜日なのかを検出して、２行目の同じ曜日にＫ１の日付を挿入する

・第一週は「１」を挿入した次の列から「＋１」していく。土曜日（Ｉ列）まで

・１日以前の枠に前回作成した日付が残っている場合があるため、１日以前は空白にする必要がある。

・日曜・祝日は赤文字にしたい

・６週まであるのは、もし１日が金曜日とか土曜日から始まると、

週末が２４／３１のような形にする必要があります。

またそのようにすると「予定欄」が狭くなります。

結局６週分作成する方が目的に叶うかなと思い６週にしました。


上記の条件で作成開始しました。

・最初にＡ１の設定。セルには「=K1」と入力しK1の内容をコピーします。

セルの書式設定で「yyyy”年”m”月”」とします。

そうすることによってA1にはK1の日付が取り込まれ、表示は年・月になります。



・作成月の１日の曜日を取得する

これはVBAで取得する方が簡単なので下記のようなプログラムを挿入します。

コマンドボタンを作成してそこにプログラムを書いてください。

Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)

まずWeekday関数で日付から曜日の数字を取得します。

取得した数字をＬ１に挿入しなさいという命令文です。

上の例でいうと「L1」に「5」が表示されていますね。

日曜日が１で木曜日が５になります。

Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("L1"))

今度は逆にWeekdayName関数で「L１」の数字から曜日を作成して「L2」に挿入しています。

例ではL2に「木曜日」と表示されていますね。

これで作成月の１日の曜日は取得できました。


・次にC1から順番に見ていき先ほど取得した曜日と等しい曜日の２行目に

K1の日付をコピーしたいと思います。

やり方は色々あると思いますが、分かりやすいやり方として、

C1から順番に比較する方法を取りました。

Dim n As Integer 　列の変数の宣言

For n = 3 To 9

If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("L2") Then

Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")

Exit For

End If

Next n

上記の内容ですが、For〜Nextを使用した繰り返し文です。

C1から見ていきL2の内容と同じ曜日の場合、２行目にK1の日付を入れよという命令文です。

同じ曜日を見つけて日付を入力したらForから出なさいということです。

・次に「1日」より前の列の入力を消去していきます。

Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select

Selection.ClearContents

これはCellsで範囲指定してその間を選択して、消去せよという命令文です。

・次に「1日」以降の列に日付を入れていく必要があります。

For n = n To 8

Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1

Next n

ここでFor n = n To 8とありますね。先ほどの１日の続きなので現在nは「５」ですね。

ですから変数ｎは５〜８ということです。

Sheets("メイン").Cells(2, n + 1)は１日の次の列を指定しています。

ここではH2からスタートします。

順番に＋１していきなさいという命令文です。

これで第一週は出来ました。

２週目以降はどうしているのかというと、下のようにC4には「=I2+1」と入力されています。

「I2」の日付に＋１するようにセルに式を入れています。



D4は下のようにC4に＋１するように式を入れています。

E4以降も同じ。第３週以降も同じ式を入れています。



これで６週目までのカレンダーが出来ました。ただしこれは日付だけの話です。

・日曜・祝日を赤文字にしたい。

日曜日の列は単純に赤文字設定にすれば問題ありません。

後は休日設定ですね。

休日に関しては少し離れた場所（N18〜O35）あたりに2016年の休日一覧表を作っています。

ここを参照して休日を赤文字にしたいと思います。

隣に2017分がありますが、これは一旦無視してください。



全部の日付の欄に条件付き書式設定を設定します。

下のように「数式を使用して、書式設定するセルを決定」にしてルールは

「=COUNTIF($N$20:$N$35,C2)」と入力し、書式は赤文字にします。

まあC2は日曜日なので関係ないのですが一応同じように書式設定しておきます。

同じようにほかの日付が入るセルに条件付き書式設定をしてください。



めんどくさいのでコピーをしようと思うのですが、例えばC2をコピーしてD2からI2まで選択して、

貼り付けるとうまく行かないと思います。

１個ずつやればうまく行きます。C2コピーしてD2に貼り付けという感じですね。

１列（C2からI2）終われば、その１列全部コピーして２週目に貼り付けるのはＯＫです。

そのようにして３週目から６週目まで貼り付けしてください。


最後にVBAの全文を記載しておきます。

Private Sub CommandButton1_Click()

Dim n As Integer

Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)

Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("l1"))

For n = 3 To 9

If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("l2") Then

Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")

Exit For

End If

Next n

Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select

Selection.ClearContents

For n = n To 8

Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1

Next n

End Sub

これでK1に日付を記入してコマンドボタンを押すとカレンダーが完成します。

２０１７年になれば休日一覧を２０１６年の部分に上書きしてください。

VBAの書き方は色々あると思います。

上級者はもっと違うプログラムを組むと思いますが、私のレベルではこのくらいですね。

VBAの上級者を目指して頑張って行きましょう。


以上です。

一つだけ忘れていました。各日付のセルには「セルの書式設定」で「ｄ」にしてください。

日にちだけ表示させるためです。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




この吹き出しにテキストを挿入するには右クリックして「テキストの編集」を選択します。



この状態で何かコメントを入れてみてください。

？？？何も書けないですよね。

カーソルだけが移動していくということになります。



このテキストのフォントの設定で文字の色を確認してほしいのですが、

「自動」になっていますね。

この「自動」は「白」で設定されています。

吹き出しの塗りつぶしはデフォルトで青だったと思います。

そこから文字は白ということになっているのでしょうね。

最初は分からなくて「文字が書けない！」とパニックになった記憶があります。

■自動になっているのだから当然「黒」だと思いますよね。

黒又は別の色にするために、ここでしっかり色の指定をします。自動は「白」ですよ。

しっかり「黒」を選択してください。



これで「黒」でテキストが書けるようになります。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




シート上に吹き出しとかセルに無関係でコメントみたいなものを

入れたい時があります。

吹き出しを作ってみましょう。

タブの「挿入」から「図形」を選択して下の方にある「吹き出し」を選択しましょう。


シート上で適当な大きさで描画してください。下のようになりましたか？


エクセル2010ではデフォルトでは上記のようなブルー系に色になっています。

ここに文字入力すると「白」で表示されます。

このままではちょっと使いづらいのでこの吹き出しを加工します。



図形を右クリックして「図形の書式設定」を開いたところです。

「塗りつぶし」「線の色」「線のスタイル」大体この３つで好みの吹き出しが作成できると思います。



私がよく使用するのは「塗りつぶし」なし、「線の色」は黒、「線のスタイル」は

１ポイントですね。

それからこの図形を右クリックして「既定の図形に設定」すると、

このシート内ではこれがデフォルトになります。

マイクロソフトアクセスの使い方はこちら

人気ブログランキングへ





リストボックスの作り方



開発タブの「挿入」からフォームコントロールで「リストボックス」を選択します。



上のようにＢ列付近にリストボックスを配置しました。

ここにマウスを当てて右クリックしてください。



メニューから「コントロールの書式設定」を選択します。



ここでまた範囲をしているところがあります。赤矢印を押してからリスト範囲をドラッグしてください。



次に「リンクするセル」とありますね。これは選択したリストをどこに表示させるかを決定します。

一応ここを押してＤ２を選択しましょう。



上記の状態で「ＯＫ」を押してみましょう。

下のようにリストボックスが出来ましたね。今「小沢　真琴」を選択しています。

Ｄ２に「９」という数字が表示されていますね。

これは９番目のリストが選択されているという意味です。



しかし「９」なんて数字で表示されても困りますよね。

そこでちょっとした関数を使用します。

表示させるセルをＤ４にします。

Ｄ４に下のように「=INDEX(E2:E24,D2)」と入力します。

そうするとD4に「小沢　真琴」が出力されます。

他の名前を選択した時もその名前がD4に出るか確かめてください。



リストボックスとコンボボックスの違い

・コンボボックスは直接入力できる。リストボックスは一覧から選択するしかない。

・コンボボックスの選択肢は１つだが、リストボックスは設定により複数選択が可能。

大きな違いは上記くらいでしょうか。用途によって使い分けしてください。

私個人は圧倒的にコンボボックスを利用しています。

マイクロソフトアクセスの使い方はこちら

人気ブログランキングへ



コンボボックス

エクセルのシート上にコンボボックスを作ってみましょう。

下記のシートでE列に名前があります。

B2にこの名前のコンボボックスを作ります。

B2を選択した状態でデータタブの「データの入力規則」で「データの入力規則」を選択してください。




「設定」の「入力値の種類」でリストを選択して「ＯＫ」を押してください。



下の画面になれば赤矢印のところをクリックします。ここはリストの範囲を指定します。



データの入力規則という小さなボックスが表示されます。

この状態でＥ２からＥ２４までマウスでドラッグすると下記のように

「元の値」というところに「=$E$2:$E$24」という値が自動的に入力されます。

もちろん手入力で直接入力してもＯＫです。



そして選択が終われば右端の四角を押してください。



上記のようになりましたか。ここで「ＯＫ」を押します。



上記のようにコンボボックスが完成しました。








それから、なにか「リクエスト」があればコメント入れてください。「過去分でもっと詳細に。」でも結構です。

よろしくお願い致します。

マイクロソフトアクセスはこちら

人気ブログランキングへ





書式のユーザー設定

通常「セル」に書式設定する場合は下記の部分で「通貨」とか「日付」とか

「パーセンテージ」とか選択しますね。

しかしここに無い場合はどうしたらいいのか。

その場合は一番下の「ユーザー定義」を使用します。

ユーザー定義でもいくつかサンプルがあるのでその中に利用できるものが

あればそれを使用すればいいと思います。

しかしそれでもない場合は自分で作るしかありません。



例えば上記B4で「２０００」と入力しています。

それを「２，０００件」というように３桁区切りで最後に「件」を付けたいと思います。



「Ｇ／標準」と書かれているところに上のように「0,000”件”」と入力します。

これは3ケタ区切りで最後に「件」を入れるということです。

結果は下のように「2,000件」と表示されました。文字列を付けたいときは「”　”」で囲ってください。



上記以外でも様々な形で表示できますので色々試してみてください。

また文字列を前につけることも可能です。

「”合計”0,000”件”」とした場合は「合計2,000件」という表示になります。

この「ユーザー定義」は意外に役に立ちます。

私もよく利用しています。

マイクロソフトアクセスはこちら

人気ブログランキングへ



ピボットテーブルの作成・使い方

ピボットテーブルを使用すると簡単にデーターベースを統合・集計・分析できます。

例えば、「相手先別」の売上とか、「日付別」の売り上げ、「商品」ごとの販売状況など、

目的に合わせて情報を抽出することが出来ます。

【作成方法】



データーベース上の任意の場所にマウスでクリックしてセルを選択します。

こうすると自動的にデーターベースの範囲を取得します。

リボンの「挿入」タブの左端に「ピボットテーブル」というところがあるので、クリックしてください。

下記のようにデーター範囲を聞いてきますので良ければ「OK」を押してください。




下記のように他のシートにピボットテーブルの基本が作成されます。



下のように「レポートに追加するフィールドを選択してください」というところにチェックを

入れると下の「行ラベル」「Σ値」に項目が追加される。

またこの行ラベルの項目を「列ラベル」までドラッグすると列ラベルになる



いま「商品名」を列ラベルに移動したところです。シート内の表示で商品名は列になりましたね。



この段階で列に表示されている商品は各相手先にどれだけ販売されているか、

また販売数量の合計が表示されます。

いま、販売日にチェックを入れました。下のように販売日別の集計が表示されています。



また販売日をその上の「レポートフィルター」に移動すると左上に「販売日」「すべて」

というセルが出現します。

このB1セルで販売日が選択でき、その日の販売状況を確認できます。



下のように2015/1/6を選択するとその日の販売分だけ表示されます。



このようにピボットテーブルはデータを簡単な手順で様々な抽出条件設定で抽出できる機能です。

データを分析したい時は非常に便利だと思います。

マイクロソフトアクセスはこちら

人気ブログランキングへ





検索と置き換え

ワークシートで「検索」とか「置き換え」をしたいときは「ホーム」タブの

一番左にある「検索と置き換え」を使用します。



検索を選択すると下記のような表示が出るので、とりあえず「鉛筆」と入力して

「次を検索」を押してみましょう。



赤矢印の部分が選択されました。

「次を検索」を押すと次の「鉛筆」と記入されたセルが選択されます。



「すべて検索」を押すと下記のようになります。

マイクロソフトアクセスはこちら

人気ブログランキングへ




行・列の入れ替え

行で並んでいるのを列に並べ替えたい時があります。

その時は行・列の入れ替えを行えば入れ替え出来ます。

A1~A14まで日付が並んでいます。そこをすべて選択して、コピーします。



B1を選択して貼り付けオプションで「行列を入れ替える」を選択してください。

下のように日付が列表示されました。



【閑話休題】
ちょっと違う話を。
Data Recovery Wizard Freeというデータ復元ソフトのレビューを

お願いされたので少々記載してみます。

１、ＵＳＢメモリでの実験

１ＧのＵＳＢメモリで、すべて削除した後に通常スキャンを実行したところ、

２３８１個検出されました。

エクセル・ワード・pdf・パワーポイント・画像・音声・動画と色々ファイルを

見ていくと、完璧に再現されるファイルと全く再現できないファイルが

発生します。特にエクセル・ワードで再現されないファイルが時々発生。

フォルダーによって再現できるフォルダーと再現できないフォルダーが発生するようです。

「ディープスキャン」を実行すると５３６８個検出された。

再現される内容は同じ。

２、ＵＳＢメモリをクイックフォーマットした後にディープスキャンを実行した。

結果５３１６個検出。再現内容は同じ

３、ＵＳＢメモリを完全フォーマットした後にディープスキャンを実行した。

結果は２個検出。さすがに完全フォーマットはしっかり削除されているようです。

４、８ＧのＵＳＢメモリでデータ削除後スキャン、２個検出

　ディープスキャンを実行した結果７１９８個検出

５、同じ条件で別のPCで実行・・・７２０４個検出

６、同じＵＳＢメモリを別の復元ソフトで実行。

（私が数年前に購入した復元ソフトです）復元された個数は不明ですが、

ディレクトリ別と拡張子別に仕分けされます。

各ファイルの名前がうまく再現できないがファイルの中身は比較的再現できる。

７、２ＴＢのハードディスクで実験。このハードディスクは以前読み書きが出来なくなったもの。

「Data Recovery Wizard Free」ではかなりの数が検出された。

無料版なので途中で試用限界が来て終了した。

しかし驚くことに私物の復元ソフトでは１つも検出できなかった。

とりあえず無料版の試用限界が来たのでここまでとします。

結論としては完璧ではないがかなり使えるソフトであることが分かりました。

会社のホームページはこちら

製品の紹介ページはこちら