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

コメントを頂いた「げん」様

学籍番号を選択したら、写真を表示するように。
ちょっと作ってみましたが…
こんなんでどうでしょう?

1.まず人数分の写真を用意。
  縦4㎝,横3㎝サイズ
  とりあえず25名分用意。
  写真のファイル名は
  学籍番号1001.jpg~1025.jpgとして用意。
b0186959_1545151.jpg

2.Sheet1のシート名を「学生名簿」
  Sheet2のシート名を「写真付き学生証」とします。
b0186959_1551220.jpg

3.学生名簿シートの
  ・A列に学籍番号
  とりあえずセルA2からA51まで
  「_1001」~「_1050」まで用意
  (※後の操作、名前の定義で、
  先頭文字に数字や、セル参照と同じ文字(A1001等)
  は使えないので、アンダーバー_を使い、
  「_1000」~「_1050」までとしました。)
  ・B列に氏名
  ・C列に写真を用意
  ・C列の列幅は「14.5」
  ・C列の行の高さは「124」
   (縦4㎝,横3㎝サイズに合わせるため)
b0186959_1563168.jpg


  ※写真を貼るときに、
  挿入→図(画像)で貼る方法と、
  =HYPERLINK("ファイルのパス\1001.jpg")
  HYPERLINK関数で、写真の入ったホルダーから直接写真を見に行き、コピー、貼り付けの方法がありま  すが、いずれも貼り付けの操作は必要です。ただ、D列にHYPERLINK関数を入れておくと、学籍番号と  写真の合致に誤りが生じないのでより確実かもしれません。
b0186959_1573655.jpg

4.名前を定義
  ・セルA2~A51を「学籍番号」
  ・セルA2~C51を「学生名簿」
  ・写真が入っているC列のセルC2~C51に
   それぞれの学籍番号を名前定義します。
  例えば、セルC2は「_1001」のように、
  一人ずつ名前を定義します。
5.「写真付き学生証」シートを表示。
  ページ設定画面で
  ・余白の設定
   →上余白:1.5 下余白:0.5
   →左余白:2 右余白:1.5
  ・列幅設定
   A列、C列、E列、G列、I列:列幅2
   B列、D列、F列、H列、J列:列幅14.5
  ・行の高さ
   1行目:12
   2行目・3行目:14
   4行目:124
   5行目:4
  ・全セル選択ボタンをクリック
   フォントを:MSゴシック、10ポイント
  ・セル範囲B1~B4に格子の罫線を引き、
   外枠太線。
6.セルB1に「学籍番号」と入力。
  セルB2にデータの入力規則設定。
  ・入力値の種類:リスト
  ・元の値:=学籍番号 と設定。
   すると、リストから選択出来るようになります。
b0186959_15102656.jpg

7.セルB3に式を入力
 =IF(B2="","",VLOOKUP(B2,学生名簿,2,FALSE))
   (もし学籍番号が空白なら空白、番号が入れば、
    学生名簿の2列目から完全一致のものを表示。)
   すると、番号に該当する氏名が表示されます。
b0186959_15111431.jpg

8.セル範囲B1~B4をコピー
  D1、F1、H1、J1に貼り付け。
9.1行目~5行目をコピー
  6行目、11行目、16行目、21行目に貼り付け。
10.これで、5×5 25名分の
  写真付き名学生証を入れる台ができました。
  「_1001」~「_1025」まで学籍番号をリストから選んでおきます。
b0186959_15114442.jpg

11.次にまた名前の定義をします。
  リストから学籍番号が入るようになった
  ・セルB2に
  「学生証01」という名前を定義します。
b0186959_15122721.jpg

  同様に残り24枚の学籍番号の入ったセル
  D2、F2、H2、J2
  B7、D7、F7、H7、J7
  B12、D12、F12、H12、J12
  B17、D17、F17、H17、J17
  B22、D22、F22、H22、J22
  に、「学生証02~学生証25」
  と名前を定義します。
12.さらに、名前の定義の画面で
  ・新規作成
  ・名前:「学生写真01」
  ・参照範囲:=INDIRECT(学生証01)
   と、参照範囲に関数式を入れます。
b0186959_151364.jpg

  同様に
  ・名前:「学生写真02~25」
  ・参照範囲:=INDIRECT(学生証02~25)
