タグ:Excelワザ ( 165 ) タグの人気記事

Excelワザ フリガナを一括入力

マサヒロ君が、
「ともちゃん、エクセルの表で、あいうえお順で並べ替えたいんやけど、
名前の列でA↓Z(昇順で並べ替えボタン)や
データ→並べ替えでやるんやけど、
あいうえお順にならへ~~ん(-_-;)」


ワタクシ「じゃ、名前の範囲を選択し、書式→ふりがな→表示/非表示クリックしてみ」

マサヒロ君「あれ~~フリガナが出るとこと、出んとこがある~」


通常、エクセルで作ったリストは入力した情報が入っているので、
メニューの書式→ふりがな→表示/非表示
で出てきます。
b0186959_2185159.jpg

ところが、Wordやテキストファイルなど
他のアプリケーションで作ったデータを
エクセルに貼り付けたものはふりがなは表示しません。
書式→ふりがな→編集で一つずつ出すことは出来ますが、
何百件もいちいち面倒です。
しかし、エクセルの一般機能では
範囲選択したセルに一括でフリガナは表示できません。
そこで、
簡単な3行マクロを伝授しましょう。
(3行マクロ、おいおい紹介しましょう006.gif

操作
1.ツール→マクロ→Visual Basic Editor
b0186959_219251.jpg

すると、こんな画面が
b0186959_21421052.jpg

2.メニュー→挿入→標準モジュール
出てきた白紙の領域に、これだけ入力して下さい。

Sub ふりがな自動設定()
  Selection.SetPhonetic
  Selection.Phonetics.Visible = True
End Sub

(解説)
すべて、日本語入力Offで半角英数で入力して下さい。
マクロはSubで始まり、End Subで終わります。
Subスペースマクロ名→ENTER押すと、End Subが出てきます。
次の行に
  Selection.SetPhonetic
 Selection.Phonetics.Visible = True
と入力して下さい。
Excelの関数にPhonetic関数というのがあります。
入力した読み情報を出してくれる関数ですが、それと同じですね。
VisibleがTrue(真)、要するに、見えるようにする、ということですね。
たった3行これだけを入力して下さい。
b0186959_21322946.jpg


3.Excelのワークシートに戻り
ふりがなを出したいセル範囲を選択
ツール→マクロ→マクロ→マクロ名のふりがな自動設定選んで→実行
すると、
b0186959_21401274.jpg

範囲選択した氏名の欄に一括でふりがなが入ります。
ここで終わったわけではないのです。
漢字の読みを確認してほしいのです。
姓でも「林」「はやし」と読むか「りん」と読むか、
名はもちろん「幸子」「ゆきこ」か「さちこ」か
チェックして、読みを直します。
Shift+Alt+↑キー2回押すと、読みの候補が出てきます。

b0186959_2157359.jpg

そして、
4.データ→並べ替え→氏名の列を最優先キーにして昇順
で並べ替えると、あいうえお順に並び変わるはずです。

出来るかな~、マサヒロ君023.gif
[PR]
by school-pc | 2009-07-21 22:00 | Comments(0)

Excelワザ 株式会社の表記を統一

リストの中の会社名
株式会社や、(株)や㈱や㏍
が混在している場合があります。それをスッキリ「株式会社」に統一しましょう。

b0186959_12232331.jpg

SUBSTITUTE関数というのがあります。
書式
=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)

  例)「さくら]という文字を「ひまわり」に置き換える
b0186959_12265427.jpg

式は
=SUBSTITUTE(A2,"さくら","ひまわり")


この関数を使って、
b0186959_12305671.jpg


E2のセルに入る式は
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(株)","株式会社"),"㈱","株式会社"),"㏍","株式会社"),"(有)","有限会社"),"㈲","有限会社")
全て「株式会社」「有限会社」に統一できました049.gif
こうしておくとオートフィルタ
データ処理するときに、オプションで「株式会社を含む」という条件で抽出することができます。
b0186959_12412415.jpg

b0186959_12413518.jpg

[PR]
by school-pc | 2009-07-21 12:34 | Comments(0)

Excelワザ 取引先マップの作成

取引先の地図をシートに貼り付けておいて、
必要なときにすぐに印刷できる様にしておくと、
とても便利です。
b0186959_2181676.jpg

