いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

Excel

(Excel)グループ別の最大値・最小値を求める

Excelで、多数の数値を個々のグループごとに区別して、それぞれの最大値・最小値を求める方法についてです。 FILTER関数とCOUNTIFS関数を組み合わせて、グループ別の最大値・最小値を求めることができます。手順 画像では、B列に氏名が、C列にスコアの数値が…

(Excel)URLからファイル名を取得する

Excelで、URLの文字列からファイル名の部分を取得する方法についてです。 具体的には文字列中の最後の「/」より後の部分を取得します。 FIND関数やSEQUENCE関数等を組み合わせることで、URLからファイル名の部分を取得できます。手順 画像ではC3セルに式を入…

(Excel)最も多く出現する文字列を取得する

Excelで、文字列のうち最も多く出現しているものを関数を使って取得する方法についてです。 数値の場合はMODE関数で直ちに取得できますが、文字列の場合は少し工夫する必要があります。 INDEX関数やMODE関数等を組み合わせることで、最も多い文字列を取得す…

(Excel)シート名を関数で取得する

Excelで、シートの名前を関数を使って取得する方法についてです。 CELL関数を使って得られる文字列をもとに、シートの名前を取得することができます。 手順 簡便な方法 CELL関数の第2引数(対象範囲)を省略できない理由 パスが角カッコを含む場合の対応 手…

(Excel)文字列に含まれる文字の種類を数える

Excelで、文字列の中に文字が何種類あるかをカウントする方法についてです。 MID関数やUNIQUE関数等を使って、文字列中の文字の種類がいくつあるかを数えることができます。手順 画像では、B3セルに入力されている文字列の文字の種類をカウントするため、C3…

(Excel)部分一致検索の逆引きをする

Excelで、部分一致検索の逆の検索(検索値が検索範囲の文字列を含んでいるケースを検索)をして表引きする方法についてです。「VLOOKUP関数で検索範囲の側にワイルドカードを付けて検索する方法」といった方がわかりやすいかもしれません。ただしVLOOKUP関数…

(Excel)テーブル名を確認・変更する

Excelで表をテーブル化した際に、テーブルに付けられた名前を確認し変更する方法についてです。 テーブル内のセルを選択した状態で出現する「テーブル デザイン」タブを選択すると「テーブル名:」の欄にそのテーブルの名前が表示されます。変更もできます。 …

(Excel)関数を使って郵便番号から住所を取得する

Excelで、入力された郵便番号に対応する住所を取得する方法についてです。 ここで紹介するのはIMEの設定によるものではなく関数を使って郵便番号を住所に変換するもので、無料で利用できるAPIから都道府県・市区町村・町域名を取得します。 WEBSERVICE関数等…

(Excel)ヘロンの公式で三角形の面積を求める

Excelで、ヘロンの公式により三角形の面積を求める方法(三角形の三辺の長さから、その三角形の面積を求める)についてです。 LET関数を使うことで、比較的短い式でヘロンの公式を記述して三角形の面積を求めることができます。手順 画像ではC2,C3,C4セルに…

(Excel)日付が土日・祝日なら直前(直後)の平日を求める方法

Excelで、ある日付が平日であればそのままの日付を、そして土日・祝日だったらその直前の平日(直後の平日)を求める方法についてです。つまり前営業日や翌営業日を求める方法ともいえます。 WORKDAY関数を使って、指定した日付が土日や祝日であるときにその…

(Excel)期間中の最後のx曜日を求める

Excelで、指定した期間内の日付でx曜日であるもののうち最後のものを求める方法についてです。 ここではSEQUENCE関数を使って候補となる日付を生成し、FILTER関数等で絞り込むアプローチを紹介します。 SEQUENCE関数やFILTER関数などを使って、指定した期間…

(Excel)直近のx曜日の日付を求める

Excelで、基準となる日付に最も近いx曜日の日付を求める方法についてです。 基準日の曜日に基づき一定の日数を加える方法がメジャーですが、式の意味がつかみにくい難点がありますので、ここでは別のアプローチを紹介します。 WEEKDAY.INTL関数を使って、基…

(Excel)期間中の第n回目のx曜日を求める

Excelで、指定した期間内の日付でx曜日であるもののうちn回目のものを求める方法についてです。 関数を使って求めるとかなり長くなりますが、2020年後半から使えるようになったLET関数を使って短縮する例を紹介します。 SEQUENCE関数やFILTER関数などを使っ…

(Excel)区切り文字による文字列の分割を関数で行う

Excelで、関数を使って区切り文字による文字列の分割を行う方法についてです。つまりはワークシート関数によるSPLIT関数の実現です。 FILTERXML関数などを使って、区切り文字による文字列の分割を行うことができます。 ただし「手順 画像ではB3セルに対象と…

(Excel)表の中のn行目(n列目)を抽出する

Excelで、表の中の(つまり指定した範囲内の)n行目の内容を取得する方法についてです。n列目の内容を取得することもできます。 「=INDEX(範囲,n,)」という式により、表の中のn行目の内容を抽出することができます。 「=INDEX(範囲,,n)」という式により、n列…

(Excel)クロス表からデータを検索・取得する