b0186959_15162750.jpg

  と、名前定義します。
13.次に学生名簿シートの
  「_1001」の学生の写真をコピー
14.写真付き学生証シートの
  写真を表示するセルB4に貼り付け。
  残り24枚の写真の入るセルにも貼り付け。
b0186959_1517458.jpg

15.学生証01セルB4に貼り付けた写真を
  選択した状態で
  数式バーに
  「=学生写真01」と入力。
b0186959_1520718.jpg

16.同様に2枚目以降の写真を選択した状態で
  数式バーに
  「=学生写真02~25」と入力
17.すると、学籍番号に合った写真が表示されます。
b0186959_1522426.jpg

18.さらに学籍番号を
  リストから選びなおすと、
  番号に応じた写真が表示されます。
b0186959_15224598.jpg


どうでしょうか、こんな感じで…?
[PR]
by school-pc | 2013-11-03 15:22 | Comments(0)

例題)
契約日(A2)を入力すると、
タイプ別の該当する日付が
計算式で出て、
該当する日付に
色がつくようにしたい。
b0186959_12183962.jpg

タイプ別の日数を出すセルB2には
=IF(B1="","",HLOOKUP(B1,$B$5:$F$6,2,FALSE))
該当日を出すセルD2には
=IF(B2="","",INDEX(INDIRECT(B1),MATCH(A2,日付検索,0),1))
の式を立てておく。

(タイプA~Eまでの日付検索する
範囲には、それぞれ
タイプA~タイプEの名前定義。
セルB1は、入力規則で、
タイプA~Eまで選択できるようにしておく。
日付1日~31日までは日付検索という
名前定義。
A~Eまで5列分の範囲は該当日検索
という名前定義。)
b0186959_11521014.jpg

INDEX関数で
、該当する範囲を
INDIRECT関数で選択し、
該当する行をMATCH関数で出し、
該当する範囲はINIRECT関数で
切り分けてあるので、1列。)

で、D2には、該当日が出ます。
次に、該当する日付に色を付けます。
条件付き書式を使い、…
b0186959_11523384.jpg

すると、
b0186959_1219030.jpg

該当する日付すべてに色がついてしまいます。
ここで、もう一つ
条件付き書式を付けます。
b0186959_1252539.jpg

=B$6<>$B$2
B6がB2と等しくなければ、
セルの色を白にする。
列に式をコピー、行は絶対参照。
b0186959_1222385.jpg

2つの条件付き書式を付けることにより、
複数色がついていたのが、
タイプに合致した列の日付のみに
色が付きます。
b0186959_12211181.jpg

結構苦戦したので…備忘録。(^_^;)
[PR]
by school-pc | 2013-10-11 10:52 | Comments(0)

ルビについて

Web上の文章を
コピー、貼り付けして、
ルビをつけようとする…

1.まず、Wordに貼り付け
ルビ設定。
b0186959_20272145.jpg

すると、1段落分しか
ルビがつかない。
b0186959_20203513.jpg


