スポンサードリンク

sumproduct関数でのOR条件の指定

前回はsumifs関数でのOR条件の指定方法を紹介した。 www.excellovers.com 今回はみんな大好きsumproduct関数をsumifsの代替で使用する際のOR条件の指定方法を紹介します。 ※大好きだよね? OR条件公式 前回と同じ事例に対して3つの方法を作ってみた。 まず…

OR条件を含むSUMIFS関数

条件付きのSUM関数といえば SUMIFS関数だが、この関数はAND条件、つまり指定した条件を全て満たすものを合算する関数です。 ではOR条件、指定した条件の一部でも満たすものを合算することは出来ないのでしょうか? SUM関数&SUMIFS関数 通常の使用方法ではSU…

1つの目標。これからも

以上。 目標達成 よく言われるblog記事を数ヶ月の間に100個公開しましょうという教えですが、 当blogはこの記事で100記事に到達いたしました。 もちろん数ヶ月という短期間ではなく開設から3年弱かかっていますが、当初少なくともこのぐらいまでは頑張りた…

マイナスの割り算の答え(商)

先日このような記事を公開した。 www.excellovers.com その際にちょっと気になったことがあった。 Excelに商(割算の答え)を算出する QUOTIENTという関数があるのだが、この関数の返り値がちょっと疑問符がついてしまった。 Quotient関数 QUOTIENT (分子,分…

マイナスの割り算の余り

先日 ー4 ÷ 3を計算する機会があった。 この場合の余りはいくらだと思いますか? Excelとは直接には関係ないが、恐らく配列数式内の場合分けでこれから先、遭遇することがありそうなので調べてみた。 余りは? 自分の感覚ではこの場合の余りは ー4 ÷ 3 =…

Excelでビッグデータを乗りこなせ(8.テーブルの正規化のVBAコード)

前回はSQLiteで単一のテーブルで管理していたデータを、データベースの正規化を行い複数のテーブルに分割してみた。 www.excellovers.com 今回は実際に単一のテーブルを複数のテーブルに分割するコードを紹介したいと思います。 例のごとく早速コード Option…

マンゴー抽選にチャレンジ

VBA

フォロワーさんがこのようなものにチャレンジしているのを見かけた。 koroko.hatenablog.com 自分はあまりこういう演出系の処理をしたことがないのでチャレンジしてみた。 コード 例のごとく早速コード Option Explicit Sub マンゴー抽選() Dim MangoNum As …

文字列を区切り文字で区切って配列化する関数

ここ最近twitterで #エクセルクイズというタグで出題されている問題に色々とチャレンジしてきました。 その中で配列数式を使用することも多かったのですが、自分の思いもよらない使い方をされているのを見かけて勉強になりました。 今回はその集大成として文…

Excelでビッグデータを乗りこなせ(7.テーブルの正規化)

前回はExcelとは異なるデータベースの独自要素を盛り込みつつSQLiteデータベース・テーブルを作成した。 www.excellovers.com 今回はデータベースの独自要素、「正規化」を考慮してデータベースを分割することにチャレンジしてみます。 正規化前のテーブル構…

数値がマイナスの時に0と表示する表示形式

Excelには「表示形式」という機能があります。 どういうものかというと このようにセルの値の見た目を変更するための機能です。 この表示形式の機能を利用するとセルの値が、プラスの場合・マイナスの場合・ゼロの場合で書式を変更することが出来ます。 正・…

オートフィルタで見えている範囲(可視セル)だけで自動連番を作る方法

先日Twitterでこのようお題が出されていました。 フィルターを設定した表について、表示されている行のみに通し番号を振ってください。・フィルターの条件を変更すると番号も変わります。・式はA2セルに入力し、下方にコピーしてください。#エクセルクイズ p…

Excelでビッグデータを乗りこなせ(6.SQLiteデータベースの作成)

前回はExcelとは異なるデータベースの独自要素を見てみた。 www.excellovers.com 今回からはその点を踏まえつつ実際にSQLiteデータベースの作成をしてみたいと思います。 ExcelでSQLiteデータベースを操作 上記を実現するためにExcel for SQLite使用する。 …

Excelでビッグデータを乗りこなせ(5.データベースの独自要素)

ADODBを利用してCSVファイルを操作する方法を検証した。 www.excellovers.com 今回からはそのCSVを取り込むためのSQLiteデータベースの設計・作成をしてみようと思います。 データベースならではの要素 今でもSQLiteデータベースを使用していますが、感覚的…

Excelでビッグデータを乗りこなせ(4.ADODBでCSV取り込みの準備)

前回でSQLiteへCSVファイルを取り込む方法をADODBで行うことを決定した。 www.excellovers.com 今回からADODBを利用したCSV取り込みの方法を紹介します。 会計ソフトの代わりに家計簿ソフト 今回のコードは会社で使用している会計ソフトから出力したCSVファ…

Excelでビッグデータを乗りこなせ(3.CSV取り込みの試行錯誤)

前回で使用するデータベースをSQLiteにすることを決定した。 www.excellovers.com 今回からCSVファイルの取り込み方法について試行錯誤していきたいと思う。 現状の取り込み方法 ・Querytables.addでCSVをExcelに取り込む ・取り込んだデータをSQLのInsert分…

Excelでビッグデータを乗りこなせ(2.データベースを決める)

前回華々しくビッグデータの大海原に漕ぎ出したわけですが、大海原を乗り越えるための船がまだ決まっていません。このままではあえなく溺れ死んでしまいます。 ビッグデータを乗り超えるための船 データベースです まずはこれを決めることから始めたいと思い…

Excelでビッグデータを乗りこなせ(1.データベースでやりたいこと)

私は職場で経理を担当していて、月次の資料を作る際に ・会計ソフトから仕訳データをCSVで出力 ・出力したデータをExcel VBAでSQLiteにインサート ・Excel VBAからSQLを使用してSQLiteから集計後のデータを出力してExcelへ転記後、関数を使用してフォーマッ…

集計表をダブルクリックすると内訳を表示するようにしてみた

VBA

先日、会社で作ったVBAの機能に我ながらテンションが上ったので取り敢えずご紹介だけ。 完成品 このようなデータを このように集計している表があるとします。 カツの売上が先月より良いけどなんでだろうと思ったとする。 そこで5月のカツのセル(C3)をダ…

子供のために作った筆算学習ツールを割り算対応にした

VBA

先日作成したこちらの筆算の学習ツール。 www.excellovers.com 足し算・引き算・掛け算までは出来ていたのですが、勢いに任せて割り算対応もしてみました。 その過程でクライアント(嫁)から次々と要望が出てきましたが、何とか全て対応いたしました。 その…

子供のために筆算学習ツールを作ってみた

VBA

以前に100枡計算ツールを息子のために作ったのだが、学校で筆算を習うようになったので筆算のツールを作って欲しいとせがまれてしまった。 子供のリクエストとあらば答えねばなるまい! 完成品 完成品はこちらです。 足し算の動きはこんな感じです 引き算 掛…

他社に送る書類からコメントを一時的に退避させる(3.退避の改修)

前回・前々回でExcelからコメントを一覧にして抽出して別ブックに退避して削除、退避したコメントの復元までを一応完成させた。 www.excellovers.com www.excellovers.com しかし実際に使ってみたりblogへのコメントを頂いて「コメントの退避」の内容を少し…

他社に送る書類からコメントを一時的に退避させる(2.復元)

前回はコメントを全て別ブックに退避させてから、全て削除するところまでを実現させました。 今回は退避させたコメントを元のブックに復元させるところまでを検証してみます。 コード Sub CommentInport() Dim FilePath As Variant Dim myWB As Workbook Set…

他社に送る書類からコメントを一時的に退避させる(1.退避)

資料を他社に送る時に気をつけないといけないのが コメントの記載です。 自社内ならともかく他社の方に見られるとちょっとまずい内容が書かれていることが多々あります。 送付用にブックをコピーしてコメントを削除してから送信したりしていますが、この時期…

保護されたセルの数式を確認する方法

どうやって機能を実現しているんだ? 機能をトレースしようと思った時にセルに保護がかかっているために、数式を確認するどころか、セルの選択自体が出来ない。 そんな時の対処方法をご紹介。 取り敢えず色々選んでみようとする 今回の例でいうとセルJ1、J2…

単一セル内でドロップダウンリストを多重に連動させる方法

先日このようなツイを見かけた。 Excelクラスタの皆さん、単一セル内でドロップダウンリストを多重に連動させる方法分かりますか…これ当社で作った人いて、スゲーてなってるんですけど既にその人退職して行方知れずに…— しいこふは現実化する (@i_siecof) Ma…

VBAでSQLを使用する際にListObjects(テーブル)をFrom句に指定する方法

VBA

ADODBを利用することでExcelのデータをSQLで操作することが出来るようになります。 SQL文のFrom句でワークシート名を指定する方法は知っていたのだが、名前定義の範囲やテーブルを指定する方法を知りたかったので調べてみた。 ワークシートの場合 まずは基本…

各種ファイル検索(Dir関数・FileSystemObject・FindFirstFile関数)比較(ファイル検索比較7)

前回まででDir関数・FileSystemObject・FindFirstFile関数と3種類のファイル検索を検証してみた。 今回はそれぞれの検索方法の特徴や処理速度などを比較検証してシリーズの締めくくりとしたい。 処理速度 まずは検索スピードについて検証してみる。 条件1 …

サブフォルダ内を含むWindows API(FindFirstFileW)を利用したファイル検索(ファイル検索比較6)

前回はFindFirstFile関数を利用したファイル検索を検証してみた、がその際にポインタがわからないとtwitterでつぶやいたところ、色々ご指導頂いたりサイトをご紹介頂いた。 www.excellovers.com ポインタとは関係ないがご紹介頂いたサイトでFindFirstFile関…

サブフォルダ内を含むWindows API(FindFirstFile)を利用したファイル検索(ファイル検索比較5)

前回までにDir関数とFilesystemObjectを利用したファイル検索を検証してみた。 www.excellovers.com www.excellovers.com 今回は最後にWindows APIの1つであるFindFirstFile関数をVBAから利用してファイル検索を実現してみたいと思います。 Windows API関数…

サブフォルダ内を含むFileSystemObjectを利用したファイル検索(ファイル検索比較4)

VBA

前回はFileSystemObjectを利用して1つのフォルダ内のファイルを検索するコードを検証してみた。 www.excellovers.com 今回は再帰処理を利用して指定フォルダ内だけでなくフォルダ内フォルダの検索を実現してみようと思う。 再帰処理でフォルダ内検索 Dir関…