Excelが大好きだ!

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


スポンサードリンク

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…

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データベースを使用していますが、感覚的…