タグ別アーカイブ: 列のマージ

Power Queryって何??~時刻を曜日と日付に変換

 今回は、関数を使用せずにyyyy/mm/dd hh:mm形式の時刻データから「日付」と「曜日」データを作成する方法を紹介します

日付と曜日データは更に、両者を組み合わせて新たな列を作成します

 今回紹介する方法は、直感的なクリック操作のみで終了しますのでとても簡単です!

 解説は、次の画像のデータをPower Queryエディタ(以降、エディタ)に読込んだところからはじめます!

1.日付データの追加

勤務開始時刻列を選択した後に「列の追加」タブで「日」をクリックします

そうすると、次の画像のように「日」の列が新たにできます

ちなみに「日付のみ」をクリックすると、yyyy/mm/dd形式になります

2.曜日データの追加

まずは勤務開始時刻列を選択します

①と同じ画面ですが、今度は「日」より下の「曜日名」をクリックします

すると次の画像のように「曜日」が新しい列に出力されます

3.日付と曜日の組み合わせ

こちらは1.と2.の列を組み合わせる「列のマージ」処理を行います

こちらのメニューも「列の追加」タブにあります

「列のマージ」をクリックすると次の画像の画面が開きます

こちらの画面で3つ指定します

・区切り記号➡カスタム記号

・カスタム記号➡「 日 /」(2段目の欄です)

・新しい列名➡日付/曜日

3つ指定したら右下のOKボタンを押します

<まとめ>

今回は時刻データから日付データと曜日データを作成する方法を解説しました

本来は関数が必要な処理をクリック操作でできるので、とても便利です

 後、「列のマージ」もデータ整理(データクリーニング)に使えますのでこちらもぜひ活用してください

今回は以上です

最後までこの記事をお読みくださいありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

文字列の追加、置換~上級編18回

 

 今回は、既存の文字列に「新たな文字列を追加」する方法と、「文字列を置き換える」方法を解説します

「文字列を置き換える」場合、繰り返し同じ文字が出現する時には、特定の位置の文字だけ置き換える必要が出てくる時があります

その場合には、過去に解説した「文字列の抽出」「列のマージ」を組み合わせて置き換えます

では、本格的な解説をはじめます!

尚、解説はPower Queryエディタ(以降、エディタ)画面内の操作のみになります

目次

1.文字列の追加

2.文字列の置換

3.繰り返し文字の置換

<まとめ>

1.文字列の追加

①既存文字列のに追加

下の画像の文字列の前に文字列「A-」を追加する方法を解説します

なお、文字列を追加する場合、元の文字列を残したまま、新たに「文字列を追加」した列を追加する方法と、元の文字列に対して、「文字列を追加」する方法があります

今回は、元の文字列を残したまま、新たに列を追加する方法で解説します

ですので、下の画像のように「列の追加」タブを使用します

「列の追加」タブ内にある「書式」の右横の▼マークから「プレフィックスの追加」をクリックします

すると下の画像のような画面が開きますので、追加する文字列「A-」を指定します

追加する文字列を指定したら、画面右下のOKボタンを押せば、下のGIF画像のように文字列「A-」が既存の文字列のに追加されています

②既存文字列のに追加

下の画像の文字列の後に文字列「-B」を追加する方法を解説します

基本的には、①の既存の文字列の前に文字列を追加する方法と一緒です

但し、下の画像の箇所にて、クリックする箇所が違います

「プレフィックスの追加」でなく「サフィックスの追加」をクリックします

それ以外の処理は①と一緒です

2.文字列の置換

では、下の文字列の中の「ABC」を「DEF」に置き換える方法を解説します

値の置換は「変換タブ」の「値の置換」から行います

ちなみに「エラーの置換」については、過去の記事で解説しておりますので、ぜひそちらをご参照ください

上の画像にある「値の置換」をクリックすると、下の画像の画面が開きます

上の画像の画面では、ⅰ)検索する値、とⅱ)置換後の値、の2つを指定します

