「Excel」カテゴリーアーカイブ

Excel:大文字⇔小文字の変換方法

Excelで英字の「大文字⇒小文字」、「小文字⇒大文字」へ変換する方法です。

小文字⇒大文字へ変換する方法

    英字の小文字を大文字へ変換する場合は「UPPER」関数を使用します。
    以下の例ではB2セルにある内容をC2セルへ大文字で表示しています。
    C2セルへは「=UPPER(B2)」と入力します。

大文字⇒小文字へ変換する方法

    英字の大文字を小文字へ変換する場合は「LOWER」関数を使用します。
    以下の例ではB3セルにある内容をC3セルへ小文字で表示しています。
    C3セルへは「=LOWER(B3)」と入力します。

CSVファイルの項目をダブルクォーテーションで囲む方法

CSVファイルを扱っていると項目値をダブルクォーテーションで囲まれていないケースもあり、これを項目毎にダブルクォーテーションで囲ませたい時があります。こういう場合に手っ取り早くダブルクォーテーションを付与する方法をご紹介します。基本的にはEXCELとサクラエディタなどのエディタを併用する事で実現出来ます。

使用するソフト

  • Microsoft Excel ※特にバージョンは問いません
  • サクラエディタ ※秀丸など他のエディタでも可

CSVファイルへのダブルクォーテーションの付与手順

  1. Excelで対象のCSVファイルを開きます。ここでは「sample.csv」を開いています。
    「sample.csv」の内容は以下の通りです。
  2. Excelでcsvファイルを開いたらデータの最終行の次行へ以下の数式を入力します。今回は1行空けて見やすくA5セルへ入力しています。
    ⇒「=””””&A1&””””」
  3. A5セルへダブルクォーテーション付で値が表示されましたので次にA5セルの式をA5:C7セルまでコピペします。
    ※項目数、行数によってペーストする範囲は変更して下さい。
  4. ここまで項目単位にはダブルクォーテーションが付与された状態になったのでこの内容をサクラエディタへ貼り付けます。
    ただこのままではカンマ区切りではなくタブ区切りの状態なので今度はこれをカンマ区切りへ変更します。
  5. 「Ctrl + R」で置換ダイアログを表示し、「置換前」の文字へ「\t」、「置換後」の文字へ「,」(カンマ)を入力し、正規表現へチェック後に「全て置換」を選択します。
  6. これでダブルクォーテーション付与のカンマ区切りのCSV形式の内容になったのでこの内容を元のファイルと差し替えれば完了です。

Excel:シートをコピーしようとすると「移動またはコピーしようとしている数式またはシートには、移動またはコピー先のワークシートに既にある名前’xxx’が含まれています。この名前を使用しますか?」というメッセージが表示される原因

Excelを操作しているとシートをコピーする際に「移動またはコピーしようとしている数式またはシートには、移動またはコピー先のワークシートに既にある名前’xxx’が含まれています。この名前を使用しますか?」というメッセージが表示される事がたまに発生します。今回はその原因と対処方法についてメモしておきます。

原因

    一部のセル範囲に名前指定していてその指定した範囲を列削除などしていると名前指定範囲等がおかしくなってしまい、シートコピーの際にメッセージが表示されてしまいます。
    ※Office2013の場合は「名前’xxx’は既に存在します。この名前にする場合は[はい]をクリックします。移動またはコピーを行うために’xxx’の名前を変更する場合は、[いいえ]をクリックします。」というメッセージになります。

対処方法

  1. 「Ctrl + F3」もしくはメニューの「数式」タブ⇒「名前の管理」を選択します。
  2. 名前の管理画面が表示されたら表示された一覧で参照範囲か範囲の数式が「#REF!」などエラーになっている箇所を修正する。もしくは名前定義そのものを削除すれば完了です。

Excel:印刷すると1ページ目にしか印刷設定内容が適用されない場合の対処方法

Excelで複数ページを印刷する場合に「2 in 1」などに集約して印刷設定した後に印刷してみると1頁目だけ「2 in 1」形式で印刷され、2頁目以降は「2 in 1」形式ではなく元のまま1ページ分そのまま印刷されてしまうという事を経験した事はないでしょうか。これは印刷設定画面でプレビュー表示されている頁のみに変更した印刷設定が適用されている為に発生する事象です。解決するには大きく2通りの方法があります。

対処方法.一

    一つ目の対処方法としては印刷画面で「1頁単位で表示⇒印刷設定」を印刷する頁数分繰り返して指定する方法です。
    ただこの方法では印刷頁数が2,3枚程度ならいいですが数十、数百頁ある場合は手間が掛かり過ぎるのでお勧め出来ません。

対処方法.二

    二つ目の対処方法(というか根本的な対処方法)としてはExcelファイルを開く前にWindowsメニューの「デバイスとプリンター」画面で対象のプリンタのプロパティから直接印刷設定変更した後でExcelファイルを開いて印刷する方法です。
    基本的にExcelでは起動した際にデフォルトでプリンターの印刷設定通りに印刷されますので、Excelを起動する前にプリンターの印刷設定をしておけば全ての印刷対象頁にその設定が反映されます。

Excel:複数の条件でカウント・集計する方法

Excelで複数の条件でデータ数のカウントや集計する方法をご紹介します。
使用する関数は複数条件でのカウントならcountifs関数を、複数条件での集計ならsumifs関数を使用する事で実現出来ます。

動作環境

    Microsoft Office Professional 2013

Excelで複数の条件でカウントする方法

今回はCOUNTIFS関数を使用して以下の表データを対象に「工程」別に「状態」毎の個数を求めます。

  1. カウント結果を表示するセル(今回はH列へ表示させるので以下はH4セルの入力例となります)へ以下の式を入力します。
    「=COUNTIFS($B$3:$B$12,$F4,$C$3:$C$12,$G4)」
  2. 後は結果を表示したい行数分コピーしてすれば「工程」別に「状態」毎の個数が表示されます。

