Excelの最近のブログ記事

Excelで印字する時に、複数ページにまたがった時も共通の見出しを各ページにつけたいことがあります。
その場合は、以下を設定してくれれば印字した際に各ページに見出しがつきます。

1. [ファイル]→[ページ設定]→「シート」タブを選択する
2. 印刷タイトルの行のタイトルを見出しにする行を選択する（例：$2:$2の場合2行目が見出しとなる）

印刷プレビューで「次ページ」を選択し、確認するとわかります
結構使うんだけど忘れちゃうんですよね。

Excelで複数の条件にマッチする行を抽出する方法です。
こちらも覚えておくと便利です。

例えば、A1、B1、C1の値がすべて正しい場合に、D1セルにOKと表示し、値が異なっていたらNGと表示する
D1セルに以下の式を入力すれば表示されます。

=IF(AND(A1=B1, A1=C1), "OK", "NG")

以下サイトにもっと詳細な情報があります
AND演算・OR演算を演算子で行う：Excel エクセルの使い方-関数/計算式
http://www.relief.jp/itnote/archives/001180.php

Excelでデータ作成している時に、セルに文字数をチェックし、さらに10文字まで抽出したい時があります。
（ないかもしれませんが、ある前提で）。その場合以下の関数を入れればOK。

• A1セルの文字数をチェックし、10文字以上であれば先頭から10文字だけ抜き出す

=IF(LEN(A1)>10, LEFT(A1,10), "OK")

簡単だけど、結構仕事で使います。

こちらも覚えておくとすごく仕事が速くなるテクニックの１つです。
Excelは使いこなすと本当に便利ですよね。覚書として残しときます。

例としてECで顧客ID、購入単価、注文日のデータがあるとします。

1）ピボットテーブルで購入単価のデータ個数で表示する

メニューより「データ」→「ピボットテーブルとピボットグラフ　レポート」を選択する

2）作成したピボットテーブルをグループ化する

• ピボットテーブル上でメニューから「データ」→「グループとアウトライン設定」を選択する
• グループ化ダイアログで、自動の単位を変更し、OKをクリック

これで完了。ちょっとしたデータ分析は十分できますね。

以下サイトにわかりやすく解説されています。
Excelマニュアル ピボットテーブルの使い方2
http://www.seta.media.ryukoku.ac.jp/manual/office/excel/table/pibod2.html

またまた、Excelシリーズです。特定のレコードだけの合計を求めたいときってありますよね？
そういったときに使える小技です。これも忘れちゃうのでメモっときます。以下2つの例

1)　商品の売上データから特定のカテゴリ（本）のみ集計する

これ正直ピボット使えばできるのですが、関数の方が手軽にできるときがあります。

※A列、カテゴリ=「”本”」とあるB列、売上金額を合計する
SUMIF(A:A, "本", B:B)


2)　商品の売上データから特定のカテゴリ（本）で東京のものを集計する

今度は条件が2つになります。SUMIFでは条件を１つしか指定できないので配列数式をつかいます。

※A列、カテゴリ=「本」でB列、地域＝「東京」のC列売上金額を合計する
SUM(IF(A1:A10="本", IF(B1:B10="東京", C1:C10)))
入力後、Ctrl + Shift +  Enter を押せばOKです。


【参考サイト】
SUMIF関数で複数条件指定：Excel エクセルの使い方-関数/計算式-数学・統計

Excelの関数にはASC()があり、全角文字を半角に変換します。この場合、カタカナも半角カナ文字へ変換されてしまいます。

例）A1に「Ａ１のセル」と入力し、Ｂ1セルに「=ASC(A1)」と入力する。
　　結果　⇒　A1のｾﾙ

私が調べたところマクロを使うしかありません。（もし、マクロ以外でもっと簡単な方法があれば教えてください）
以下コードをマクロにコピペし登録してください。

Function ASC2(ByVal str As String) As String

Dim length As Integer
Dim char As String

length = Len(str)

Do While str <> ""
  char = Left(str, 1)
  If (char >= "０" And char <= "９") Or (char >= "Ａ" And char <= "Ｚ") Or (char >= "ａ" And char <= "ｚ") Then
    ASC2 = ASC2 & StrConv(char, vbNarrow)
  Else
    ASC2 = ASC2 & char
  End If
  str = Mid(str, 2)
Loop
End Function

これで先程の例でいうとC1セルに「=ASC2(A1)」と入力すると以下の石化になります。

　結果　⇒　A1のセル

税込み価格のみ表示されているExcelデータで税抜き価格にしてとお願いされました。次のように関数を使用したらきれいにできましたのでここでメモしときます。



例）税込み価格　105円　の税抜き価格を計算する

ExcelのROUND関数を使用する。以下は105という数値がB3セルあった場合の例です。

=ROUND(B3/105*100,0)

105で割って、100をかけると　100円　と出力されます。たまに使うんですよね。税抜きの計算
また、逆に税込み価格を計算する場合は、四捨五入の設定にあわせ、ROUNDUP関数やROUNDDOWN関数を使用します。

重複以外のユニークなセルの値をカウントしたいということはないでしょうか？フィルタで重複を非表示にしても良いのですが、カウントをとりたいだけなのにちょっと面倒ですよね？
そんなときは、以下の方法で簡単にカウント数がとれます。

• カウントを出力したいセルに以下の通り入力

　　=SUM(1/COUNTIF(A2:A10, A2:A10))
　　※下線部は対象範囲

• 入力後、Ctrｌ＋Shift ＋Enterをクリック（配列数式により以下のようになります）

　　{=SUM(1/COUNTIF(A2:A10,A2:A10))}

すると、きちんとユニークカウント数が表示されます。

何も書くことがないので、個人的にExcelで良く使うゼロパッディングの方法を書きます。結構すぐ忘れてしまうんですよね。

=REPT("0", 10-LEN(A1))&A1

【例】

• A1セルに「12345」に10桁になるまで0をつける(⇒0000012345)となる

【解説】

• REPTは第1引数を第2引数分くりかえす
• LENは指定したセルの文字数を取得する
• 10桁にしたい場合、セルの文字列数分引いて「0」を加え、その後にA1の文字列を連結する

Excelって覚えていると仕事が速くなるんですよね。

Excelはビジネスにかなり使えますが、今日職場で話していたらExcel関数が実はあまり使われていないことが判明。ってことでExcel関数からSUBTOTALを紹介します。

SUBTOTAL

【使い方】
セルの範囲をオートフィルタした場合、表示されている行にのみ関数を適用する

【使用例】

SUBTOTAL(9, A1:A50)

• 第1引数：実行する関数（1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV, 8=STDEVP, 9=SUM, 10=VAR, 11=VARP）
  

• 第2引数：対象のセル範囲

フィルタをかけていない場合、以下と同様の意味です。

SUM(A1:A50)

フィルタをかけると動的に値が変化するのでいちいちSUMを入力する手間がはぶけます。
今回はこんな感じで。次回は別の関数か、ピボットテーブルを紹介する予定。

↓Excelを普通に使いこなしている人に最適。
普通じゃわからない。Excelでの様々なソリューションがのっています。