Excelで、検索値に基づいて表の中の行と列を特定し、行と列が交差するセルのデータを取得する方法についてです。 XLOOKUP関数を使ってクロス表からデータを抽出することができます。ネストは必要ですが1つの関数で済むのがメリットです。 オーソドックスな方…

(Excel)関数を使ったグループ化と集計

Excelにはメニュー操作によるグループ化と集計の機能がありますが、ここではより簡単に関数を使って値をグループ化し、その値ごとに数値を集計する例を試してみます。 UNIQUE関数を使って値をグループ化した結果を得ることができます。 さらにSUMIF関数など…

(Excel)XLOOKUP関数で複数条件の検索を行う

ExcelのXLOOKUP関数を使って、複数の条件による検索をする方法についてです。 ただしここで「複数の条件」とは、複数列にわたる条件のことです。1つの列に対して2つ以上の検索値を設定して検索する、というものではありません。 XLOOKUP関数の第2引数(検索…

(Excel)一番右の値を取得する

Excelで、指定した行のうち値が存在する(空白でない)最も右の行の値を取得する方法についてです。 XLOOKUP関数を使って列の一番右の値を取得できます。 INDEX関数とFILTER関数を使う方法もあります。 手順 XLOOKUP関数を用いる方法 INDEX関数とFILTER関数…

(Excel)関数を使って抽選をする方法

EXCELで、関数を使って抽選を行う方法についてです。 すべての対象者にランダムな順位を与える例と、一定の数の当選者をランダムに決定する例を紹介します。 RANDARRAY関数やSORTBY関数などを使って、順位をつける抽選や当選者を決める抽選を行うことができ…

(Excel)表のデータを上下逆・左右逆に並べ替える

Excelで、関数を使って表の内容を上下逆あるいは左右逆に並べ替える方法についてです。データの内容に関係なく並びを逆転させます。 SORTBY関数を使って表を上下逆・左右逆に並べ替えられます。 手順 上下逆に並べ替える例 左右逆に並べ替える例 手順 画像の…

(Excel)SORTBY関数で左右方向に並べ替えることができる

ExcelのSORTBY関数には(SORT関数と異なり)並べ替え方向を指定する引数がありませんが、左右方向への並べ替えは可能です。 SORTBY関数の第2引数(並べ替え基準の範囲。第4,第6…引数も同様)で左右方向の配列を指定すれば、左右方向への並べ替えとなります。…

(Excel)1列に並んだ値を斜めに並べる

Excelで1列に並んでいるデータを、関数を使って斜め方向に並べなおす方法についてです。 対角行列とINDEX関数を用いることで、1列並んでいる値を斜めに並べることができます。手順 いきなりですが結果です。 画像ではB3:B7に並んでいる5つの値を、D3セルに式…

(Excel)任意の対角行列を生成する

Excelで対角行列(非対角成分がすべて0)を生成する方法についてです。対角の各要素の値を任意に設定することができます。 「=配列*MUNIT(配列の長さ)」という式で、配列の各要素を対角要素とする対角行列が作れます。手順 B3:E3に、対角行列の成分とする各…

(EXCEL)SORT関数で複数の並べ替えキーを指定する

ExcelのSORT関数で並べ替えのキーとなる列を2つ以上指定する方法についてです。単純にSORT関数をネストする方法もありますが、別の方法で複数のキーを設定し、昇順降順も独立して設定することができます。 SORT関数の第2引数(並べ替えキー)と第3引数(昇順…

(Excel)ランダムなパスワードを作成する

Excelで、使える文字と長さを指定してパスワード(ランダムな文字列)を作る方法についてです。 MID関数とRANDARRAY関数等を利用してパスワードを生成することができます。手順 画像ではB3セルで使用可能文字が、C3セルで長さ(パスワードの文字数)が設定さ…

(Excel)文字列中の文字をランダムに並べ替える

Excelで1つの文字列をシャッフルする、つまり1文字単位でランダムに並べ替える方法についてです。 各種関数の組み合わせにより文字列をランダムに並べ替えることができます。手順 画像ではB2セルに式を入力することにより「ABCDEvwxyz」の10文字をランダムに…

(Excel)検索値の1つ隣(上下左右)の値を取得する

Excelで、値をリストから検索し、マッチしたセルの1つ隣にある値を取得する方法についてです。 XLOOKUP関数を使って、検索値が存在するセルの1つ隣(上下または左右)にある値を取得することができます。手順 B列に駅名のリストが並んでいます。 また、E3セ…

(Excel)文字列を2文字ずつ(n文字ずつ)分割する

Excelで、文字列を2文字ずつ(n文字ずつ)区切って各セルに分割する方法についてです。 MID関数やSEQUENCE関数等の組み合わせにより、文字列を任意の文字数で区切って分割することができます。 手順 2文字ずつ分割する場合 n文字ずつ分割する場合 手順2文字…

(Excel)REPLACE関数を使って文字列中に文字を挿入する

ExcelのREPLACE関数についてです。この関数は文字列中の文字を別の文字に置き換えるための関数ですが、引数の指定により文字(文字列)を挿むこともできます。 REPLACE関数の第3引数(置き換える文字数)を「0」と指定する(または何も指定しない)ことで、…