ふーむ(-"-)

今度は、
2.Excelでルビをつけてみよう。

Web上の文章を
コピーして、
テキスト形式で貼り付ける。
b0186959_2028973.jpg

A列に折り返して表示。
b0186959_20284651.jpg

これにルビ設定のボタンを押しても
行高が高くなるだけで、
ルビはつかない。
(セル内に文字の読み情報が
入っていないので表示されない。)
そこで、3行マクロを書く。

Alt + F11
挿入→標準モジュール

Sub フリガナ表示()
Selection.SetPhonetic
Selection.Phonetics.Visible = True
End Sub

すると、

1つのセル分だけに、
ルビが入った。
b0186959_20441836.jpg

で、さらに無理にA1のセルに
全部の文章を入れて…

フリガナ表示マクロを実行。
が、
1段落分しか、フリガナ表示しなかった。
b0186959_20445712.jpg

ふ~~む(-"-)
文字数の制限があるのかなぁ……
[PR]
by school-pc | 2013-09-25 20:30 | Comments(0)

引き続き1級表計算練習問題
課題2設問5
なかなかの難易度!
解答の計算式が
提供されていないので…
解答通りの結果になったが、
この計算式でどうかなぁ。

問題)
b0186959_1854140.jpg

最高得点、最低得点、10点につき
丸数字1つに置き換えた簡易グラフを作る。

 ・記号1つあたり10点とし、
  小数点以下第1位を四捨五入した数とする。
 ・最高得点の丸数字は、
  最高得点の中で値の大きい順に
  ①②③・・・と表示する。
 ・最低得点の丸数字は、
  最低得点の中で値の小さい順に
  ①②③・・・と表示する。
b0186959_18542262.jpg

考え方)
最終的に出したいのは、
REPT関数で順位の丸数字を、
得点を10で割って、小数点第1位を
四捨五入して出した整数分,繰り返す。
RANK関数で順位を出した数字を
SUBSTITUTE関数で置き換える。
その時、1~13まで順に
置き換えたいので、
CHOOSE関数を使う。
で、
=REPT(SUBSTITUTE(RANK(E5,E$5:E$17,0),RANK(E5,E$5:E$17,0),CHOOSE(RANK(E5,E$5:E$17,0),"①","②","③","④","⑤","⑥","⑦","⑧","⑨","⑩","⑪","⑫","⑬")),ROUND(E5/10,0))

b0186959_195372.jpg

もっと簡単な式があるかなぁ…
[PR]
by school-pc | 2013-07-23 23:17 | Comments(0)

b0186959_1739687.jpg

b0186959_17415390.jpg

リクエストが多いので、作り方を伝授致します。
社員証をA4枚に10名分、印刷できます。
1.まず社員人数分の写真を用意。
  Excelの表内のサイズに合わすと、
  約 縦2cm、横3cm位のサイズにしておく。
2.Sheet1のシート名を
  「社員証」にします。
  この社員証シートに社員証の台を作ります。
3.全セル選択→列の幅「1.63」に設定。
  (方眼紙にするため)
4.以下のように作ります。
b0186959_17431732.jpg

  「指名」の横のセルN5~S5は
  セルを結合して均等割付
  「社員番号」「所属部署」の横のセル
  N7~S7,N9~S9は
  セルを結合して左揃えにしておく。
  セルC5~I9はセルを結合して中央揃え
5.これを1列あけて横にコピー
  (B2~T12のデータをV2~AN12にコピー)
  さらに、2行~12行をコピーして
  14行~24にコピー
  26行~36行にコピー
  38行~48行にコピー
  50行~60行にコピー
  これで、10枚の社員証の台ができました。
b0186959_1734636.jpg

6.Sheet2のシート名を「社員名簿」
  にし、社員の名簿一覧表を作ります。
  (20人分の社員名簿を作りました。)
  A列に社員名、B列に写真
  C列に社員コード、D列に所属部署
  今回はA列の社員名を昇順に
  並べておきました。
  (浅田真央が1番上)
b0186959_17303892.jpg
b0186959_17305061.jpg
 
7.範囲に名前を定義していきます。
  1.セル範囲A2~A21を「社員名」
  2.セル範囲A2~D21を「社員名簿表」
  3.セル範囲B2~B21の
  写真が入っているセルに
  それぞれの社員名を名前定義します。
  例えば、B2は浅田真央のように
  B2~B21、一人ずつ20人の名前を定義します。
b0186959_19183093.jpg

8.社員証シートのセルN5(氏名の横のセル)に
  社員名をリストから入力できるように
  データの入力規則で設定します。
b0186959_19193769.jpg

  これで、リストから氏名が入ります。
b0186959_19205372.jpg

  残り9枚の社員証にも
  同様にリストから入るように設定。
  10枚の社員証にすべてリストから
  氏名を選択しておいてください。
