Excel ヒント集

Excel の列を数字表記にした表の生成

新年明けましておめでとうございます。本年もよろしくお願いいたします。 本年最初の記事は、生成するExcel表の列を数字表記にしたいときに関する話題です。 通常はアルファベット表記の列ですが、Excelの [ファイル] > [オプション] > [数式] から、列を数…

背景を透過した(透明にした)画像を使用する

例えば印鑑などの画像を、xoBlos ひな形シートに置いておいて帳票を生成したい場合、背景を透過した(透明にした)画像を使用したいときがあります。 背景を透過した画像を作成するには、世間に出回っているツール(有料、無料)が多くありますので、そのよ…

Designer:セルに色を付けるときの留意点

Designer:行に色を付ける https://xoblos.hatenablog.jp/entry/2022/06/10/110526 上記の記事で、Excel「条件付き書式」メニューを使ったセルへの色付け手法を紹介しました。この例のように、オレンジや黄色といった原色系でセルを塗る場合はいいのですが、…

白丸 ○(記号)と〇(漢数字)、そして黒丸●

PCで「まる」と入力して変換する白丸記号ですが、○(記号)と〇(漢数字)と2種類があるので、チェックシートなどで確認マークとして使用する場合は注意が必要です。 下図シートに入力された白丸記号では、NO 3, 6 が〇(漢数字)であり、その他が○(記号)…

Excel:グループ化でテンプレートを作成する

グループ化とグラフでデータ分析の新次元へ https://xoblos.hatenablog.jp/entry/2023/01/08/174259 上記記事で解説したExcel「グループ化」ですが、詳細行(明細行)と集計行があり、 縦軸に分類項目指標(科目・補助科目、組織項目、商品分類項目など)が…

数式・関数を使って日付をセットするとき

「値確定セル」、「編集後削除する列」キーワードなどをご利用の皆様。 ひな形シートのF2セルに、日付形式の「販売年月日」などをセットし、他のセルから関数を使用して条件判定後に値をセットし分けて出力するときのことですが。 =IF(条件式, "", F2) 入力…

Excelオートフィルで表設計(テーブルデザイン)してみよう (2)

前回の記事に、1ヵ月分を3列で表現した表を、Excel オートフィルで設計する例を載せました。 この「予算」「実績」「達成率」の三つ組を、横軸(列方向)ではなく、縦軸(行方向)に変えて表設計してみましょう。 xoBlos の場合、1ヵ月分を3列でも、このよう…

Excelオートフィルで表設計(テーブルデザイン)してみよう (1)

次の記事で、 表の設計にはExcelのオートフィルが活躍するhttps://xoblos.hatenablog.jp/entry/2022/06/29/125123#label Excelのオートフィルを活用して、縦軸に商品1~商品9、横軸に1月~9月という形式の表を設計する例を載せました。 1ヵ月分を3列で表現し…

九九表を例にしたクロス表チュートリアル (4)

九九表を例にしたクロス表チュートリアル (3)https://xoblos.hatenablog.jp/entry/2022/06/08/225721の続きです。 九九表の形式をビジネスに応用してみる 縦軸に9個の商品名、横軸に1月から9月までの9個の月。交差点(セル)に販売数量。ビジネスシーンには…

Designer:行に色を付ける

目標(GOAL) 入力データに、小計や総合計がデータ行として入っていることがあります。 このようなデータから新たなレポートを作成するときに、小計や総合計の行に色を付けたい場合。 ひな形シートと「条件付き書式」設定 「Excel表を生成」手順の「ひな形シ…

九九表を例にしたクロス表チュートリアル (3)

九九表を例にしたクロス表チュートリアル (2)https://xoblos.hatenablog.jp/entry/2022/06/03/124619の続きです。 Excel で九九表を作成する 参考記事 Excel:オートフィルの基本https://xoblos.hatenablog.jp/entry/2017/01/27/161031 クイズ:この Excel …

Excel が扱える行数制限、列数制限は?

Excel 2003 行数制限 65,536行(2の16乗) 列数制限 256列(2の8乗) Excel 2007 以降 行数制限 1,048,576行(2の20乗) 列数制限 16,384列(2の14乗) Excel 2007 以降で扱える数には驚かされますが、当然、数十万行を超えるデータを処理するにはハイスペッ…

Excel 年代記

日本の企業・組織で、Excel が本格的に使われ始めたのは、Windows 95 のときの Excel 95 あたりではないでしょうか。その後の、Excel の変遷を年表にまとめてみると、あのとき、あそこで、あんなことをしていたなぁ、といった感慨を覚えられるのではないでし…

Excel:列番号を調べる

次の記事にも書いていますが、 セル参照を行番号、列番号指定方式(オフセット方式)にするとメンテナンスしやすい http://xoblos.hatenablog.jp/entry/2017/07/22/204049 xoBlos で指定するセル参照方式としては、圧倒的に下記のどちらかが多いと思います。…

チェックボックス、ラジオボタンの値を抽出する

目標(GOAL) 申込書や申請書、アンケート用紙などフォーム用紙の設計にあたって肝心なのは、回答者の視点に立った、回答しやすい明快な質問文や選択肢の設定であり、ユーザーが面倒でない量の記入フォームの準備にあります。チェックボックスやラジオボタン…

