Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

SQLでデータを抽出するユーザー定義関数を作ってみた その2

先日SQLでデータを抽出するユーザー定義関数SQLSearchを作成した。 www.excellovers.com その後テストした際に追加したいと思った機能を追加してみた。 その際に風柳さん@furyutei には貴重なアドバイスを頂きました。 ありがとうございます。 例えば ADODB.…

SQLでデータを抽出するユーザー定義関数を作ってみた

Office365やExcel2019で新しく搭載された「スピル」と言う機能があります。 スピル機能を使うと複数の答えを返す関数を従来よりも圧倒的に簡単にセルに展開することが出来ます。 この機能と相性が良いのでは思っていた、ExcelのデータをSQL文で集計した結果…

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

以前にSQLのFrom句にテーブル名を指定する方法を紹介した。 www.excellovers.com 上記とは別の方法をtwitterでフォローさせて頂いている方が以前に紹介されていたのを見かけたので備忘録として記事にさせて頂く。 コード select * from [Range("個人情報").l…

Excelの勉強に皆さんが参考にされた書籍・サイト

VBA

先日このようなtweetをしたところ、30名程の方からご回答を頂きました。 「Excelをどういうきっかけで、どうやって勉強しましたか?」のまとめを公開しました!先日緩募したテーマにご回答頂いた内容を1箇所にまとめました。「Excelをどういうきっかけで、ど…

PostgreSQL・SQLite・Excel ADODBで使えるSQL文をまとめてみた

先日、こちらの書籍でSQL文を勉強してみた。 SQL 第2版 ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)作者:ミック発売日: 2016/06/17メディア: 単行本(ソフトカバー) その際にPostgreSQL・SQLite・Excel ADODB(ADODB経由でExcelをデー…

編集中の文字列選択を(ちょびっとだけ)楽にするショートカットキー

選択セルを編集モードにするショートカットキーは「F2」です。 選択セル内の文字列を全て選択するショートカットキーは上記「F2」から「Ctrl + Shift + Home」 文字列選択のショートカットキーはコレぐらいかと思っていたのですが、別のショートカットキーが…

「重複の削除」唯一の使い途

誤ったデータを返すと評判の「重複の削除」機能ですが、個人的には結構使っている。 重複の削除機能は使っちゃ信用できないから使っちゃ駄目よって言われるけど、実は結構使っている。ただ使い途ははほぼ一択で、データをsumifsで集計するときの検索条件群を…

「ウィンドウ枠の固定」をちょっとだけ便利にしてみた

「ウィンドウ枠の固定」を利用すると表を下にスクロールしていっても、見出し行が隠れずに済むので、今度の列のデータを見ているか迷わずに済みます。 大変便利な「ウィンドウ枠の固定」ですが、ほんのちょびっとだけ不便な点があるんですよね。 一旦解除し…

オートフィルターがかかっている列を検索する機能にテーブルも検索できる機能を追加した

前回に引き続き機能の追加のお話。 www.excellovers.com オートフィルターと同じようにデータを絞り込む機能がテーブル機能にも備わっている。 しかし現状の機能のままではテーブルのフィルタがかかっている列を抽出することが出来ない。 さてここでシンプル…

オートフィルターがかかっている列を検索する機能を改良した

先日、タイトルまんまのアドイン機能を作成した。 www.excellovers.com 公開したところtwitterフォロワー様から以下の様の意見を頂いただので早速(パクって)改良してみた。 私も職場では横に長~~~~いテーブルを多用しているんです。でも気が付きませんで…

オートフィルターがかかっている列を検索する機能を作った

列が何十・何百とあるExcelシートでどこにオートフィルターがあるか迷ったことはないだろうか? 文字通り、「あれ、この辺りだと思ったけど」と右往左往したことはないだろう? 私はある。 現在進行系で悩まされている。 しかもまだ1・2ヶ月はこのシートと…

Thisworkbook.PathがURLを返すようになった時の対応

Thisworkbook.Pathはこのコードが書かれたExcelブックが保存されているフォルダのフルパスを返すプロパティです。 例えばこのような感じです。 ところが先日、このようなフルパスを返してくれなくなりました。 何かインターネットのURLが返ってきてるように…

Office365契約の流れ

前回flickrを止め、Onedrive(つまりOffice365)を契約することを決心いたしました。 今回はOffice365の契約からOffice365インストールまでの流れを紹介します。 Office365サブスクリプション契約まで サブスクリプションプランの選択 今回私はOnedriveの1TBの…

写真保存サービスをflickrから乗り換える

ながい事写真保存にはflickrを利用してきましたが、今回の更新をもって別のサービスに乗り換えようと思い、色々なサービスを調べてみました。 なぜflickrから乗り換える? きっかけは料金プランの価格変更です。 利用を始めた頃は2年間で44.95$という価格で…

CSVファイル閲覧・編集用テキストエディタ比較

CSV

CSVファイルの閲覧・編集にはgPadを使用しています。 が、特に大きな不満があるわけではないのですが、よりよいCSVファイル用テキストエディタがないものかと思いたち、何点か比較してみることにしました。 CSV用テキストエディタに求める条件 ・数百MBのフ…

オートフィルタ適用状態の最終行の取得方法

最終行の取得方法というと Range("A" & Rows.Count).End(xlUp).Row のようなコードを使用するが、オートフィルタ適用時には思ったような答えを返さない。 このような表があるとする。 オートフィルタ設定前に確認すると最終行は11である。 オートフィルタ…

HELPを開いた時に動画が再生されることを防ぐ方法

まずはこちらをご覧頂こう。 SUM関数の詳細を調べようとしたときのこと なんで動画がブラウザで表示されるんだ! テキストの情報で良いんだよ! HELPに戻っても… なんだ、この薄い情報は。 役に立たない。 何とかならないものか… オフライン 1箇所設定を変…

未入力は計算上は0

フリもなく早速検証です。 ​ B2~B4セルは未入力 B5セルは 半角スペース B6セルは =”” という式が入力されています。 表を見ると未入力は 0 として計算されています。 一方空白の2種類は0ではなく文字列として認識されているので計算でエラーが発生してい…

#########になるのは

最近になって数値で不思議な表示になる 指数表示は数値の桁数が12桁以上の場合になるということを知りました。 しかしそれ以外にもExcelは数値の表示をおかしくすることがあるんです。 指数は12桁より少なくても 10桁の数値が表示されていますが、列を狭める…

ADODBでExcelを操作する時の列毎の型判定の基準

VBA

Excelでデータ集計をする際には真っ先にSQLでの集計を連想してしまいます。 しかし操作する対象がデータベースではなくExcelなので、思わぬところで蹴躓いてしまいます。 今回はデータの型判定についてです。 データベースの型 Excelでも書式設定等で列毎の…

ExcelでADODBで外部結合をしたときに集計結果が倍で表示された原因

ADODBを使ってExcelをデータベースのように扱うのは、SQL文を書くだけで集計出るのでとても便利ですね。 そんなデータベースで集計の指示を出すSQL文で今回始めて Excel VBAでデータベースの結合処理を行ってみました。 やろうとしたこと 今回ミスったことを…

一風変わった?動的配列の宣言方法

動的配列を宣言する時はどうしていますか? Dim Var() as variant Redim Var(1 to 3) 上記のように変数名の後に()をつけて宣言し、 Redimで要素数を指定してから使用するのではないでしょうか。 しかし今回長年見慣れたこの方法とは違う宣言方法に出会いま…

ExcelとExcel VBAでの改行の違い

VBA

Excel VBAにおいて改行というと vbCrLf や vbLf が紹介されている。 これらの定数はどんな違いがあるのだろうか? 改行に関わる定数 定数 値(文字コード) 内容 vbCrLf Chr(13) + Chr(10) キャリッジ リターンとライン フィードの組み合わせ vbCr Chr(13) …

オートフィルタの設定どうやってますか?

皆さんオートフィルタ使ってますか? ピボットテーブルを使わない人でもオートフィルタはわかりやすいのか使ってますよね。 皆さんオートフィルタを設定するときどうやってますか? 「上のメニューから選ぶんだろ?」 その通りなんですが、今回のネタはその…

テーブルのあるフィールドのあるレコードだけ数式を変える方法

テーブル機能。便利ですよね。 テーブルを設定すると色々とExcelが気を使ってくれるようになるのですが、その細かい気の使いようにちょっと困った自体に遭遇しました。 その現象と対処法をご紹介 数式の自動入力 このようなテーブルに税込みの金額を算出する…

オートフィルタにフィルタ適用状態のままテキストフィルタ検索条件を追加する方法

データの抽出や分析に必須の機能オートフィルタ。 20年近く使ってきて今更ですが個人的にビックリの機能を先日発見してしまったのでご紹介。 オートフィルタに条件を追加する方法は? 例によってなんちゃって個人情報のデータである。 現時点では都道府県が…

テーブルの列名参照を含む式をオートフィルした時に列がずれる現象への対応方法

テーブル機能便利ですよね。 Excel2007以降での最高の追加機能だと思います。 しかし先日このような場面に出くわした。 ※データは例のごとくなんちゃって個人情報より テーブルの列名参照は名前定義みたいなもので絶対参照だと思いこんでいたので、このよう…

ADODBを利用して指定した件数ずつ新しいシートにデータを転記する方法

いつも勉強させていただいているinfomentさんのblogで以下のような記事を公開されていました。 infoment.hatenablog.com ADODBにまさに上記のような機能があったはずと思いだして自分なりに作ってみた。 早速コード Sub ページ分割() '参照設定でMicrosoft A…

sumproduct関数でのOR条件の指定

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

OR条件を含むSUMIFS関数

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