- 2021.4.1 移転しました...
-
- このページの旧URLは
http://www10.plala.or.jp/palm84/excel_memo.html
- このページの旧URLは
- 内容古いです!
-
最終更新日は 2007.5.9、ご注意。
まだまだまだなレベルですが、ぼちぼち更新して行きます...。
参考になるサイト
- インストラクターのネタ帳
-
説明がわかりやすいです。めちゃ便利なtipsがいっぱい。
- Microsoft Office Online ホーム ページ
- Microsoft Users
ダウンロード
FAQ
- セル内改行 -
Alt + Enter
- セル内改行を検索/置換 -
Ctrl + J
- 表を図として貼り付け
- 表コピー→Shift-編集→図の貼り付け
- セルを図としてコピーする - Excel 一般機能 モーグ即効テクニック集
- オブジェクトを複数選択 - Ctrlではなく、Shiftキーを押しながらクリック
- 指数表示になってしまったJANコード(13文字)を文字列に変換するには
- 「区切り位置指定ウィザード」を使う - EXCEL生産性向上委員会 トラブル回避テクニック15
- Excel:指数表示にされてしまった数値を一気に直す : 保管庫(Yahoo!ブログより移行)
- 文字列として認識されている数値を数値形式に変換する - Excel 一般機能 モーグ即効テクニック集 - 『'』が削除される
- エラー表示を消せ! - EXCEL生産性向上委員会 トラブル回避テクニック7
- EXCEL生産性向上委員会 隠しメニュー
- 数式バーに「=」ボタンがなくなった:Excel エクセルの使い方-関数/計算式
- 曜日を表示
- 月日と同じセルに表示 - ユーザー定義で
m"月"d"日"(aaa)
など - 別セルに表示 -
=TEXT(A1,"(aaa)")
など
- 月日と同じセルに表示 - ユーザー定義で
- Excelの仕様・上限・制限値:Excel エクセルの使い方
- *や?を検索したい:Excel エクセルの使い方
- 真ん中に印刷、苦労してませんか?:Excel エクセルの使い方-印刷
- [IME] テキストサービスが有効になっている際に発生する現象について
- 顔文字入力一覧:IME 日本語入力システムの使い方
- マクロの削除 - モジュールの開放
-
マクロの選択画面から削除しただけでは、モジュールは削除されていません。Visual Basic Editor から解放が必要です。
小技集
- @IT:Windows TIPS -- Tips:ピボットテーブルでクロス集計を行う
- ピボットテーブルによる複数シートのくし刺し集計を行う - @IT
- 折りたたみ機能:Excel エクセルの使い方-データベース/検索(アウトライン)
- 拙作集
- ノートパソコンを選ぶときにキーボードまで気にしていますか? - EXCEL生産性向上委員会 基本テクニック番外編8
- 個人的には Home / End キーが単独で使えることを重視してます。
- オートフィル
-
フィルハンドルでコピーが簡単に
- 重複行を削除
-
実際には削除ではなく、元データそのままで別シートへ重複分を除き貼り付け
- フィルタオプションの設定
- リスト範囲に対象セル(列)を指定
- 「重複するレコードは無視する」にチェック
- 表示行を別シートへコピー
- 行に連番挿入
-
- 2行目から連番挿入
=ROW()-1
- 2行目から「1~7」を繰り返し挿入
=MOD(ROW()-2,7)+1
- 1の行に条件付書式を設定
- 数式が
=MOD(ROW()-1,10)=1
でパターン指定 - 数式が
=MOD(ROW()-1,7)=1
でパターン指定
- 数式が
- 2行目から連番挿入
- セルの値を分割
-
- 分割したい位置にスペースや記号などを入力
- データ→「区切り位置」
- 「カンマやタブ...」→区切り文字指定
- ふりがなを再設定
-
- 選択領域にふりがなを再設定するマクロ
Sub ふりがな() Selection.SetPhonetic End Sub
- テーブルに名前を設定
-
- 範囲選択
- 名前ボックスに名前を入力してEnter
- 配列数式
-
- 日経PC21 / エクセル(Excel)「配列数式」講座
- 日経PC21 / 初めての「配列数式」2 配列数式を作るときの考え方
- 単価*数量の合計を一発で計算(=
SUMPRODUCT
関数){=SUM(C2:C10*D2:D10)}
- 列内の実績(金額等)のみ合計
{=SUM(IF(B2:B10="実績",D2:D10*E2:E10,""))}
- (行番号の剰余から計算)
{=SUM(IF(MOD(ROW(B2:B10),2)=0,D2:D10*E2:10,""))}
テンプレート
- テンプレートの保存
-
- 拡張子を xlt に変更する
- or ファイルの種類を「テンプレート」を指定して保存する
- マクロの挿入(インポート)
-
まず、Alt-F11 で Visual Basic Editor を起動。表示→プロジェクト エクスプローラ(画面左、通常は開いてるかな)で挿入するファイルを選択
- マクロを記述したファイルを拡張子 .bas にして保存(※.txtでもインポート可能ですけど)
- ファイル→インポート でファイルを指定
または、挿入→標準モジュール で内容を貼り付け(書き込み)
JANコードの指数表示を文字列に
短縮JANは"00000"付加
- A列にデータ貼り付け
- B1 -
=IF(A1="","",IF(LEN(A1)=8,"00000"&A1,TEXT(A1,0)))
- B1を必要な分だけコピー(または、オートフィルを実行)
- B列をコピー、C列へ値の貼り付け
- 貼り付けたセルの書式を「文字列」に設定
- マクロ
-
A列にデータを貼り付けてから実行。やりなおし可能。
Sub 指数表示戻し() Columns("B:C").Select Selection.Clear Range("B1").Value = "=IF(A1="""","""",IF(LEN(A1)=8,""00000""&A1,TEXT(A1,0)))" Range("B1").Copy Range("A65536").Select 'A列の最下セルへ移動 Selection.End(xlUp).Select 'Ctrl + ↑ ActiveCell.Offset(0, 1).Select '右のセルへ移動 Range(Selection, Selection.End(xlUp)).Select 'Ctrl+Shift+↑ ActiveSheet.Paste '数式貼り付け Application.CutCopyMode = False Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlValues 'C1へ値の貼り付け Selection.NumberFormatLocal = "@" '書式を文字列に Range("A:C").Columns.AutoFit '列幅最適化 End Sub
チェックディジット
- A列にデータ貼り付け
- B列桁数チェック・短縮JANは「00000」付加
=IF(A1="","",IF(LEN(A1)=8,"00000"&A1,IF(LEN(A1)=13,TEXT(A1,0),IF(LEN(A1)>13,"桁多い!",IF(LEN(A1)<13,"桁少ない!","エラー")))))
- C列に判定結果
=IF(A1="","",IF(ISERROR(IF(P1=S1,"OK","ちゃいまんがな!")),"エラー",IF(P1=S1,"OK","ちゃいまんがな!")))
- D列 - P列に桁毎に抜き出し
=IF($A1="","",VALUE(MID($B1,COLUMN()-3,1)))
- Q列
=IF(A1="","",D1+F1+H1+J1+L1+N1+3*(E1+G1+I1+K1+M1+O1))
- R列
=IF(A1="","",10-(MOD(Q1,10)))
- S列
=IF(A1="","",+IF(R1>9,0,R1))
B1:S1
を選択してフィルハンドルをWクリック。
- マクロ
-
白紙シートのA列にデータを貼り付けてから実行
Sub チェックディジット() Columns("B:S").Clear Range("B1").Formula = "=IF(A1="""","""",IF(LEN(A1)=8,""00000""&A1,IF(LEN(A1)=13,TEXT(A1,0),IF(LEN(A1)>13,""桁多い!"",IF(LEN(A1)<13,""桁少ない!"",""エラー"")))))" 'B1 に数式入力 Range("C1").Formula = "=IF(A1="""","""",IF(ISERROR(IF(P1=S1,""OK"",""ちゃいまんがな!"")),""エラー"",IF(P1=S1,""OK"",""ちゃいまんがな!"")))" 'C1 に数式入力 Range("B1").Select 'B1 に条件付書式 Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""桁少ない!""" With Selection.FormatConditions(1).Font .Bold = True .ColorIndex = 3 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""桁多い!""" With Selection.FormatConditions(2).Font .Bold = True .ColorIndex = 3 End With Range("C1").Select 'C1 に条件付書式 Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""エラー""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""ちゃいまんがな!""" Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("D1").Formula = "=IF($A1="""","""",VALUE(MID($B1,COLUMN()-3,1)))" 'D列 - P列に桁毎に抜き出し Range("D1").Copy Destination:=Range("E1:P1") Application.CutCopyMode = False Range("Q1").Formula = "=IF(A1="""","""",D1+F1+H1+J1+L1+N1+3*(E1+G1+I1+K1+M1+O1))" 'Q列 Range("R1").Formula = "=IF(A1="""","""",10-(MOD(Q1,10)))" 'R列 With Range("S1") 'S列 .Value = "=IF(A1="""","""",+IF(R1>9,0,R1))" .Interior.ColorIndex = 6 End With Columns("A:C").ColumnWidth = 15 '列幅 Columns("D:P").ColumnWidth = 2 Columns("Q:S").ColumnWidth = 3 Range("B1:S1").Copy 'B1 - S1 をコピー Range("A65536").Select 'A列の最下セルへ移動 Selection.End(xlUp).Select 'Ctrl + ↑ ActiveCell.Offset(0, 1).Select '右のセルへ移動 Range(Selection, Selection.End(xlUp)).Select 'Ctrl+Shift+↑ ActiveSheet.Paste '数式貼り付け Application.CutCopyMode = False Range("A1").Select End Sub
ふりがな編集
この下のはだめだめなので、もうちっとましに使えそうなの作ってみました。
- ふりがな編集用マクロ(軽い方 - 追加するとエラー出るかも)
- ふりがな編集用マクロ(ちょっと重い)
- ふりがなが付いていないデータにマクロでふりがなを付けることができます(但し、正確とは限らない)
- 修正したふりがなをデータに再設定できます
- ボタンでカナの切替ができます
こんなかんじです。
- マクロの挿入
-
- 上記ファイルをダウンロード
Alt-F11
で Microsoft Visual Basic を開くCtrl-R
(または表示メニューから)でプロジェクト エクスプローラを開く- 左ツリーで現在編集中のファイルを選択
- 右クリックから「ファイルのインポート」
- ダウンロードしたファイルを指定
- 標準モジュールの Modulexに追加される
- マクロの実行
-
- Excelに戻る
Alt-F8
でマクロ選択画面を出す- 「FORMAT」を実行
- 数式入力 & ふりがな再設定マクロ
-
あんまり使えなさそうです。すみません。正確に出ないことも多いので修正は必要。
- A列にデータ
- B列に計算式
- C列に結果
データをA列に貼り付けてから実行
Sub ふりがな設定() Range("A65536").Select Selection.End(xlUp).Select 'Ctrl + ↑ Range(Selection, Selection.End(xlUp)).Select 'Ctrl+Shift+↑ Selection.SetPhonetic '選択範囲にふりがな再設定 With Selection.Phonetics .CharacterType = xlKatakanaHalf .Alignment = xlPhoneticAlignNoControl .Font.Name = "MS ゴシック" .Font.Size = 9 .Visible = True End With Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(ASC(PHONETIC(RC[-1])))" Selection.Copy Range("A65536").Select Selection.End(xlUp).Select 'Ctrl + ↑ ActiveCell.Offset(0, 1).Select '右のセルへ移動 Range(Selection, Selection.End(xlUp)).Select 'Ctrl+Shift+↑ ActiveSheet.Paste '数式貼り付け Application.CutCopyMode = False Selection.Copy Range("C1").Select Selection.PasteSpecial Paste:=xlValues 'C列へ値の貼り付け Application.CutCopyMode = False Range(Selection, Selection.End(xlToLeft)).Select Selection.WrapText = True '折り返し表示 Columns("A:C").ColumnWidth = 35 '列の幅を設定 Range("A65536").End(xlUp).Select 'Ctrl + ↑ Range(Selection, Selection.End(xlUp)).Select 'Ctrl+Shift+↑ Selection.Rows.AutoFit '行の高さを調整 End Sub
- やりなおしマクロ(オールクリア)
Sub やりなおし() Columns("A:C").Select Selection.Phonetics.Visible = False Selection.Clear Range("A1").Select End Sub
- 半角カタカナ -
.CharacterType = xlKatakanaHalf
- 全角カタカナ -
.CharacterType = xlKatakana
- ひらがな -
.CharacterType = xlHiragana
関数
- ふりがな表示 -
PHONETIC
- 半角を全角に -
JIS
- 全角を半角に -
ASC
- 文字数 -
LEN
- 文字数(バイト数) -
LENB
- 空白の除去 -
TRIM
- 置換 -
SUBSTITUTE
- 参照先のセルが削除されてもエラーにならない -
=INDIRECT("Sheet2!A1")
- 「リンク貼り付け」の場合は参照先のセルを削除するとエラーになる
- コード番号(10進数)を文字に -
CHAR
- 文字を10進数に -
CODE
- 2進数を10進数に -
BIN2DEC
- 2進数を16進数に -
BIN2HEX
- 単価*数量の合計を一発で計算 -
SUMPRODUCT
- SUBTOTAL
-
- SUBTOTAL関数の引数一覧:Excel エクセルの使い方-関数/計算式-数学・統計
- フィルタ実行時に連番を表示させたい-SUBTOTAL関数:Excel エクセルの使い方-データベース/検索
- 小計を除いて合計
- フィルタ実行時に表示セルのみ合計
- 各種統計値の算出
etc
ほなね