取引先をクリックすると、地図が開く
b0186959_2183515.jpg

印刷ボタンで印刷
b0186959_2185083.jpg
目次に戻るボタンで目次に
b0186959_2191579.jpg

                    ↓
b0186959_21251489.jpg


  作り方

  1.目次シートに取引先一覧作成 名前とURLなど

  2.取引先シート作成
    取引先の地図や、会社概要等貼り付け
  3.印刷したい範囲を選択し、
    ファイル→印刷範囲→印刷範囲の設定

  4.完成した取引先シートをコピー(Ctrl+ドラッグ)
    内容を書き換えて、必要分の取引先シートを作成

  5.目次シートの取引先の入ったセルを選択し
    ハイパーリンクの挿入ボタンクリック
     リンク先→このドキュメント内→該当する取引先シート選択
b0186959_2128027.jpg
b0186959_2136532.jpg



  6.ジャンプ先の取引先シートの
   「目次シートの戻る」と書いたセルを選択し、
   ハイパーリンクの挿入ボタンクリック
   リンク先→このドキュメント内→目次シート選択


シートは最大255枚まで増やせるので、
取引先分だけシートを作り、
一つのファイルで管理しておくと便利です。




    
[PR]
by school-pc | 2009-07-16 21:31 | Comments(0)

Excelワザ 表の行列を入れ替えて貼り付け

Excelで表を作った後で、
行列を入れ替えて貼り付けることが出来ます。

1.A11:F12の表を範囲選択
2.コピー
3.貼り付けの横の下向き三角、クリック
  行列を入れ替える、クリック
b0186959_1116231.jpg

                             
b0186959_11181256.jpg

[PR]
by school-pc | 2009-07-14 11:24 | Comments(0)

Excelワザ 自動的に連番のふり直し

ROW関数というのがあります。

=ROW():式を入れたセルの行番号が求められます。
例)式を入れるセルが「A3」なら「3」が返されます。


Excelで会員表などを作り、会員さんが減った場合、
行削除すると、
減った番号が抜けてしまいます。
例)会員番号「4~6」を削除
b0186959_9253684.jpg

                          

「1、2、3、7、…」となってしまいます。
b0186959_9534890.jpg
  
b0186959_9325030.jpg


