「Office」の版間の差分

提供：senooken JP Wiki
(条件付書式で括弧をつけた時の空白セル)
(固有値の抽出)
103行目: 103行目:


LibreOffice Calcだとデフォルトで正規表現が使えるので|もみなされてしまう。設定を変えるか、他の文字にするとよい。
LibreOffice Calcだとデフォルトで正規表現が使えるので|もみなされてしまう。設定を変えるか、他の文字にするとよい。
==== 固有値の抽出 ====
<nowiki>https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/</nowiki>
<nowiki>https://ask.libreoffice.org/en/question/101374/find-distinct-values-in-a-column-formula-for-calc/</nowiki>
<nowiki>https://office-hack.com/excel/duplicate-delete/</nowiki>
<nowiki>https://www.pc-koubou.jp/magazine/34116</nowiki>


== Excel ==
== Excel ==

2024年10月31日 (木) 14:25時点における版

Font

いくつか愛用しているフォントがある。

日本語と英語でフォントを分けられないソフトがあったりするので、必然的に日本語フォントが優先される。

  • 明朝体: IPAexMinch
  • ゴシック: Migu 1C
  • 等幅: Migu 1M
  • スライド: Migu 1C

以前はスライド用にMigMix 1P/Migu 1Pを使っていた。視認性が高いから。その後、2011-04-18にMigu 1Cとさらに視認性を重視したフォントが生まれた (変更履歴 : M+とIPAの合成フォント)。Migu 1Cでいい。

Power Query

Power Query（パワークエリ）とは？Excelの限界を超える画期的なデータ処理を実現！

[データ]-[データの取得と変換]

データソースの変更

ファイルを変更して、一度読み込んだデータを別のデータに変更する。

  1. [データ]-[クエリと接続]-サイドバーからクエリをダブルクリック
  2. [Power Query エディター]-[クエリの設定]-[ソース]

なお、そのままやると列数が増えた場合に対応できない。

[ソース] で数式を [Columns=null] に変更するとOK。

Spreadsheet

文字数ソート

エクセル　文字数　多い順　並び替え -エクセルの列の並び替えなのです- Excel（エクセル） | 教えて!goo 

=IF(A2="","",LEN(A2))

そのままではできないので、作業用シートを作る。

Cell

重複判定

Excelで重複したデータを簡単に確認する方法［COUNTIF関数］ | パソコン工房 NEXMAG

別シートなどでCOUNTIF。これで重複があれば2以上になる。最後フィルターして、該当セル番地などが分かる。

空判定

ISBLANK 関数を使用すると、結果は false になります - Microsoft 365 Apps | Microsoft Learn

Excelの話。

ISBLANKは空文字の場合、FALSEになる。

セルの空白判定に役立つ関数がいくつかある。

  • ISBLANK=空白の場合。
  • ISERR=N/A以外のエラーの場合。
  • ISERROR=エラーの場合。
  • ISNA=N/Aの場合。
  • ISNONTEXT=空白セルと、文字以外のセル。

空文字の場合、ISBLANK=FALSE、ISERR/ISERROR/ISNONTEXT=TRUEになる。

これを利用して、(ISBLANK || ISERR)にすると、空白を判定できると思われる。

文字列

表計算ソフトで、文字列リテラルは、二重引用符で囲む。一重引用符はNG。文字列として二重引用符を使う場合、二重引用符内で、二重にする。

デフォルト値

IF関数でやるしかない。条件演算子、Null合体演算子、OR/||などは使えない。

IFERRORに近い機能がある。

先頭の一重引用符

Excelでは先頭の一重引用符 (アポストロフィー) はセルが文字列であることを示す特殊なマーク扱い。

先頭の一重引用符を残したい場合、工夫が必要。

  1. 一重引用符を二重にする ('text')。
  2. 先頭を=で始める (="'text'")。

=で始めると末尾の"の削除も必要になる。一重引用符の二重がシンプル。

検索特殊文字

vlookupなど検索系関数のキーの文字列では、特殊な文字がある。そのまま使うと、解釈されてマッチしない。

具体的には、~があるとうまく検索できない。

LibreOffice Calcだとデフォルトで正規表現が使えるので|もみなされてしまう。設定を変えるか、他の文字にするとよい。

固有値の抽出

https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/

https://ask.libreoffice.org/en/question/101374/find-distinct-values-in-a-column-formula-for-calc/

https://office-hack.com/excel/duplicate-delete/

https://www.pc-koubou.jp/magazine/34116

Excel

オートシェイプの全選択

Ref: シート上の図形（オートシェイプ）を全て選択した状態にするショートカットキー【エクセルTips】

[C-g]-[セル選択]-[オブジェクト]

ファイルのインポート

複数のCSVをシートごとにインポートして、整理したいことがある。単にCSVを開くだけだとやりにくい。

[データ]-[テキストまたは CSV から]-CSVファイルを選択-[インポート]-[データの変換]

CSVに改行が入っていると、崩れる。インポート時に設定がある。

[適用したステップ]-[ソース] をダブルクリック-[改行]=[引用符で囲まれた改行を無視] を選択-[OK]-[閉じて読み込む]。

データの変換時に、列の型も指定できる。全部文字列にすると、エラーなく全部読込はできる。

R1C1参照形式

列の表示が数字になってしまったのを、元のアルファベットに戻したい。｜中小企業ソリューション｜キヤノン

列を番号で表示して確認したいことがある。

[ファイル]-[オプション]-[数式]-[数式の処理]-[□R1C1参照形式を使用する]

