今日を乗り切るExcel研究所

Excel に働かされていませんか

【Excel LAMBDA】文字列操作編(その2)【ユーザー定義関数集】

文字列操作関数集その2 LAMBDA 関数を使った数式によるユーザー定義関数(カスタム関数)として、実用的でコピペで使える実装例をまとめていきたいと思います。 使い方は以下の記事を参照してください。 www.shegolab.jp 本記事では文字列操作関連のツール…

複数リストから全ての組み合わせデータを作るVBSとPowerShellのスクリプト

今回も、しつこく組み合わせデータを生成します。今回は VBS と PowerShell を使います。 組み合わせ大爆発 以前、リストのファイルから組み合わせデータを生成するバッチとクエリを作成しました。 複数リストから全ての組み合わせデータを作るバッチとクエ…

【Excel LAMBDA】文字列操作編(その1)【ユーザー定義関数集】

文字列操作関数集その1 LAMBDA 関数を使った数式によるユーザー定義関数(カスタム関数)として、実用的でコピペで使える実装例をまとめていきたいと思います。 使い方は以下の記事を参照してください。 www.shegolab.jp 本記事では文字列操作関連のツール…

【Excel LAMBDA】ユーザー定義関数集【企画】

LAMBDA 関数とユーザー定義関数 2022年2月、Microsoft 365 版の Excel に、「LAMBDA関数」とそれに関連するいくつかのヘルパー関数が導入されました(「LAMBDA」は「ラムダ」と読みます)。 今回のアップデートは単に、また新たな関数の追加された、だけには…

Excel 方眼紙を解消したい

今回は、Excel 方眼紙上にセル結合で作られた表からデータを取り出しやすくするための整形方法を調べます。 Excel 方眼紙からデータを取り出したい セル結合でガチガチに固められた Excel 方眼紙というのはやっかいです。 そこから単純にデータを取り出すだ…

Excel ファイルの保護ビューを外したい

今回は、Excel ファイルの「保護ビュー」を外す方法について調べます。 保護ビュー、再び このご時世のテレワークにともなって、Excel で「保護ビュー」とかいう何もできない状態に遭遇することも増えたのではないでしょうか。 保護ビュー 注意―インターネッ…

【VBA】業務用語で計算式を表したい【100本ノック】

VBA

今回はエクセルの神髄様の Twitter 企画 「VBA100本ノック」の 88 本目、クロスABC分析をやってみます。 お題:VBA100本ノック 88本目:クロスABC分析作成 #VBA100本ノック 88本目「data」と「商品マスタ」から「クロスABC」を完成させる。・仕入金額=仕入単…

【VBA】階層見出しをデータ構造に変換したい【100本ノック】

VBA

エクセルの神髄様の Twitter 企画 「VBA100本ノック」の 42 本目になります。 お題:VBA100本ノック 42本目:データベース形式に変換 #VBA100本ノック 42本目画像1のように「階層」シートに階層を表したデータがあります。これを画像2のように「階層DB」シー…

【VBA】固定長テキスト形式でデータを保存したい【100本ノック】

VBA

今回も今さらですがエクセルの神髄様の Twitter 企画 「VBA100本ノック」の 65 本目を取り上げます。 お題:VBA100本ノック 65本目:固定長テキスト出力 #VBA100本ノック 65本目固定長データの出力です。「フォーマット」シートに従い、2行目以降をテキスト…

複数リストから全ての組み合わせデータを作るバッチとクエリ

今回は、複数リストから全ての組み合わせデータを作る Batch スクリプトと、Power Query のクエリを作成します。 組み合わせのつらみ 以前、Excel を使って複数リストからの組み合わせデータを作成する方法について紹介しました。 複数リストから全ての組み…

【VBA】ワークシートのデータを Power Query で加工したい【100本ノック】

VBA

続けてエクセルの神髄様の Twitter 企画 「VBA100本ノック」の 74 本目を取り上げます。 お題:VBA100本ノック 74本目:1シート複数表をDB形式に変換 #VBA100本ノック 74本目「売上」シートには、A列B列に取引先コードと名称があり、その下に見出し行から始…

【VBA】条件を満たす行のみセル内容を一括変更したい【100本ノック】

VBA

今更ながらエクセルの神髄さんのTwitter企画 「VBA100本ノック」への便乗記事、6本目の課題です。 お題:VBA100本ノック 6本目:セルに計算式 #VBA100本ノック 6本目画像のようにA1から始まる表があります。D列にB列×C列の計算式を入れてください。ただし商…

【VBA】空文字列セルを空白セルにクリアしたい【100本ノック】

VBA

遅まきながらエクセルの神髄さんのTwitter企画 「VBA100本ノック」の便乗記事、 5 本目課題への解答例です。 お題:VBA100本ノック 5本目:セルの計算 #VBA100本ノック 5本目画像のようにB2から始まる表があります。B列×C列を計算した値をD列に入れ、通貨\の…

複数キー列でパッパと並べ替えしたい

今回は、複数のキーによるデータの並べ替えを素早くやる方法がないか調べます。 古きよきダイアログ 複数列のキーを切り替えながらソート(並べ替え)したいことはよくあります。 データをざっとチェックしたいときなど、組織や期間、カテゴリといった階層的…

セル結合の解除で開いた空白セルに値を埋め戻したい

今回は、セル結合を解除したあとの空白セルに値を一括で入力する方法を調べます。 セル結合しましたが、何か? 人は何故にデータのセルを結合するのでしょうか。 ひとえにそれは「同じ値のセル範囲をまとめたい」からでしょう。 であるのなら当然、逆の「セ…