そこで、番号を入れるセルに「Row関数」を入れておくと、自動的に番号をふり直してくれます。
例)式を入れるセルが「A3」なら
=RPW()-2
(3-2=1 開始番号1になるように、求めた行番号から2引く

b0186959_9483431.jpg

この式を下にオートフィルでコピー
すると、行を削除しても「1、2、3、4、5、…」と
番号をふり直してくれます。049.gif
b0186959_9505433.jpg

[PR]
by school-pc | 2009-07-10 09:55 | Comments(0)

Excelワザ 生まれてから何年何ヶ月何日?

DATEDIFという関数があります。
開始日と終了日との間の経過年月を出してくれます。


この関数は、
b0186959_16151343.jpg
関数の挿入からは出てきません。

キーボードから入力しないと使えません。
(Excelには幾つかそんな関数があります。)

=DATEDIF(開始日,終了日,単位)
 単位は”y”は満年数
     ”ym”は満月数
     ”md”は満日数を出します。

もう一つ、〖&〗
(アンパサンド(アンドマーク))を使うと、
文字や式を結合することができます。
例えば=山田&花子→山田花子 となります。

B1のセルに、生年月日を入力
B2のセルに、今日の日付を
TODAY関数
で入れておきます。=today()
b0186959_1635696.jpg


セルB4に式を立てます。
=datedif(B1,B2,"y")&"年"&
ここまで入力したら、datedif(B1,B2,"y")&"年"&を
コピー(Ctrl+C)
貼り付け(Ctrl+V)、2回
=datedif(B1,B2,"y")&"年"&datedif(B1,B2,"y")&"年"&datedif(B1,B2,"y")&"年"&
これを修正します。

=datedif(B1,B2,"y")&"年"&datedif(B1,B2,"ym")&"ヶ月"&datedif(B1,B2,"md")&"日"
b0186959_1631791.jpg


ということで、
僕は生まれてから『4年と10ヶ月と22日』
たちましたワン!
b0186959_16393592.jpg

[PR]
by school-pc | 2009-07-04 16:41 | Comments(2)

Excelワザ なぜ「1.63 」

前日のExcelのワークシートを方眼紙として使う場合、
なぜ列幅を「1.63」にするか
-----------------------------------------------------------------
エクセルの行の高さは『13.5 (18ピクセル)』ポイント数です
 13.5ポイントは25.4÷72×13.5= 4.76 mmになります。
 (1ポイントは1/72インチ 1インチは25.4mm)
b0186959_20352243.jpg
 

 エクセルの列幅の単位は
 『8.38 (72ピクセル) 標準フォント(MSゴシック11ポイント)で
  表示できる文字数』です
 (半角)で8文字と少し表示できる幅という意味です。
 プリンタのドライバと関係するので一概に何 mm とか
 計算できません。
b0186959_20353949.jpg

 
方眼紙にするといっても、セルがまったくの正方形に
なったわけではなく、あくまでも正方形に一番近いと
いうことだけです。列幅1.63という数値は、セルの
標準の高さ13.5ポイント(18ピクセル)に合わせて、
ともに18ピクセルにして合わすということです。
(Excel2000からピクセル数も出るようになったので、
この数字を使って合わしています。
b0186959_20354634.jpg

[PR]
by school-pc | 2009-07-02 19:47 | Comments(0)

Excelワザ シートを方眼紙として使う-2

方眼紙で地図も描いてみよう!
b0186959_1658343.jpg

オートシェイプで
 直線はShift+ドラッグ
  で水平線、垂直線、15度ずつ傾く
 オートシェイプ上でダブルクリック
  →線の色(灰色)
  →線の太さ(10Pt)
 コピーはCtrl+ドラッグ
 水平、垂直にコピーする時は
 Ctrl+Shift+ドラッグ
b0186959_1721781.jpg

 JRの線路の描き方
 オートシェイプ→曲線→クリック、クリック、最後にダブルクリック
 線の太さ→10P、色→黒
 これをCtrl+ドラッグでコピー
 コピーしたほうを
 線の太さ→8P、色→白、線の種類→破線
 で黒い線の上に重ねる
b0186959_16573389.jpg

[PR]
by school-pc | 2009-07-01 17:03 | Comments(1)

Excelワザ シートを方眼紙として使う-1

エクセルで何でも(文書も、地図も、計算も)、
作成する人は結構おられます。
ここで、エクセルで文書、地図などを見栄えよく作るコツは、
サイズの異なる入力欄をきれいに配置する事。
そのために、シートを「方眼紙」状にして、
必要に応じてセル結合し、
好みのサイズの入力欄を作ればOK!

まず、
 1.シート全体を選択
   全セル選択ボタンクリック
b0186959_15254988.jpg

 2.任意の列上で右クリック、
  「列の幅」「1.63」
 すると、全てのセルが小さな正方形になる。→方眼紙状
b0186959_15291173.jpg

b0186959_15304182.jpg

必要なサイズでセルを結合して、そのサイズのセルをコピーする時はオートフィルで
b0186959_1551559.jpg

こんな感じで…
b0186959_15584656.jpg

[PR]
by school-pc | 2009-07-01 15:59 | Comments(0)

Excelワザ テストの点数、星いくつ~~~

REPT(リピート)関数というのがあります。
この関数を使うと、テストの点数などを絵グラフのように、
記号や文字で表示できます。

b0186959_21172277.jpg


REPT関数
書式
=REPT(文字列,繰り返し回数)
文字列 繰り返す文字列を指定します。
繰り返し回数 文字列を繰り返す回数を、正の数値で指定します。

解説
繰り返し回数に 0 を指定すると、空白文字列 ("") が返されます。
繰り返し回数に整数以外の値を指定すると、小数点以下が切り捨てられます。

10点満点の場合
セルC2の式 =REPT("☆",B2)

100点満点の場合
セルC2の式 =REPT("☆",B2/10)
10で割るのがミソ
b0186959_21262910.jpg

98点も切捨てで、☆9つとなります。
[PR]
by school-pc | 2009-06-23 21:32 | Comments(2)