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

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)

Excelワザ 住所から都道府県だけ抜き出す

Excelで作った住所録から、都道府県名と都道府県名以下とに分けて表示したい時

b0186959_1874548.jpg


これにはちょっとした予備知識がいります。
都道府県は3~4文字で構成されます。ほとんどの場合、3文字目を指定すれば、都道府県と市区町村以降を区切ることができます。「北海道」や「東京都」は3文字、府は「大阪府」と「京都府」でいずれも3文字でOKです。しかし、4文字で区切らなければいけない県は3つ存在します。
 ・神奈川県 ・鹿児島県 ・和歌山県
 これを考慮に入れ4文字目が「県」の場合は4文字目で区切りそれ以外は3文字目まで抜き出せば良いことになります。
 データを抜き出すために必要な関数は以下の通りです。
  =IF(論理式,真の場合,偽の場合) 
   論理式:4文字目が"県”の場合
   真の場合:4文字目まで抜き出す  偽の場合:3文字目まで抜き出す
  =LEFT(文字列,文字数)
   文字列の先頭から指定した数の文字を返します。
   都道府県を抜き出すのに利用します。
  =MID(文字列,開始位置,文字数)
   文字列の指定した位置から、
   指定された数の文字を返します。
   4文字目が「県」の場合、といった論理式を指定する
   場合などに利用します。
  =RIGHT(文字列,文字数)
   文字列の右端から指定した数の文字を返します。
   ここでは、住所から都道府県を取り除いた住所を
   抜き出すのに利用します。
  =LEN(文字数)
   文字列の長さ(文字数)を返します。
   全角・半角は問わず1文字は「1」として処理します。
   ここでは、住所の文字数を求めるときに利用します。
 別解
  =SUBSTITUTE(文字列,検索文字列,置換文字列)
   文字列中の指定した文字を新しい文字で
   置き換えた結果を返します。
   ここでは、都道府県の部分を「””」空白で返します。

<式>
D2のセルに=IF(MID(C2,4,1)="県",LEFT(C2,4),LEFT(C2,3))
E2のセルに=RIGHT(C2,LEN(C2)-LEN(D2))
b0186959_1823573.jpg


別解
F2のセルに=SUBSTITUTE(C2,D2,"")
[PR]

by school-pc | 2009-06-20 18:28 | Comments(0)