ⅰ)ⅱ)の2つを指定し、OKボタンを押せば置換は終了です

3.繰り返し文字の置換

では、記事の冒頭で紹介した内容の解説に入ります

下の画像の文字列にある「ハイフン/-」のように、繰り返し出現する文字列の中から位置/順番を指定して文字列を置き換える方法を解説します

今回は、文字列内に5回登場する「ハイフン/-」の内、から2番目の「ハイフン/-」を「X」に置き換えます

この種の置換の場合には、記事の冒頭で前述したように「 文字列の抽出」「列のマージ」 を組み合わせて行います

実際に文字列の置換が完成するまでに、次の図のように2つの段階を踏みます

①の抽出にて、まずは文字列を右から2番目の「ハイフン/-」を境目にして分解します

この時、抽出する内容から、右から2番目の「ハイフン/-」は省いておきます

②では、下の画像にある画面を使用し、一度分解した文字列をマージ(結合)します

この時に、上の図の黄色の箇所のように、複数列をマージする際のつなぎ目に、置き換える文字である「X」を指定します

それでは、これより詳細な解説に入ります

①抽出

抽出は右から2番目の「ハイフン/-」に対して2回行います

使用するメニューは「列の追加」タブにある「抽出」メニューになります

抽出

上の画像の赤枠内の「区切り記号ののテキスト」 「区切り記号ののテキスト」 を1回ずつ使用します

ⅰ)「ハイフン/-」のの文字を抽出

上の画像の「区切り記号ののテキスト」をクリックすると次の画像の画面が開きます

こちらの画面で3か所(黄色の箇所)を指定します

・区切り記号➡こちらは「ハイフン/-」になります

・区切り記号のスキャン➡

文字列の読込方向を指定します。こちらは「入力の末尾から」になります

・スキップする区切り記号の数➡

こちらは末尾(右)から2番目なので、1度スキップします。ですので「」です

上の3つを指定してOKボタンを押すと、エディタ内の表示が次の画像のようになります

ⅱ)「ハイフン/-」のの文字を抽出

こちらは「区切り記号ののテキスト」から処理を開始します

次に開いたダイアログボックスでは、3つの箇所をⅰ)と同じ様に指定します

②マージ

①の抽出が終了した段階で、エディタ内の表示は次の画像のようになっているはずです

では、前述の通り、新たに抽出した列をマージする処理を行います

マージの処理は、次の画像の黄色で印をつけた箇所のメニューより行います

列をマージする際には、必ず、Ctrlキーでマージする列を選択しておきましょう

「列のマージ」をクリックした後に開いたダイアログボックスでは、まず区切り記号を「カスタム」で指定しましょう

更に、前述の通り置き換える文字「X」を、複数列のつなぎ目として指定します

そして、最後に新しい列名を指定します

次にOKボタンを押すと、下のGIF画像のように列がマージされます

列が「X」をつなぎ目としてマージされた結果、右から2番目の「ハイフン/-」が「X」に置き換わっています

<まとめ>

今回は、文字列を追加及び置き換える方法について解説を行いました

 文字列の追加については、「列の追加」タブにある、次の画像の箇所から追加を行いました

 文字列の置換については、変換タブにある「値の置換」メニューから置換処理を行いました

 そして、最後に同じ文字列が繰り返し出現する場合に、位置(順番)を指定して文字を置き換える方法について解説しました

こちらについては、①抽出と②マージの2つに処理を分けて実施しました

まず①の抽出では、下の画像の画面から文字列の抽出を行いました

抽出

②のマージでは、①で抽出した文字列を、列のつなぎ目に「置き換える文字」を指定した上で「マージ」しました

 この①と②の処理を組み合わせることで、かなり高度な「文字の置換」が行えます

ぜひ、様々な文字列で実際に手を動かしながら試してみてください

次回は、エディタ内での「起こり易いエラーの防止策」について解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

Power Queryって何?~列の結合/複合キー活用~

Power Queryについて、名前は知っていて興味はあるけど実際にどう使うの?

という人は多いと思います