テキストを1文字ずつにバラしたい

今回は、テキストデータの文字列から1文字ずつをマス目のセルに入力する方法について調査します。 ネ申エクセル セルを文字マスとして使うというシートに遭遇することがあります。 入力欄が、セル幅を狭めて作った文字マスになっていて、名前や文章を1セル1…

【VBA】ファイルを高速に検索したい【100本ノック】

VBA

エクセルの神髄さんのTwitter企画 「VBA100本ノック」への便乗記事、飛びとびですが、今回は66本目。 お題:VBA100本ノック 66本目:全サブフォルダからファイルを探す #VBA100本ノック 66本目ブック自身のあるフォルダ以下の全サブフォルダを検索し、自身と…

【VBA】入力セルをクリアしたい【100本ノック】

VBA

エクセルの神髄様の Twitter 企画「VBA100本ノック」のノック4本目の解答例です。 お題:VBA100本ノック 4本目:セルの消去 #VBA100本ノック 4本目画像のように1行目に見出し、A列に№が入っています。この表範囲の一部には計算式が入っています。(画像の最…

【VBA】Excel で作成した表を HTML に変換したい【100本ノック】

VBA

今回もエクセルの神髄様の Twitter 企画「VBA100本ノック」の便乗記事です。 今回はその94本目をやります。 ついでにはてなブログで使える Markdown 表組へ変換するマクロも作ります。 お題:VBA100本ノック 94本目:表範囲からHTMLのtableタグを作成 #VBA10…

【VBA】Excelファイルを開かずにデータを取り出したい【100本ノック】

VBA

今回もエクセルの神髄様の Twitter 企画「VBA100本ノック」の便乗させていただきます。 今回やってみるお題はその93本目です。 外部参照のスピルを使ってみます。 お題:VBA100本ノック 93本目:複数ブックを連結して再分割 #VBA100本ノック 93本目「月別」…

【VBA】横持ちデータを縦持ちに変換したい【100本ノック】

VBA

今回もエクセルの神髄様の Twitter 企画「VBA100本ノック」の便乗記事です。 今回のお題はその25本目です。 配列数式を使ってみます。 お題:VBA100本ノック 25本目:マトリック表をDB形式に変換 #VBA100本ノック 25本目画像1のように「売上」シートに横に日…

【VBA】時間の計算をしたい【100本ノック】

VBA

今回もエクセルの神髄様の Twitter 企画「VBA100本ノック」に便乗させていただきます。 今回はその91本目への解答です。Date 型の使い方もまとめました。 VBA100本ノック 91本目:時間計算(残業時間の月間合計) #VBA100本ノック 91本目「勤怠」に複数月の…

【VBA】セルの値によって行を削除したい【100本ノック】

VBA

エクセルの神髄様の Twitter 企画「VBA100本ノック」の問題への解答記事です。 今回はその10本目になります。Sort を使います。 お題:VBA100本ノック 10本目:行の削除 #VBA100本ノック 10本目画像のように「受注」シートに今月の受注データがあります。受…

【VBA】表から特定のデータのみを別シートに抽出したい【100本ノック】

VBA

エクセルの神髄様の Twitter 企画「VBA100本ノック」に便乗した解答記事です。 今回はその9本目をやります。AdvancedFilter を使ってみます。 お題:VBA100本ノック 9本目:フィルターコピー #VBA100本ノック 9本目「成績表」シートに5教科の成績とG列に合否…

【VBA】別シートにセル範囲をコピーしたい【100本ノック】

VBA

エクセルの神髄様の Twitter 企画「VBA100本ノック」に挑戦してみたいと思います。 ますはその1本目から。 FillAcrossSheets メソッドを使ってみます。 お題:VBA100本ノック 1本目:セルのコピー #VBA100ノック 1本目「Sheet1」のA1:C5のセル範囲を、「Shee…

2つの表を比較して完全一致するかを確認したい(2/2)

データ範囲の一致を確認する方法の続きです。 2つのセル範囲が一致するかを確認するには 前回の記事はこちらです。 www.shegolab.jp フィルターオプションを使う 「フィルターオプション」を使って、行ごとの比較による絞り込みで差異の有無を確認してみた…

2つの表を比較して完全一致するかを確認したい(1/2)

今回は、2つのデータ表を比較して、完全に一致するかどうかを手軽に確認する方法がないか調べます。 確認しました! Excel で2つの表やデータ範囲を比較したいことはよくあると思います。 ところが Excel を探してもそのための機能が見当たりません。 ネット…

Excel でタテのデータをヨコにしたい【スピル編】

今回は、スピルを使って表組み換えの問題を解決できないか調べます。 スピルで表の組み替え 「表の組み替え」というのは既存の表データの列や行の向きを変えたり、配置やまとめ方等の構造を変更するという、手作業でやると非常にしんどい操作です。 数式を駆…

Excel 図表を図として保存したい【Microsoft 365 版】

今回は、ついに Excel にサポートされた「図として保存」を使い倒します。 エクセルさん、ありがとう! なんと、Excel でも画像や図形で「図として保存」機能が使えるようになりました! 図またはその他のグラフィックスを別のファイルとして保存する - Offi…

Excelファイルをどこからダウンロードしたのか思い出したい

今回は、ダウンロードした Excel ファイルから、ダウンロード元の場所を取得する方法を調べます。 テレワークしてますか ご時世がら、在宅での仕事が増えている今日この頃、いかがお過ごしでしょうか。 筆者的にはなかなか快適で、オフィスでよりもはかどっ…