9.ここで、また、
  リストから氏名が入るようになったセルN5に
  「個別社員名01」という名前を定義します。
  同様に残り9枚の社員証の
  氏名が入るようになったセルにも名前を定義します。
  (セルAH5は「個別社員名02」、
   セルN19は「個別社員名03」、
   セルAH19は「個別社員名04」…
   のように「個別社員名10」まで
  名前定義します。
b0186959_20503846.jpg

b0186959_21243814.jpg

10.社員番号、所属部署名は
  IF関数と、VLOOKUP関数で出します。
  社員番号N7に入る式
  =IF(個別社員名01="","",VLOOKUP(個別社員名01,社員名簿表,3,FALSE))
  所属部署名N9に入る式
  =IF(個別社員名01="","",VLOOKUP(個別社員名01,社員名簿表,4,FALSE))
  同様に残り9枚の社員証に
  式を入れます。
b0186959_21212261.jpg

※これで、社員名をリストから選択すると、
社員番号、所属部署名が出るようになりました。

さて、ここからが、
氏名を選ぶと、写真が表示されるワザ!
11.名前の定義を開きます。
   新規作成で、
   名前:「社員画像01」
   参照範囲:=INDIRECT(個別社員名01)
   参照範囲に関数式を入れます。
b0186959_22242261.jpg
 
  同様に社員画像02~社員画像10
  までの名前を定義し、
  それぞれの参照範囲に
  =INDIRECT(個別社員名02)~10
  の数式を入れます。
b0186959_22244967.jpg

12.社員名簿シートの
  B列最初の社員の写真をコピー
  (浅田真央)
13.社員証シートの
   写真を表示するセルC5に貼り付け。
   残り9枚の社員証にも、貼り付ける。
b0186959_23113987.jpg

14.社員証1枚目のセルC5に貼り付けた、
   写真を選択した状態で、
   数式バーに
  「=社員画像01」と入力。
15.同様に2枚目以降の社員証の
  写真を選択した状態で、
  数式バーに
  「=社員画像02~10」と入力。
  すると、氏名に合った写真が表示されます。
  ※多少ずれるので、
  枠内に収まるように、
  図のトリミングで
  調整してください。
完成!
b0186959_23115622.jpg

リストから氏名を選ぶと、
写真も変わるでしょう?

久々の大作!
反響待ってますよ~~(^_-)-☆
MFさん、20~30人の従業員数なら
これでいけませんかね?

※補足
印刷して、再検討.
用紙の上下の余白を
「上0.5cm、下0cm」にし,

それぞれの社員証の最終行の
行の高さを「24」ポイント
(12,24,36,48,60行目)
行の上下の配置を
上揃えにして、

下から3行目
(10,22,34,46,58行目)
を「22」ポイント
行の上下の配置を
下揃え
にすると、名刺サイズの
大きさになりました。
b0186959_1035139.jpg

[PR]
by school-pc | 2013-04-22 12:50 | Comments(0)

2012-12-14の記事
Excelワザ 社員証を写真つきで作成!の改良版!
A4、1枚の用紙に
(ちょっと厚紙のほうがいいかも)
従業員10名分の社員証を
写真つきで印刷できます!
b0186959_19442455.jpg

追加内容は
前回の要領に
個別社員名01~10と
社員画像01-~10まで
の名前定義
を追加。
そうすると、10枚のカードに
10人ずつの
写真入り社員証の出来上がり!

名前を選ぶだけで、写真も
自動的に変わります。
b0186959_19525556.jpg

[PR]
by school-pc | 2013-04-18 19:15 | Comments(3)

例)エクセルのシートに
b0186959_9143830.jpg

メモ帳のアンケートデータをインポート
b0186959_9144999.jpg

インポート後
b0186959_915081.jpg

これを
金額のグループ化をしようとすると、
Excel2003では、グループ化できません。
と出ますが、
2007以降では、ちゃんと「空白】
というグループ化の項目が出てきて、
1000円未満のグループができます。
b0186959_9141582.jpg