私は良くPower Queryをスマホに例えて説明するようにしています

例えば、好みに合うレストランを探してスマホで予約するケースでは、①検索サイトで好みのレストランを探す、②好みのレストランが見つかったら電話番号をクリック、③レストランの電話番号が、スマホの電話アプリ内に自動で読み込まれるのでそのままクリックする、という感じになりますよね

Power Queryでも①シート内、他のファイル、フォルダからデータを取得、②データを変換、③シートやピボットテーブルに取得データを読み込み、という流れになります

そして、詳細は本編に譲りますが、Power Queryの”Query”はスマホ内に設定するBOOKMARKと同じだと説明しています

スマホ内のBOOKMARKは、クリックさえすれば、いつでもお気に入りのデータにアクセスできます

Power Queryがスマホ内に設定するBOOKMARKとの違いは、アクセスしたデータを事前に決めたルールで変換できることです

今回は、このQueryの変換例を紹介します

ところで、エクセル内のデータを自分の都合のいいように集計するコツって何でしょう?

関数を知っている、ピボットテーブルを使いこなせる・・・

上記はいずれも、正しいです

但し、もっと重要なことがあります

データを集計するためのキーをうまく作り出すことです

私が良くセミナーで受講者の方に出す問題があります

ちなみにこの事例は、私が以前、日常業務で行っていたものです

以下から実際の問題です

上記の表にて、条件1と条件2、両方が”B”の時に、表内に◯を加えるにはどうしたらいいでしょう?というものです

しかも、条件がたえず変わる事も考慮して解決せよ、というものです

もしかしたら、AとCの時に〇を出せ、という風に修正されることもあるし、更にCとCの時にも〇を加えろ、ということもありえます

答えは、新たなキーを生み出すことにあります!

条件1と条件2を組み合わせて複合キーを作成するのです

そして、複合キーに対応した値を加えた表を作成します(この表をマスター表とします)

こうすれば、D列にVLOOKUP関数を加えれば、複合キーを参照して関数が自動的に、右横の表から値を引っ張ってきてくれます

もし、条件が修正になったり、条件が加わっても、右横の表を変えれば自動的に対応できます

以上がキーをうまく使いこなすことの効果の説明です

では、今回説明した問題の解決方法をPower Queryでも行ってみましょう!

Power Queryには”列のマージ”という機能があるので、上記で説明したような複合キーも簡単に作成できます

①表をテーブルに変換してPower Query Editorを立ち上げ

エクセルシート上のデータをテーブルにして取得し、変換できるようにします

ちなみに、このPower Query Editorというデータ変換用の画面が、別途、立ち上がるのが、Power Queryの分かりにくさかもしれませんね

➁Power Query Editor画面上の”列の追加”をクリック

③画面内の両列をクリックした上で”列のマージ”をクリック

Ctrlキーを押しながら、複合キーを作成する列を指定するのがポイントです

④③を行うと自由に複合キーを作成できるようになります!

両列をつなぐ文字に”-”を指定して、新たに作成する列名を複合キーと指定します

⑤読込先を”接続の作成のみ”に指定して読込

接続の作成のみとは、シートに表示しないでQueryを作成するという意味です

⑥新たなQueryが画面右に出来ました!

⑦マスター表も接続のみのQueryを作成

⑧作成した2つのQueryをマージします

Queryのマージとは関数で言えば、VLOOKUP関数と同じです

①~⑧で読み込まれた表が以下です!

<まとめ>

 Power Queryを前述の問題に適用しましたが、Power Queryだと最初から複合キーを作成する前提で機能が作られている分、利便性が高いです。

 そして、VLOOKUP関数で同じことを行う場合には、実際には、VLOOKUP関数単独だとマスター表にないものはエラー表示になりますので、”IFERROR関数”も組み合わせる必要があります。エラー対策が必要ない点も、Power Queryが利便性が高いです。

長文になりましたが、Power Queryに可能性を感じた方は、ぜひ、Power Query講座をのぞいてみてください!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村