Designer:改行記号以外の制御コード記号も削除する

前回の記事で、改行コード記号を削除する方法を記載しました。 Designer:改行記号を抜くhttp://xoblos.hatenablog.jp/entry/2019/12/16/153254 この指定で項目中の改行記号を削除することができますが、他システムからの連携データの中に、16進コード08(バ…

Designer:『シート分割』の注意事項まとめ

当然シートの仕様が問われる 前回記事で、シート分割の設定例を挙げました。便利な機能ですが、Excel のシートを操作する以上 xoBlos 側だけでなく、Excel 側の仕様も意識しなければなりません。 xoBlos 側‥‥①入力データ(.xml)内のシート分割項目は、最優…

Designer:複数の CSV を一括抽出

繰り返しの設定は必須 抽出元が複数の Excel である場合は、ワイルドカード「*」の曖昧指定と「%BOOK%」「%SHEET%」の活用ですんなり対応出来ました。では、複数の CSV はどうでしょうか。 手順種『変換(CSV→抽出)』は、『a.入力ファイル名(必須)』項に…

Designer:シート名に全角スペースは要注意!

シングルクォーテーションが判断出来ない 生成時、高速エンジンを使用すると結果のブックが破損することがあります。前回記事でも示しましたが、これは大抵のケースで高速エンジンの未使用にて解決します。高速エンジンの未使用は "Excel ならではで動かす" …

Designer+Excel:「INDIRECT」関数の活用

見た目は複雑でも強い味方 生成の雛形シート上に Excel の関数をいれておくと、値の操作が楽になる場合があります。しかし時として「雛形シート」や「行/列ひな形」「エリアひな形」のような処理後に消えて仕舞う領域上を参照しなければ成らない式も、設定…

Excel:「SYLK」ファイルのエラー

冒頭項目「ID」の落とし穴 ‥‥筆者は人にサンプル業務を提示する時や、急ぎのテストデータを作る時、上図のような簡単なものを用意します。一先ず、これだけあれば大凡のパターン(単票/クロス表/シート分割/ブック分割/集計/並べ替え/エリア生成/対象…

Designer:整形記号「#U」のある行/列の書式設定

生成先のセル書式は無関係!? 前回記事で、「#U」は直前のキー値を保持することをお話しました。ではこのキー値を生成している部分の書式変更は、どのように行うのでしょうか。試しに、普段通りの方法で "事業所の文字色は赤" という要件をこなしてみましょ…

Designer:生成キーワード『日時指定』

基本文字列なのを忘れずに! 私達が普段 Excel に日付の値を入力する時は、そのセルの書式に応じて、入力時点の値から自動変換されるパターンがありますね。下図のような例があります。 日付の手入力は、xoBlos の生成処理で XML から渡される値がセルにあて…

Designer:不定な数の項目を連結する

最初の項目が定まらない 抽出と生成の両方にあるキーワード『取得データの組合せ』。複数の項目の値同士、或いは固定の文字列を連結する要件で活躍します。しかしその内、値が無い(空白の可能性がある)項目が対象と成った場合、文字列の連結はどのようにす…

Excel:結合セルへの書式(罫線)設定

各セルは独自の情報のみ保つ 生成の際、雛形行/列に予め条件付き書式設定を行えば、無論その書式情報や条件式はコピーされます。背景色/文字色は、上図のような設定で難なく反映出来ますね。 では上図に加えて、更に罫線で囲って強調させるという要件が来…

Designer+Excel:セルの名前定義

セル番地とは? 制御シートのリファレンスマニュアル上、各キーワードの設定値に「セル参照」と示されているものがあります。これは、セル番地を指定することが出来るという意味です。 セル番地とは、その考えを持つ Excel 上のセル一つ一つが固有に持つ位置…

Excel:データの重複チェック

関数式で示そう xoBlos の中間処理は慣れてきましたか? さて、今回は処理対象と成ったデータから、重複データをチェックしてみましょう。手段は幾らかあるかもしれませんが、代表的なのを 2つ、以下に挙げます。 上図左側は、存在チェック先である列全体を…

Designer:シート名に取り扱える "カッコ"

シート名の制限 Excel のシート名は、フォルダやファイルの名前と同様、様々な制限があります。生成キーワード『シート分割』や、業務ファイル(.xob)内の変数は、シート名を決定することもありますので注意が必要ですね。 使用不可記号 ‥‥ コロン(:)、円記…

Designer+xobQuery:様々な "今日日付" の取得方法

有効性と有用性の境目。 入力データから、"今日(システム)日付" を得る手段は、数あります。それこそ――たとえ何処にも明示されていなくとも、xoBlos の内部処理や Excel の関数等で得ることができますので、業務実行時の日付/日時を記録として残したい場…

Designer:Excel の親切機能に笑顔で対応する

親切が "仇" に。 上図は一見して、特に難しくはない抽出用の制御シートですが、作成途中です。出力項目である「売上」は、セル「AUG2」を対象にしたく‥‥更に隣のセル「AUH2」からは、「bve」という項目名として抽出を行おうとしています。 しかし作成者は、…