バージョンの違いで、
微妙にできないことがある……(*_*;
[PR]
by school-pc | 2013-02-26 18:57 | Comments(0)

★データ内の空白セルを一括で選択し、
  「0」を入れたいとき。

1.F5キー(またはCtrl+G)
 でジャンプのダイアログボックスを出し
2.セル選択ボタン→空白セルチェック
 は、よく使いますね。
b0186959_18151226.jpg

b0186959_18171076.jpg

3.選択されたセルに「0」を入力し、
 Ctrl+Enter
 で、空白セルに「0」が入りました。
b0186959_18184883.jpg


逆に、やっぱり、空白に戻したい!
★「0」の入ったセルを空白セルに戻すには
1.置換を使います。
  Ctrl+H
 検索する文字列に「0」
 置き換える文字列は空白のまま
 でOKを押すと、
b0186959_18221016.jpg
b0186959_18224147.jpg

 ありゃま、「2000、3000」が、
 ただの「2、3」
 になってしまいます。
2.ここで、もうひとひねり
 置換のダイアログボックスの
 オプションをクリック。そこで、
 「セル内容が完全一致のものを探します。」
b0186959_200354.jpg

3.「0」と完全に一致するものだけ探して、
 空白にするわけですね。
b0186959_2014578.jpg

これで、
空白を「0」に
「0」を空白に
が出来ます!(^_-)
[PR]
by school-pc | 2013-02-14 18:23 | Comments(0)

あんまり難しくない課題。
10年以上来ていただいております
生徒さんたちがおられまして…
もう、ずいぶんいろんなこと教えましたんやけど…
すっかり忘れておられることも多々ありまして…
行ったり、来たりで…
何をしようかな、
と課題で悩むときもあるわけで…(・。・;

今日は、EXCELの課題をしよう!
顔文字が出てくる、絵グラフを
REPT関数で作ってみましょう。
b0186959_1872695.jpg


★B列は、経過日を
「DATEDIF関数で出します。
「=DATEDIF($A$4,A4,"D")」
★D列は、表示形式をユーザー定義で
「#,##0”g”」の表示形式にします。
★E列には、REPT関数を使って…
「=REPT("(^-^)",D4/1000)」

使った関数は2つです。
=DATEDIF(開始日,終了日,単位)
開始日から終了日までの
年数や月数、日数を表示できます。
単位:“Y”満年数 ”M“満月数 ”D“満日数
=REPT(文字列,繰り返し回数)
文字列を指定された回数だけ
繰り返して表示します。


で、こんな感じ…
b0186959_18131497.jpg

あと、応用で、
身長と体重の
複合グラフも作りましょう!
b0186959_18154983.jpg

[PR]
by school-pc | 2013-02-06 18:16 | Comments(0)

Kさんから、ダメだし(-.-)
ハイフンの次の番号もきちんと昇順に並べたい!
例)
b0186959_116173.jpg

こんな表を単純に昇順に並べ替えると↓
b0186959_117483.jpg

数字と文字に分かれてしまい、
さらに、ハイフンの次の番号は
101-111→101-3→101-589
てな並べになってしまう。
これを、
101-3→101-111→101-589
の順で、枝番号も昇順にしたいわけね。

わかりました!
これで、どうでしょう?
1.処理用の列を2列用意します。
 A列の右に2列挿入。
2.セルB2には、前日と同じ
 ハイフンより前の数字を取り出します。
=IF(ISNUMBER(A2)=TRUE,A2,VALUE(LEFT(A2,FIND("-",A2,1)-1)))
3.さらに、セルC2には
 ハイフンより後ろの数字を取り出します。
=IF(ISNUMBER(A2)=TRUE,0,VALUE(RIGHT(A2,LEN(A2)-FIND("-",A2,1))))
(もし、A2が数値なら、0を表示、
(空白ではダメでした。)
数値でないなら、右から(Right関数)
ハイフンまでの数字を取り出します。
全部の文字数(LEN関数)から、
Find関数でハイフンを探し、ハイフンまでの
文字数分を引くと、右からハイフンまでの
文字数がとり出せれます。
取り出した、数字は文字になっているので、
VALUE関数で数値に戻してやります。
すると、B列、C列に
ハイフンの前と、後ろの数字が取り出せました。
b0186959_11392124.jpg

4.これを、並べ替えのダイアログボックスで、
 2つの条件で並べ替えます。
 ・最優先されるキーが「-の前の数字」
 ・次に優先されるキーが「-の後の数字」
b0186959_1142734.jpg

5.2つの条件で並べ替えると、
b0186959_11455921.jpg

101→101-3→101-111→101-589
と、並びましたねぇ。
6.必要なければ、B列、C列削除して。
 (残しておくと、番号、枝番がわかってよいかも(^_-)))
こんな感じで並び替えできましたが…
b0186959_1149581.jpg

 Kさん、これで、どうでしょう?
[PR]
by school-pc | 2013-01-12 11:52 | Comments(0)