Excelで複数の条件で集計する方法

今回はSUMIFS関数を使用して以下の表データを対象に「工程」と「状態」毎に「期間」の集計値を求めます。

  1. 集計結果を表示するセル(今回はI列へ表示させるので以下はI4セルの入力例となります)へ以下の式を入力します。
    「=SUMIFS($D$3:$D$12,$B$3:$B$12,$F4,$C$3:$C$12,$G4)」
  2. 後は結果を表示したい行数分コピーしてすれば「工程」と「状態」毎に「期間」の集計値が表示されます。

Excel:重複データをチェックする方法

Excelで重複データをチェックする方法をご紹介します。
Excelで重複データをチェックする場合はifとcountifを使用する事で簡単にチェックする事が出来ます。
以下の例ではA2セル~A10セル内で重複データの有無を調べて重複データが存在する場合にB列へ”★”を表示させています。

動作環境

    Microsoft Office Professional 2013

Excelで重複データをチェックする方法

  1. 重複結果を表示するセル(今回はB列へ表示させるのでまずはB2セルへ入力)へ以下の式を入力します。
    「=IF(COUNTIF($A$2:$A$10,$A2)>1,”★”,”-“)」
  2. 後は結果を表示したい行数分コピーすれば重複データの行に”★”が表示されます。

Excel:1行おきに空白行を追加する方法

Excelで1行おきに空白行を挿入する方法をご紹介します。
基本的に1行おきに空白行を挿入する方法としては「Ctrlキーを押下しながら1行ずつ行番号をクリックしていく方法」と「連番列を追加してソートする方法」の2通りあります。

動作環境

    Microsoft Office Professional 2013

Ctrlキーを押下しながら1行ずつ行番号をクリックして1行おきに空白行を挿入する方法

  1. 空白行を追加したい行をCtrlキーを押下しながら選択します。
  2. 行選択後、右クリックしメニューの「挿入」を選択します。
  3. これで以下の様に選択した各行の上に1行ずつ空白行が挿入されます。

連番列を追加してソートして1行おきに空白行を挿入する方法

    「Ctrlキーを押下しながら1行ずつ行番号をクリックしていく方法」でも空白行は1行おきに挿入出来ますが、行数が100行や1000行とかあると1行ずつ選択するのは苦行です。そういう場合は連番列を追加してソートする方法を推奨します。
  1. 空白行を追加したい行の隣に連番列を追加します。
  2. 追加した連番分のセルをコピーして真下にそのまま貼り付けます。
  3. 上記で追加した連番セルも含めてソートしたい範囲(空白行を挿入したいセル)を選択し、連番列の昇順にソートします。
  4. 連番列の昇順でソートすると以下の様に1行おきに空白行が挿入されます。

Excel:列番号の数値形式をアルファベット形式へ戻す方法

Excelで他の人が作成したファイルを開くと以下の様に列番号が数値形式になっていることがあります。

アルファベット形式に慣れている人がそのまま使用すると関数を使用する時などどのように指定すればいいのか結構迷います。
そういう時は慣れてない数値形式のまま作業する必要などありません。アルファベット形式に戻してやれば良いのです。

動作環境

    Microsoft Office Professional 2013

列番号の数値形式をアルファベット形式へ戻す手順

  1. まずメニューの「ファイル」を選択して左メニューにある「オプション」を選択します。
  2. オプション画面が表示されたら左メニューの「数式」を選択し、数式の処理欄にある「R1C1 参照形式を使用する」のチェックを外し「OK」ボタンを選択します。
  3. 上記手順で以下の様に列番号がアルファベット形式に戻ります。

Excel:生年月日から年齢を算出する方法

Excelで生年月日から年齢を算出する方法をご紹介します。
生年月日を表示する為にはDATEDIF関数をすると簡単に年齢を算出出来ます。

動作環境

    Microsoft Office Professional 2013

年齢を表示する手順

  1. まず生年月日を入力するセルを設定します。 ※今回の例ではB3セルへ生年月日を表示します。
    セルの書式は日付であれば好みで設定してOKです。
  2. 次に年齢を表示するセルを決めて以下の通り入力します。(今回はC3セルへ年齢を表示します。)
    「=DATEDIF(B3,TODAY(),”Y”)」
    ※DATEDIF関数の書式は以下の通りとなります。
    「DATEDIF(開始日,終了日,単位)」
    単位説明
    "Y"期間内の満年数
    "M"期間内の満月数
    "D"期間内の日数
    "MD"開始日から終了日までの日数
    "YM"開始日から終了日までの月数
    "YD"開始日から終了日までの日数
  3. 基本的には上記手順だけでも年齢は表示出来ますが、より分かりやすくするために年齢が「○○歳」という感じに表示されるように設定します。
    単位を表示するためには年齢を表示するセルで右クリック⇒「セルの書式設定」から「ユーザ定義」の種類へ「0″歳”」と入力します。
  4. これで以下のように生年月日を入力すれば年齢が単位付で表示されます。

Excel:西暦から和暦へ変換する方法

Excelで西暦から和暦へ変換する方法をご紹介します。

動作環境

    Microsoft Office Professional 2013
  1. 和暦表示するセルを選択して右クリックし、「セルの書式設定」を選択します。
  2. セルの書式設定ダイアログが表示されたら、「分類=ユーザー定義」を選択し、種類欄へ「ggge”年”m”月”d”日”」と入力します。これだけで設定は終了です。
  3. 後は書式設定したセルへ西暦で入力すると和暦へ変換されて表示されます。