これをオンにする。

Type

日付の数値対策

【Excel】入力した日付が5桁の数字になるのを防ぐ「書式設定」の確認方法 | 特選街web

Excelの日付が数字になるときの対処法 - [その他 + その他 ぺんたん info]

日付のデータが5桁の数字になって困ることがある。

これはセルの書式設定が、[標準] や [数値] になっているから。[日付] に変更すれば解決する。

セル参照、数式の場合は、TEXT関数で表示形式を指定する必要がある。"yyyy-mm-dd"を指定しておくとよいだろう。

文字の数値変換

文字列の数字を数値に変換する方法がいくつかある。

  • value
  • 1*

+はだめで、代わりに1*。これがシンプル。

数字がない場合、#VALUEのエラーになる。回避したければ、IFERRORで囲む。 

=1*IFERROR(A1, 0)

文字列から数字の抽出

固定幅で決まっているならともかく、任意の位置の場合、シンプルな方法はない。

「Excel: Extract number from text string」

文字がない場合0。 

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

文字がない場合空。 

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

ただ、どちらの方法も1の単独数値はなぜか抽出できない。

google sheets - Extract numbers from excel cell with mixed text - Stack Overflow 

=MID(A1, SEARCH("(", A1) + 1, SEARCH(")", A1) - SEARCH("(", A1) - 1)

前後の囲み文字が分かっているならば、SEARCHを駆使するのがいい。

文字列内の数字判定

Excel: Check if cell contains number in text string - Stack Overflow 

=0<COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))

Cell

大量セルのオートフィル

Excelで大量行のオートフィルを一気にしたい。｜カルフ / Ph.D / メーカー研究職

  1. まずベースとなる範囲を選択。
  2. [ホーム]-[検索と選択]-[ジャンプ] (C-g)。
  3. オートフィルの端のセル番地を指定して、Shiftを押下したままOK
  4. [ホーム-[フィル]-[連続データの作成]-[オートフィル]-[OK]

最大値位置の取得

【Excel】エクセルにて最大値や最小値のセルの場所（番号）を検索する方法【セル位置の取得】 | モアイライフ（more E life） 

=MATCH(MAX($A$1:$A$11),A1:A11,0)

MATCHで (最大) 値のセルを検索する。

ゼロの非表示

空セルを参照したり、一部の計算結果が空白になる場合、0が表示されることがある。

いくつか方法がある。

[ツール]-[オプション]-[表示]-[ゼロ値のチェックを外す] 

=IF(Sheet1!A1="","",Sheet1!A1)

置換

文字列を置き換えるREPLACE関数とSUBSTITUTE関数 | ノンプログラミングWebアプリ作成ツール - Forguncy（フォーガンシー）| メシウス株式会社

  • REPLACE: 文字位置を指定した置換。
  • SUBSTITUTE: 文字の置換。

SUBSTITUTEは重宝する。

条件付書式で括弧をつけた時の空白セル

『条件付書式で括弧をつけた時の空白セル』（kibi） エクセル Excel [エクセルの学校]

空白ってのは セルデータなし なのか 数式の結果が ="" なのか？


どっちでしょ？


後者なら #;#;;(@)


前者なら書式標準で数式結果でカッコ対応。


ちなみに、条件付き書式、関係ある？

Word

キーワード変数

【Word】文書中に何度も登場する文字列を1か所変更するだけで他の箇所も一括で変更する方法 | My Life ８

例えば契約書などで、同じ氏名や住所が同一文書内に複数回登場することがある。一箇所変更すれば、全体も反映されるようにしたい。

ブックマークと相互参照を利用する。

  1. まずどこかで後で流用したい文字列を入力しておく (文書の先頭などわかりやすいだろう)。
  2. 登録対象文字列を選択して [挿入]-ブックマーク] を選び、わかりやすい名前でブックマークに登録する。
  3. 以後の登録した文字列を使いたい場所で、[挿入]-[相互参照] で先に登録したブックマークの参照を配置する。
  4. 文字列を一括置換したい場合、最初に登録したブックマークの文字列を別のものに変更する。

表内のタブ

【Word】表内でのタブ設定について | ヤマダパソコンスクール

枠組みなどで、表の中で基本文書を記述することがある。その際に、ルーラーによる揃えをしたくて、タブを入力したいが、次のセルに移動になってしまう。

C-TabでTabを入力できる。

LibreOffice Draw

テキストボックスの内部余白

text box internal margins - how to set them - English - Ask LibreOffice

テキストボックスに二重に余白がある。内部余白は、[Format]-[Text Attributes...] から変更可能。

グリッド

LibreOffice Drawの基本

図形の配置でグリッドとスナップが重要。

グリッドの間隔類は、[Preferences]-[LibreOffice Draw]-[Grid] で設定する。

[Resolution] がグリッド (点) の表示間隔。その横の [Subdivision] はグリッド同士の細分化個数。グリッドをあまり細かくしすぎると、目が疲れる。

例えば、グリッドを1間隔にして、subdivisionを2にすると、0.5間隔でグリッドの間をスナップできる。

LibreOffice Calc

e-taFormula

evaluate

セル内のテキストを数式として評価してほしい場面に遭遇した。

例えば、画素数 (720x720) の合計。Excelだとevaluateという関数があるが、Calcにはない。マクロなどで頑張る。あるいは、数式ではないがF9で数式に変換できるらしい。

「https://wiki.senooken.jp/index.php?title=Office&oldid=2127」から取得