eightban's homepage

2.Ms Access,Excel(CONTENT)

このページの最終行に移動

検索画面(access)



データベースを指定項目で検索するサンプルです

1.条件を入れて抽出ボタンを押してください。
2.クエリの抽出条件がポイントになります。
Like [Forms]![F_検索]![code1抽出条件] & "*"
3.コンボボックスを使用しています。table1,table2を参照
4.最下部に終了ボタンがあります。
5.マクロでautoxecを利用し画面を表示しています。
6.モジュール(vba)は使っていません
・download


CSVファイルを読込み、指定セルにセット(excel)

CSVを読み込み決まった場所にセットするサンプルです

a.csvを読み込みますが、モジュールの中でcsvファイルがあるフォルダを変更後、マクロを実行してください。
(現状ではc:\a.csvを読込むようにしています)



愛媛,1,250
青森,300,3
長野,110,90



	りんご	みかん
愛媛	1	250
青森	300	3
長野	110	100

・download


確認メッセージを表示させないようにする(access)

更新クエリや削除クエリを行うときに確認メッセージがでますが、これを回避する方法です。



下記の関数をモジュールに作成しておきます。
Public Function Confirm_off() As Integer
　DoCmd.SetWarnings False
End FunctionDoCmd.SetWarnings True

Public Function Confirm_on() As Integer
　DoCmd.SetWarnings True
End Function

マクロを作成するときに、次のように使ってください。

プロシージャの実行　/ プロシージャ名 = Confirm_off()
更新(削除)クエリ
プロシージャの実行　/ プロシージャ名 = Confirm_on()


なお、ランタイム版でない場合は
[ツール]-＞[オプション]-＞[編集/検索]タブの[確認]でチェックを外してもできます。


確認して終了させるようにする(access)

フォームに[終了]という名前のボタンを作成してください。
[プロパティ]-＞[イベント]タブ-＞[クリック時]-＞[イベント プロシージャ]の横の[...]をクリック
下記コードを追加してください。
Private Sub 終了_Click()
　 If MsgBox("終了しますか", 1 + 32, "確認") = 1 Then
　　DoCmd.Quit
　Else
　　Exit Sub
　End If
End Sub


日付から曜日をつける(excel)

	A	B
1	日付	曜日
2	2004/02/08	(日)
3	2004/02/09	(月)
4	2004/02/10	(火)




B2のセルに　=A2
B2のセルの書式設定-＞表示形式-＞ユーザ設定-＞種類(T)
(aaa)

B2をコピーしB3,B4にペースト



固定長ファイルを読込み、データベースを更新する(access)

DAOを用いSQLで固定長ファイルからaccessのdbを更新するサンプルです

まず、c:\testのフォルダに解凍してください。
kosin.mdbのモジュールにmodule1がありますので、これを選択するとvbの画面がでます。
vbの画面でメニューバーの[ツール]-[参照設定」の中から(Microsoft DAO 3.6 Object Library)にチェックを入れてください
始めにsyohin1.datからsyohin.datを作成(コピー)します。
kosin.mdbのマクロから更新を実行してください。 2件追加されます。
次にsyohin2.datからsyohin.datを作成(コピー)します。
kosin.mdbのマクロから更新を実行してください。 1件変更され、1件削除されます。

*入力データの先頭の1桁は更新区分です。A=追加,M=修正,D=削除です。
*環境が異なる場合はModule1を変更してください
*名称の所は全角のみの文字数で設定してあります。(半角・全角が混在するとうまく処理できません)
*対象となるデータベース名はsyohin.mdbです。
・download


TAB区切りファイルを読込み、データベースを更新する(access)

DAOを用いAddnewメソッドでTAB区切りファイルからaccessのdbを更新するサンプルです

まず、c:\testのフォルダに解凍してください。
kosin2.mdbのモジュールにmodule1がありますので、これを選択するとvbの画面がでます。
vbの画面でメニューバーの[ツール]-[参照設定」の中から(Microsoft DAO 3.6 Object Library)にチェックを入れてください
始めにsyohin3.datからsyohin.datを作成(コピー)します。
kosin2.mdbのマクロから更新2を実行してください。 2件追加されます。
次にsyohin4.datからsyohin.datを作成(コピー)します。
kosin2.mdbのマクロから更新2を実行してください。 1件変更され、1件削除されます。

*入力データの先頭の1桁は更新区分です。A=追加,M=修正,D=削除です。
*環境が異なる場合はModule1を変更してください(CSV区切りにするなど)
*対象となるデータベース名はsyohin2.mdbです。
・download

途中経過の処理件数を表示(access)



途中経過の処理件数を表示するサンプルです

1.マクロ更新中を実行させてください
・download

フォームで選択した項目に色をつける(access)



フォームで選択した項目に色をつけるサンプルです

フォームにテキストボックスを作成した後、テキストボックスの背景色を黄色にし、テキストボックスの背景スタイルを透明にします。
・download

POSコードを文字列で表示させる(excel)

POSコード(JANコード)[4988123456781]を入力すると[4.98812E+12]と表示されてしまいます。
書式→セル→表示形式で数値とすれば、[4988123456781]になりますが
文字列で表示させるには、書式→セル→表示形式で文字列とした後、
該当セルをダブルクリックすると変わります。



4つ以上の項目でSORTする(excel)

	A	B	C	D
1	メーカー	形態	発売日	品番
2	E	紙	041001	BB-2
3	E	紙	040920	BB-1
4	E	ペン	040922	AD-2
5	DD	消しゴム	040915	CC-4
6	E	紙	040901	AA-1

それぞれの項目の長さが等しければE2～E6のセルに　「= A2 & b2 & C2 & d2 」(E2の例)
Eの列にSORTキーを作り、E列でSORT

データの長さが違うときは空白をデータの後ろにつけてから、列をつなぎ合わせてSORTキーを作る
・download


先月と今月を比較をする[先月と今月のどちらかに品名があれば、すべて表示する](access)

先月に無かった分、今月に無かった分も表示して比較する方法です。

1.クエリーでSQLビューの画面で下記のように入力して野菜だけ項目のデータを作成します(重複しないように)

SELECT 野菜 FROM 今月
UNION
SELECT 野菜 FROM 先月;

2.クエリーで列が野菜だけのデータを元に先月と今月のデータと「→」で結合して表示します。

*UNION ALLとすると重複ありになります



先月


野菜	売上
きゅうり	20
トマト	10
なす	30


今月


野菜	売上
トマト	50
ピーマン	100


比較


野菜	先月売上	今月売上
きゅうり	20	0
トマト	10	50
なす	30	0
ピーマン	0	100

・download

比較して「違う」、「新規」、「削除」を判定する(access

	前日	今日
雑誌		9	(削)
単行本	2	1	(違)
漫画	12		(新)




1.モジュールに関数を作る
2.クエリ→フィールド→右クリック→ビルド→関数→chigai.Module1.Sameをダブルクリックして入力
3.Same ([比較].[昨日],[比較].[今日])に修正

・download


テーブルから名称を得る(excel)

コード、データだけのデータにテーブルから名称をつける方法です。

vlookup関数を使います。
例として、テーブルは違うシートにある場合を示します。
都道府県シートのA1～B48にコード+名称のテーブルがあり
対象シートのA列にコードがあり、B列に名称を表示させるときです。
IF(A2<>"",VLOOKUP(A2,都道府県!$A$1:$B$48,2,FALSE),"")



オリジナルデータ


コード	名産品
01	三平汁
03	わんこそば


テーブル


コード	名称
01	北海道
02	青森県
03	岩手県


合成後


コード	名称	名産品
01	北海道	三平汁
03	岩手県	わんこそば

・download

セルを相対指定する方法(excel)

例えば2つ左のセルの値を使いたいとき、C[-2]で指定できます
まず、ツール → オプション→ 全般タブの
「R1C1参照形式を使用する」をチェックします



あ	=C[-1]
=R[-1]	=R[-1]C[-1]


結果


あ	あ
あ	あ


・download

横にデータを持っている構造のデータベースを縦に無駄なく印刷(access)

下記のような横に複数、同じ項目を持っているデータを縦に無駄なく印刷する方法です。
ポイントは縦に並んでいる連結された項目をページフッタなどに持って行き、「可視をいいえ」にし
すでに項目のあった所に、非連結のテキストボックスを作成し「印刷時拡張をはい」にします。
そして、セクション詳細のプロパティのイベントで、フォーマット時イベントプロシージャーで細工をします。
非連結のテキストボックスに項目1を入れた後、改行をつけ項目5まで繋げます。





地方	1	2	3	4	5
北海道	北海道
四国地方	徳島	香川	愛媛	高知
中国地方	鳥取	島根	岡山	広島	山口


印刷


（通常）	（改良）
北海道 　　　　　北海道 　　　　　□□□ 　　　　　□□□ 　　　　　□□□ 　　　　　□□□ 四国地方 　　　　　徳島 　　　　　香川 　　　　　愛媛 　　　　　高知 　　　　　□□□ 中国地方 　　　　　鳥取 　　　　　島根 　　　　　岡山 　　　　　広島 　　　　　山口	北海道 　　　　　北海道 四国地方 　　　　　徳島 　　　　　香川 　　　　　愛媛 　　　　　高知 中国地方 　　　　　鳥取 　　　　　島根 　　　　　岡山 　　　　　広島 　　　　　山口

・download

同じ内容を「〃」で表現して印刷(access)

「重複データ非表示」を指定すると下記（通常）のようになりますが、これを「〃」で表現します。
ポイントは「〃」で表現したい連結された項目をページフッタなどに持って行き、「可視をいいえ」にし
すでに項目のあった所に、非連結のテキストボックスを作成し、「重複データ非表示をいいえ」にします。
そして、セクション詳細のプロパティのイベントで、フォーマット時イベントプロシージャーで細工をします。
非連結のテキストボックスに連結されたテキストボックスの内容を入れるのですが、
前のデータと比較して同じであれば「〃」を代わりに入れます。





1	2
愛甲郡	愛川町
愛甲郡	清川村
足柄下郡	箱根町
足柄下郡	真鶴町
足柄下郡	湯河原町


印刷


（通常）

（改良）

愛甲郡＿＿愛川町 ＿＿＿＿＿清川村 足柄下郡＿箱根町 ＿＿＿＿＿真鶴町 ＿＿＿＿＿湯河原町

愛甲郡＿＿愛川町 〃＿＿＿＿清川村 足柄下郡＿箱根町 〃＿＿＿＿真鶴町 〃＿＿＿＿湯河原町

・download

正数、負数、ゼロ、文字によって表示形式を変える方法(excel)

セルの書式設定の中で表示形式がありますが、ユーザー定義を使い下記の分類で設定できます。
(正);(負);(ゼロ);(文字列)

・ゼロを表示したくないとき
「#;#;#」

　表示形式の「#」はゼロの場合表示しませんので３番目の「#」が効いてきます。「0」にすると表示します。

・正、負、ゼロで色を変えたいとき
「[青]0;[赤]0;[黄]0;[緑]@」

　　色は[黒]・[青]・[水]・[緑]・[紫]・[赤]・[白]・[黄]の8色が使用できます。
　　「@」は文字列の意味です。

・決まった文字列を表示させたいとき
「"平成"@"年"」

　　表示形式が一つの場合は正数、負数、ゼロ、文字のすべてに対応します。

・前ゼロをつけたいとき「000123」
「000000」

・マイナスを「△」で表現したいとき「△123」
「0;"△"0」



キーボードでの操作(excel)


自分がよく使っているものです

1.[ctrl]+[c]コピー
2.[ctrl]+[v]貼り付け
3.[ctrl]+[x]切り取り
4.[ctrl]+[end]データのある最後のセルまで移動
5.[ctrl]+[shift]+[end]データのある最後のセルまで選択
6.[ctrl]+[pageup],[ctrl]+[pagedown]シートの移動
7.[ctrl]+[y]前の操作の繰り返し
8.[alt]+[enter]セル内の改行
9.[shift]+[space]行の選択
10.[ctrl]+[space]列の選択
11.[shift]+[矢印]1セルずつ続けて選択
12.[ctrl]+[1] ・・・　書式設定の画面を表示
13.[ctrl]+[p] ・・・　プリントアウト
14.[ctrl]+[home] ・・・　A1セルに移動
15.[shift]+[F11] ・・・　新しいシートの挿入
16.[ctrl]+[;] ・・・　今日の日付を挿入
17.[ctrl]+[n] ・・・　新しいブックの作成
18.[ctrl]+[shift]+[矢印] ・・・　データのあるセルまで選択


自作関数を作る(excel)

vbeではできて、excelの関数ではできないもの
式が複雑になってしまった物などは自作関数を作ったほうが良いときがあります。
ツール→マクロ→Visual Basic Editorで標準モジュールにModule1などを作成し
その中に参考として下記のような関数を作ります。(自分で好きなものを作成してください)

・ひらがなをカタカナに変換
Function StrConvKata(str As String)
　StrConvKata = StrConv(str, vbKatakana)
End Function

・カタカナをひらがなに変換
Function StrConvHira(str As String)
　StrConvHira = StrConv(str, vbHiragana)
End Function

・1からそこまでの数字の合計を計算する
Function RuiKei(i As Integer)
　RuiKei = (i * (i + 1)) / 2
End Function

セルにRuiKei(b2)など入力して使います。

他のブックでも使用したいときはアドインに登録します。
方法はファイルの種類をMicroSoft Excel アドインとして名前をつけて保存します。
(Excel 2000で確認、他は違うと思われます)
次にExcelのツール→アドインを選び、自作した関数をチェックします。
使用したいブックで自作関数を使用します。
・download



セルを書き換える方法(excel)

使用しているセル全体に変更をかけるとき


Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange
　(処理)
Next

選択しているセルに変更をかけるとき
Dim Rng As Range
For Each Rng In Selection
　(処理)
Next

例として(処理)に次のことするサンプル示します。
全角を半角にして(ひらがなも)、英小文字を大文字にする場合
With Rng.Cells
　If .Value <> Nul Then .Value = StrConv(.Value, vbKatakana + vbNarrow + vbUpperCase)
End With

空白の場合、一つ前の行のセルの値をいれる場合



A	1
	2


結果


A	1
A	2


With Rng.Cells
　If .Row <> 1 Then
　　If .Value = Nul Then .Value = Cells(.Row - 1, .Column)
　　　End If
　　　End If
End With
・download

画面の更新を止める(excel)

*画面の更新を止める
Application.ScreenUpdating = False

*画面の更新停止を解除する
Application.ScreenUpdating = True


書式設定を変更しても日付が変更されない場合(excel)

文字列で書式設定されている日付(2005/06/23)を書式設定で
日付(YYMMDD)/(050623)に変更しても変わらないときがあります。
ダブルクリックすると書式設定が反映されますが。複数行いたい場合は
セルの中が変更すればよいので、空白の文字列もしくは「0」が入った列をコピーし
形式を選択して貼り付けで、値を選び、演算で加算(減算)をします。
もしくは、「1」が入った列をコピーし、値を選び、演算で乗算します。



条件付書式の例(excel)

1.文字数　=LEN(B3) > 10
2.全角　=LENB(C3) <> LEN(C3)
3.半角　=LEN(D3)*2 - LENB(D3)
4.小数点　=TRUNC(E3) <> E3
5.OR条件　=OR(F3=1,F3=3,F3=5,F3=7,F3=11)
・download


集計した行だけをコピーする方法(excel)

[データ]→[集計]を選び集計した集計行のみ別シートにコピーしたいとき
集計行以外の行もコピーされてしまいますが、実際には集計行以外は表示
されていないだけです。そこで[編集]→[ジャンプ]を選び、セル選択を押し
可視セルをチェックすると、集計行のみ選択されますので、これをコピーします。



excelで自動実行するには(excel)

Auto_Open　マクロを作成します。



excelでマクロを強制終了するには(excel)

sub test()
if (条件)
end ←
end if
end sub

[exit sub]では、そのプロシージャーのみ抜けます。


ファイルを選択するするには(excel)

Dim OpenFileName As Variant
OpenFileName = Application.GetOpenFilename("CSVファイル,*.CSV", , "ファイルを選択してください")
If OpenFileName = False Then 'キャンセルボタンが押された場合
MsgBox "キャンセルされました"
else
MsgBox "ファイル名 : " & dir(OpenFileName)
End If

*dir関数はフルパスを外します。

[書式]
GetOpenFilename ([FileFilter],[FilterIndex],[Title],[ButtonText],[MultiSelect])



MsgBox(excel)

MsbBoxの使用方法です。
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
　prompt　:　メッセージ
　buttons　:　ボタン
　title　:　タイトルバー
　helpfile　:　ヘルプファイル
　context　:　ヘルプファイルのコンテキスト番号

buttons[button]


vbOKOnly	0	[OK]
vbOKCancel	1	[OK][キャンセル]
vbAbortRetryIgnore	2	[中止][再試行][無視]
vbYesNoCancel	3	[はい][いいえ][キャンセル]
vbYesNo	4	[はい][いいえ]
vbRetryCancel	5	[再試行][キャンセル]


buttons[icon]


vbCritical	16	警告
vbQuestion	32	問い合わせ
vbExclamation	48	注意
vbInformation	64	情報


buttons[focus]


vbDefaultButton1	0	ボタン1
vbDefaultButton2	256	ボタン2
vbDefaultButton3	512	ボタン3
vbDefaultButton4	768	ボタン4


modal


vbApplicationModal	0	excel内でMsgBoxを閉じるまで動かない
vbSystemModal	4096	すべてのアプリでMsgBoxを閉じるまで動かない


help


vbMsgBoxHelpButton

16384

Help


etc


VbMsgBoxSetForeground	65536	最前面
vbMsgBoxRight	524288	右揃え
vbMsgBoxRtlReading	1048576



戻り値


vbOK	1	[OK]
vbCancel	2	[キャンセル]
vbAbort	3	[中止]
vbRetry	4	[再試行]
vbIgnore	5	[無視]
vbYes	6	[はい]
vbNo	7	[いいえ]



(例)
Sub sample()
　dim ret
　ret = MsgBox("終了しますか？", vbYesNo, "確認")　
　If (ret = vbYes) Then
　　End
　End If
End Sub

buttonsは論理和をとることができるので、「+」で繋げられます。

・download

自作アドインをメニューから選択できるようするには(excel)

[excel2000]

[ツール]→[アドイン]でチェックしたときに、メニューに追加します。
　チェックを外したときに、メニューから削除します。

1.[ツール]→[マクロ]→[Visual Basic Editor]を起動
2.左上ペインのフロジェクトの 　VBAProject(Book1)
　　Microsoft　Excel　Object
　　　ThisWorkBookをダブルクリック
3.右ペイン上のオブジェクト部▽を押し、WorkBookを選択
4.右ペイン上のプロシージャ部▽を押し、Addininstallを選択しプロシージャ作成
5.プロシージャの中を記述

Private Sub Workbook_AddinUninstall()
　With Application.CommandBars("Worksheet Menu Bar") _
　　.Controls.Add(Type:=msoControlPopup)
　　.Caption = "MenuName" 'メニュー名
　　With .Controls.Add(Type:=msoControlButton)
　　　.Caption = "CommandName"
　　　.OnAction = "Command"
　　End With
End With

6.右ペイン上のプロシージャ部▽を押し、Addininstallを選択しプロシージャ作成
7.プロシージャの中を記述

Private Sub Workbook_AddinUninstall()
　With Application.CommandBars("Worksheet Menu Bar")
　　.Controls("MenuName").Delete
　End With
End Sub

8.左上ペインのフロジェクトの 　VBAProject(Book1)
　　Microsoft　Excel　Objectで右クリックして、挿入→標準モジュールを選びます
9.Module1に下記プロシージャを追加 Sub Command()
　MsgBox "Test"
End Sub

10.プロジェクトのプロパティ(省略可)
[ツール]→[プロジェクトプロパティ]→[全般]タブのプロジェクト名、説明をつけます。
[ツール]→[プロジェクトプロパティ]→[保護]タブのプロジェクトのロックとパスワードを設定します
11.excelの名前を付けて、ファイルの種類をMicroSoft アドイン(*.xla)として保存します。
　IsAddin プロパティがTrueとなるようです。
12.アドインにチェックするとメニューが追加されます。
13.必要なくなった場合、アドインを保存したものを削除してください。
・download


フリガナをつける(excel)

=PHONETIC(セル)

貼付けした漢字を変換する場合は、下記のマクロを作成し
漢字のセルを選択し実行します
Sub SetPhonetics()
　Selection.SetPhonetic
End Sub
・download

フリガナに変換する(excel)

Application.GetPhoneticを使用します
使用例を示します
'---------------------------
'選択したセルの漢字をカナに
'---------------------------
Sub Phonetics()
　Dim Rng As Range
　For Each Rng In Selection
　　With Rng.Cells
　　If .Value <> Nul Then .Value = Application.GetPhonetic(.Value)
　　End With
　Next
End Sub




拡張子がcsvのファイルで「001」と表示させたいとき(excel)

="001",="002",="003"



データが入っている最後の行数を求めるとき(excel)

=IF(COUNTIF(A:A,"*"),MATCH("",A:A,-1),)

まず、COUNTIFでA列に入ってる数を求めます。(ワイルドカード使用「*」)
データが存在しているとき、MATCHで何も入っていない行数を求め１行戻ります
COUNTIFがないと、データが入っていないとき#N/Aになります。

IFの条件で、
TRUE→　ゼロ以外の数値
FALSE→　ゼロまたは空白
であるので数値が入っているときのみ、MATCHを使います



excelのTrue,Falseの値(excel)

TRUE→　ゼロ以外の数値
FALSE→　ゼロまたは空白



#N/Aのとき表示させないためには(excel)

=IF(ISNA(VLOOKUPなど,"",VLOOKUPなど)
他に下記のものがあります
ISERROR　エラー値のときにTRUE
ISBLANK　空白のときTRUE
ISNUMBER　数値のときTRUE
ISTEXT　文字列のときにTRUE



ゼロのものを表示させないためには(excel)

条件付き書式で、フォントの色を白にする方法

表示させたくない場所が式ならば =IF(C1=0,"",C1)



ピボットテーブルを使用せずクロス集計する(excel)

	A	B	C	D
1	日付	支店	品番	数量
2	2/17	111	A-1	2
3	2/17	111	A-2	1
4	2/17	111	B-1	1
5	2/17	111	B-2	3
6	2/17	222	A-1	1
7	2/17	222	B-1	2
8	2/17	222	B-2	4
9	2/17	333	A-1	2
10	2/18	111	A-1	10
11	2/18	111	B-2	10
12	2/18	222	A-1	10


sumproduct関数を使用します
元データがdataという名前のシートとします。
下記のようなシートを作成します。



	A	B	C	D
1		111	222	333
2	A-1	12	11	2
3	A-2	1	0	0
4	B-1	1	2	0
5	B-2	13	4	0




B2のセルに下記の式をいれます。
=SUMPRODUCT((data!$C$2:$C$12=$A2)*(data!$B$2:$B$12=B$1),data!$D$2:$D$12)
そして、B2からD5までコピーします。

SUMPRODUCTは指定した配列の乗算の和を求めます。
SUMPRODUCT(配列1, [配列2], [配列3]..)
例えば

パン,200円,3個
牛乳,100円,2個
ケーキ,300円,1個

SUMPRODUCT({200;100;300},{3;2;1})


　200×3
+)100×2
+)300×1
-------
1100

SUMPRODUCT(B1:B3,C1:C3)のようにかくことができます


戻りますが、B2に入るものは品番がA2(A-1)かつ支店がB1(111)のもののD列を集計したものが入ります。

条件式は通常AND(条件1,条件2)となりますが、配列数式の場合は (条件1)*(条件2)となります。
また、OR(条件1,条件2)の場合は(条件1)*(条件2)となります。

真(TRUE)のときは「1」、偽(FALSE)のときは「0」を返します。
動きを示すと下記のようになります。

2/17,111,A-1,2　→　[真(1)×真(1)]×2　=2
2/17,111,A-2,1　→　[真(1)×偽(0)]×1　=0
2/17,111,B-1,1　→　[真(1)×偽(0)]×1　=0
～
2/18,111,A-1,10　→　[真(1)×真(1)]×10　=10
2/18,111,B-2,10　→　[真(1)×偽(0)]×10　=0
2/18,222,A-1,2　→　[偽(0)×真(1)]×2　=0
----------------------------------------
合計　　　　　　　　　　　　　　　　　12

・download


SUMPRODUCTを配列数式で表すには(excel)




	A	B	C
1		値段	個数
2	パン	200円	3個
3	牛乳	100円	2個
4	ケーキ	300円	1個


SUMPRODUCT({200;100;300},{3;2;1})

　200×3
+)100×2
+)300×1
-------
1100



とすると配列数式は
=SUM({200;100;300}*{3;2;1})と入れた後
[cntl]+[shift]+[enter]を押します
↓
{=SUM({200;100;300}*{3;2;1})}のように{}が付きます

SUMPRODUCT(B2:B4,C2:C4)と書く場合は
=SUM(B2:B4*C2:C4)で、[cntl]+[shift]+[enter]です

考え方はsumproductと同じです。

ちなみに、B2:B4*C2:C4を選択し、[F9]を押すと途中経過を見ることができます。



配列の行と列の表し方(excel)

「;」は行の区切り
「,」は列の区切
[1][2][3]
[4][5][6]　は
{1,2,3;4,5,6}　となります



小計を出さずに合計を出すには(excel)




	A	B	C	D>
1		単価	箱数	入り数>
2	B5用紙	1円	3個	1000枚>
3	A4用紙	2円	2個	500枚>
4	B4用紙	3円	1個	100枚>





sumproduct(B2:B4,C2:C4,D2:D4)
または、配列数式を使う(=sum(B2:B4*C2:C4*D2:D4)を入力後[cntl]+[shift]+[enter])
{=sum(B2:B4*C2:C4*D2:D4)}




SUMIFではできない、条件を2つ指定して合計するには(excel)




	A	B	C	D
1	名前	種類	注文数	単価
2	佐藤	さけ	3個	130円
3	佐藤	明太子	0個	120円
4	佐藤	梅	2個	110円
5	佐藤	たらこ	0個	120円
6	佐藤	おかか	1個	100円
7	鈴木	さけ	0個	130円
8	鈴木	明太子	2個	120円
9	鈴木	梅	0個	110円
10	鈴木	たらこ	3個	120円
11	鈴木	おかか	0個	100円
12	田中	さけ	0個	130円
13	田中	明太子	0個	120円
14	田中	梅	2個	110円
15	田中	たらこ	0個	120円
16	田中	おかか	1個	100円




佐藤さんと鈴木さんの注文数の合計は配列数式で
{=SUM(IF((A2:A16="佐藤")+(A2:A16="鈴木"),C2:C16))}

=SUM～と入力後[cntl]+[shift]+[enter]を押すと{}が先頭と末尾につきます

配列数式の条件の「+」は「or」の意味です


佐藤さんと鈴木さんの注文合計金額は配列数式で
{=SUM(IF((A2:A16="佐藤")+(A2:A16="鈴木"),C2:C16*D2:D16))}

また、佐藤さんが頼んだおにぎりの種類は(佐藤　かつ　注文数がゼロでないもの)
{=SUM((A2:A16="佐藤")*(C2:C16>0))}
または、{=SUM(IF(A2:A16="佐藤",IF(C2:C16>0,1,0),0))}
または、{=COUNT(IF((A2:A16="佐藤")*(C2:C16>0),D2:D16))}

・download

行の挿入、削除を気にしない通し番号の付け方(excel)

=ROW()を入れたら、他の行にコピーして貼付ける

タイトルが一行目にある場合は
=ROW()+1

列の場合は
=COLUMN()



POSコードのチェックデジットを求める(excel)


モジュラス１０／３ウェイトでチェックデジットを計算します。
奇数桁を合計し、偶数桁の合計に3を掛けたものと足し合わせ
その数の下1桁の数を10から引いたものが最終桁のチェックデジットとなります
式が入力されていますので、行をコピーして使用してください
・download

関数を文字で表す(excel)


サインカーブをexcelで文字を使い表現します
REPT関数を使い「*」で示す例です。
・download

重複ありの項目の種類を求めるには(excel)




	A	B	C
1	パン	1個
2	パン	3個
3	牛乳	2個
4	ケーキ	1個
5	シュークリーム	4個
6	牛乳	2個
7	パン	3個
8	牛乳	3個



=SUM(IF(FREQUENCY(MATCH(A1:A8,A1:A8,0),MATCH(A1:A8,A1:A8,0))>0,1))

または配列数式でおこないます。
=SUM～と入力後[cntl]+[shift]+[enter]を押すと{}が先頭と末尾につきます
{=SUM((MATCH($A$1:$A$8,$A$1:$A$8,0)=ROW($A$1:$A$8))*1}
{=SUM(({{1;1;3;4;5;3;1;3})={1;2;3;4;5;6;7;8})*1}　　←[f9]でこのように変わります
ここで、配列の1つめから比較して
1=1で真なので「1」を足す
1<>2で偽なので「0」を足す
3=3で真なので「1」を足す
4=4で真なので「1」を足す
5=5で真なので「1」を足す
3<>6で偽なので「0」を足す
1<>7で偽なので「0」を足す
3<>=8で偽なので「0」を足す
よって、合計は4種類になる。



excelの集計機能を使わずに集計するには(excel)




	A	B
1	パン	1個
2	パン	3個
3	パン	3個
4	牛乳	2個
5	牛乳	2個
6	牛乳	3個
7	ケーキ	1個
8	シュークリーム	4個




1.まずキー(A列)でソートします。
2.作業用シートを用意して、その項目が何番目に出てきたかを求めます。
　2-1.元になるシート(シート名はデータ)から、キーの項目を作業シートのC列にくるようにします。
　　=データ!A1
　　以下コピー
　2-2.A列で上と違う場合は「1」,同じ場合は「0」になるようにします。(但し、１行目は「1」)
　　=IF($C1<>$C2,1,0)
　　以下コピー
　2-3.B列で(2-1)のデータを足していきます。(但し、１行目は「1」)
　　=$B1+$A2
　　以下コピー

細かい処理はexcelを見てください。





	A	B	C
1	1	1	パン
2	0	1	パン
3	0	1	パン
4	1	2	牛乳
5	0	2	牛乳
6	0	2	牛乳
7	1	3	ケーキ
8	1	4	シュークリーム


3.集計シートを用意して、一意（ユニーク）の項目になるようにします。
　作業シート名をWorkとします。
　=VLOOKUP(ROW(),Work!$B$1:$C$8,2,FALSE)
　以下コピー
4.SUMIF関数で集計します。
　=SUMIF(データ!$A$1:$B$8,$A1,データ!$B$1:$B$8))
　以下コピー

細かい処理はexcelを見てください。



	A	B
1	パン	7個
2	牛乳	7個
3	ケーキ	1個
4	シュークリーム	4個




注)データ件数が多いと、処理に時間がかかります。
・download


オートコンプリート機能(excel)


[alt]+[↓]
同じ列に入っている値が一覧で表示され、選択したものを入力できる


CSVの最終行に合計を入れるには(excel)


"=SUM(INDIRECT(ADDRESS(1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))"
これ(Xとします)をCSVの最終行にカンマで区切っていれてください。
X,X,X.X
といった感じです

1行目ににタイトルがある場合は
"=SUM(INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))"


分類ごとにシートを分けてクロス集計する(excel)

	A	B	C	D
1	年月	区分	形態	数量
2	1月	新譜	DVD	2
3	1月	旧譜	CD	1
4	1月	旧譜	CD	1
5	2月	新譜	DVD	1
6	2月	旧譜	CD	3
7	2月	旧譜	DVD	3
8	3月	新譜	DVD	1
9	3月	新譜	CD	2
10	3月	旧譜	CD	2


sumproduct関数を使用します
元データがdataという名前のシートとします。
下記のようなシートを作成します。



	A	B	C
1		新譜	旧譜
2	1月	2	2
3	2月	1	6
4	3月	3	2




B2のセルに下記の式をいれます。
=SUMPRODUCT((data!$A$2:$A$10=$A2)*(data!$B$2:$B$10=B$1),data!$D$2:$D$10)
そして、B2からC4までコピーします。

SUMPRODUCTは指定した配列の乗算の和を求めまるものですが、ある形態で集計するときは
DVDを集計したい場合は替わりにB2からC4までに下記のものをいれます。 =SUMPRODUCT((data!$C$2:$C$10="DVD")*(data!$A$2:$A$10=$A2)*(data!$B$2:$B$10=B$1),data!$D$2:$D$10)
CDを集計したい場合 =SUMPRODUCT((data!$C$2:$C$10="CD")*(data!$A$2:$A$10=$A2)*(data!$B$2:$B$10=B$1),data!$D$2:$D$10)
とC列の条件をいれ掛け合わせて集計します。
・download

前ゼロをつける方法(excel)


1.書式設定で00000とします
2.文字列で入っていて直らないときは、そこのセルを数値に変え、空白のセルをコピーし、そこに形式を選択して値を加算し貼付けます。
3.新規のシートにデータをコピー(A1とする)したあと B列に「=TEXT($A1,"0000")」とする。B列をコピーし値を元の場所に貼り付けます


期間を求める方法(excel)


DATEDIF(開始日、終了日、フォーマット)関数を使います

フォーマットの形式はつぎの通りです

[Y]期間の満年数
[M]期間の満月数
[D]期間の日数
[YM]1年に満たない月数
[YD]1年に満たない日数
[MD]一ヶ月に満たない日数





生年月日	今日	年齢
2000/12/31	=TODAY()	=DATEDIF(A2,B2,"Y")

日付が４年ズレる(excel)


MAC版は1904/01/01、Windows版は1900/01/01を基準にしているために起こります
オプションで、[1904 年から計算する] チェック ボックスをオン/オフして切り替えます。


セルに固定長のデータを作るには(excel)

excelのテキスト出力では、固定長のデータを作成するのは大変です。
A列に固定長のデータを作ってしまう方法です。

len関数とrept関数を使います。
規格5桁、番号6桁、数字前ゼロ5桁固定長データを作成します。





規格番号数量	規格	番号	数量
abc 123 00001	abc	123	1
ABC 123 00001	ａｂｃ	１２３	１




セルのA2には下記式が入っています。 =B1&REPT(" ",5-LEN(B1))&C1&REPT(" ",6-LEN(C1))&TEXT(D1,"00000")

セルのA3には下記式が入っていますが、全角を半角にし、規格部を大文字にしています。
全角を半角にするのにはasc関数を使用しています。 =ASC(UPPER(B2))&REPT(" ",5-LEN(B2))&ASC(C2)&REPT(" ",6-LEN(C2))&TEXT(ASC(D2),"00000")


数値の書式について(excel)

数値を前にゼロをつけたり、前にゼロをつけず右詰にする例です
"123.45"をいろいろな形で表現します



数値	書式
|123.40|	"###.00"
|123.4|	"###.##"
|0123.40|	"0000.00"
| 123.40|	"????.00"
| 123.4 |	"????.??"



B1のセルに下記式を入れると
=text(a1,"????.00") で「 123.45」と右詰で表現することができます


Split関数のようにハイフンで左右に分けるには(excel)

excel(vba)のsplit関数のように、区切る方法です。
メニューから区切り文字で分けてしまうのも手ですが、もとデータを残して表示させたいときに使うことができます。

mid関数とfind関数とlen関数を使います。



品番	規格	番号
abcd-12345	abcd	12345




セルのB2には下記式が入っています。
=MID(A2,1,FIND("-",A2,1)-1)

セルのC2には下記式が入っています。
=MID(A2,FIND("-",A2,1)+1,LEN(A2)-LEN(B2)-1)
または　=MID($A2,FIND("-",$A2,1)+1,LEN(A2)-FIND("-",$A2,1))
または　=MID($A2,FIND("-",$A2,1)+1,LEN(A2))


文字列から数値、数値から文字列変換(excel)

違うセルに形式を変えて表示します

value関数とtext関数を使います。


文字列→数値 A1=003(文字) としB1に式[=VALUE(A1)]をいれると[3]と表示
数値→文字列 A2=3(数値) としB2に式[=TEXT(A2,"000")]をいれると[003]と表示


セル内の改行を取る、変更する(excel)

SUBSTITUTE関数かCLEAN関数を使います。



a b c


=SUBSTITUTE(B1,CHAR(10),"/")
　→a/b/c

=CLEAN(B2)
　→abc



・download

作成したマクロを他のBookでも使用したい場合(excel)

1.personal.xlsに保存する
　ツール→マクロ→新しいマクロの記録でマクロの保存先を「作業中のブック」ではなく
「個人用マクロブック」に保存します。XLStartのフォルダに保存されます
※　以前流行ったウィルス「Laroux」でこのファイル操作し、ウイルスに感染させていました。

2.アドインを作成する
　これを参照してください[eightban excel addin test]

3.vbスクリプトを使用する
　これ等を参考にしてください[excel ファイルを読みCSVにする(vbs)]

4.マクロを別のBookにコピーする
　これが一番単純かもしれません



図形をセルに揃えるには(excel)

図形を選択し、ALTキーを押しながらマウスで大きさを調整すれば揃います



ドロップダウンリストを作成するには(excel)

メニューの【データ】→【入力規則】から【データの入力規則】の設定タブ
【入力値の種類】で【リスト】を選択し、【元の値】に「リスト1,リスト2,リスト3」などと入力します。
【元の値】の項目の右にあるボタンを押し、リストのあるセルの範囲を指定することもできます。
→図形を選択し、ALTキーを押しながらマウスで大きさを調整すれば揃います



複数のシートを同時に同様に変更するには(excel)

シフトキーを押しながら左クリックすると、シート名の下に点線の下線がつくので この状態になったら同時に同様に変更ができます。


キーが変わったら改ページするには(excel)

マクロを使用し、A列のキーが変わったら改ページ文字を入れます。
Sub go()
　oldkey = ActiveSheet.Cells(1, 1).Text
　
　' 最終行まで　ループ
　For ii = 1 To ActiveSheet.UsedRange.Rows.Count
　'キーが変わったか？
　　If oldkey <> ActiveSheet.Cells(ii, 1).Text Then
　　'改ページを入れる
　　　Cells(ii, 1).Select
　　　ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
　　'キーの入れ替え
　　　oldkey = ActiveSheet.Cells(ii, 1).Text
　　End If
　Next
End Sub

・download


セルの情報を取得する関数(excel)

自作関数を作り、色、背景、罫線などの情報を返す関数を作ります。
背景色で分けて報告したexcelを見かけますが、色ごとに抽出、並び替えできます
【背景色】
Function haikei(objCell As Range) As Integer
'Ctrl+Alt+F9キーで再計算しますが、次のコードでも再計算します。
' Application.Volatile
haikei = objCell.Interior.ColorIndex
End Function

【フォント色】
Function iro(objCell As Range) As String
iro = objCell.Font.ColorIndex
End Function

【罫線下線】
Function kasen(objCell As Range) As String
kasen = objCell.Borders(xlEdgeBottom).LineStyle
End Function
・下線実線xlContinuousは「1」
・下線なしxlLineStyleNoneは「-4142」

自作関数は前述



セル内の改行を消すには(excel)


[alt]+[↓]
セルの中で改行をするにはCTRL+改行ですが、その改行文字(見えませんが)を消す方法です
置換のウィンドウで検索する文字列の所にCTRL+Jを入れます(見えませんが)。
置換後の文字列には何もいれません。これで置換を行うとセル内の改行が削除されます。


自分のセル番地、隣のセル番地(excel)


現在の行はROW()、カラムはCOLUMN()で求められるので、
ADDRESS(ROW(),COLUMN())
です。
番地の中身を取り出すにはINDIRECT関数を使います。
例えば左となりのセルは
INDIRECT(ADDRESS(ROW(),COLUMN()-1))
OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -1)
のどちらを使用することができます。

結合したセルに隣のセルの合計を入れる(excel)




得点	合計
4点	9点
5点

合計がはいるセルの行数を求める関数を作ります。
Function MergeRow(objCell As Range) As Integer
MergeRow = objCell.MergeArea.Rows.Count
End Function

参考に結合セルの列を求める関数
Function MergeCol(objCell As Range) As Integer
MergeCol = objCell.MergeArea.Columns.Count
End Function

参考に結合セルの範囲を求める関数
Function MergeRange(objCell As Range) As Variant
MergeRange = objCell.MergeArea.Address
End Function

結合セルでは自分の一番左の列、一番上の行が番地となります。

SUM(自分の隣の上部のセル番地:自分の隣の下部のセル番地)
SUM(INDIRECT(ADDRESS(ROW(),COLUMN()-1)):INDIRECT(ADDRESS(ROW()+自分の 行数,COLUMN()-1)))
SUM(INDIRECT(ADDRESS(ROW(),COLUMN()-1)):INDIRECT(ADDRESS(ROW()+MergeRow(INDIRECT(ADDRESS(ROW(),COLUMN())))-1,COLUMN()-1)))
といった感じです。

・download

数字以外と数字を分けるには(excel)

A1のセルにABCDE12と入っているとします。
これを「ABCDE」と「12」に分ける方法です。
※初めて見つかった数字の部分で分けます

B1のセルに「=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},ASC(A1)& 1234567890))-1)」
C1のセルに「=RIGHT(A1,LEN(A1)-LEN(B1))」
といれます。
B1のセルの「&12345678990」は数字が入っていないセルの対応です


都道府県でセルを分けるには(excel)

住所の入ったデータを都道府県とそれ以外に分ける方法です。
都道府県だけでなく、○○○屋　△△△店といったデータも分けることができます

A1に「神奈川県横浜市」と入ったデータがあり、
B1に「=MATCH(1,FIND({"神奈川県","東京都","埼玉県"},$A1,1))」と入れます。
B1は配列の何番目を返しますのでエラーを考慮し下記のように
D1に「=IF(ISNA(B1),"",INDEX(SUBSTITUTE($A1,{"神奈川県","東京都","埼玉 県"},),B1))」
入れます。
C1には「=IF(ISNA(C1),A1,SUBSTITUTE($A1,E1,))」と都道府県が入ります。
解り易いように都道府県を3つしか入れてありませんので、都道府県以外の用途があれば使用してください。
都道府県で分ける方法ですがexcelシート上のデータを配列として使う方法を載せてあります。

・download


時間を30分ごとに切り上げ、切り下げるには(excel)

9:25を9:30にしたい場合に
=CEILING(9:25,"0:30")
とします。切り捨てるには
=FLOOR(9:25,"0:30")
とします。


「^」キャレット・ハットの意味は？ (excel)

べき算をあらわす演算子です


最後の行数を求める (excel)

A列にデータがあるとします。

(文字列)
=MATCH("ー",A:A,1)　照合の型を省略可能
=MATCH("*",A:A,-1)
=MATCH("",A:A,-1)

(数値)
=MATCH(2^1023,A:A)

おおきな値をいれておきます。
ちなみにexcelで使用できる最大値は
1.79769313486232E+308で、べき乗で表すと「=2^1023.99999999999」が近い値のようです

(数値と文字列の混合)
=MAX(MATCH("ー",A:A),MATCH(2^1023,A:A)) 数値と文字列がそれぞれ必ず両方入っている場合

=MATCH(1,INDEX(1/(A1:A32767<>""),0))　

これは四則演算すると「TRUE=1」,「FALSE=0」扱いになり
A1からA32767まで空白かどうかの判断をします。
データがないところは[1/FALSE→ #DIV/0]、あるところは[1/TRUE→ 1]
で「1」以下最大の値で検索。今回は下から調べていき最初に「1」が出た位置を返します

例えばA32767の代わりにA10と小さくて「INDEX(1/(A1:A10<>""),0)」を反転した後[F9]を押し てみてください
{1;1;1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}のようになってるのが分かります
※INDEX関数で行番号または列番号を 0 に設定すると、列全体または行全体の値の配列がそれぞれ返されます。
=MATCH(検索値,範囲 [,照合の型])
照合の型[0]-完全一致,[1]-一致しない場合は最大値,[-1]-一致しない場合は最小値。デフォルトは[1]


四角の中にチェックマークをいれるには (excelなど)

フォントをWindingsにした後
[alt]+0254と入力すると四角の中にチェックマークが入った文字が表示されます
[alt]+0111は□
[alt]+0252はチェックマークのみ


電話番号のハイフン「-」をとるには (excel)

substitute(セル,"-","")


今月の第1土曜日を求めるには (excel)

今月の月初と曜日を求めてから計算します
前月末の日付は当月の何日目かを調べるDAY関数を使い今日の日付から今月の経過した日数を引きます
=TODAY()-DAY(TODAY())
例えば今日が2011/11/5であるならば、2011/11/5から5日を引くことで前月末となります。 月初はそれに1を加えた数で=WEEKDAY(TODAY()-DAY(TODAY()))+1 月初の曜日は =WEEKDAY(TODAY()-DAY(TODAY())+1)
で曜日はWEEKDAY関数で下記の数字で表されます。

曜日　数値
日　1
月　2
火　3
水　4
木　5
金　6
土　7

土曜日まで何日足せばよいかというと 7-WEEKDAY(TODAY()-DAY(TODAY())+1)

で、月初からこの数字を増やした日付が第1土曜日となります。
=TODAY()-DAY(TODAY())+1+7-WEEKDAY(TODAY()-DAY(TODAY())+1)
第3土曜日はこれから2週分先なので7を2倍して =TODAY()-DAY(TODAY())+1+7-WEEKDAY(TODAY()-DAY(TODAY())+1)+7*2

第1木曜日は、木曜日まで何日足せばよいかというと
5-WEEKDAY(TODAY()-DAY(TODAY())+1)
月末が金、土曜日の場合は都合が悪いので
5+7-WEEKDAY(TODAY()-DAY(TODAY())+1)
場合分けがあるので、次のように対応
=MOD(5-WEEKDAY(TODAY()-DAY(TODAY())+1),7)

=TODAY()-DAY(TODAY())+1+MOD(5-WEEKDAY(TODAY()-DAY(TODAY())+1),7)

第3木曜日はこれから2週分先なので7を2倍して =TODAY()-DAY(TODAY())+1+MOD(5-WEEKDAY(TODAY()-DAY(TODAY())+1),7)+7*2
と応用していきます
一般的な形にすると
=TODAY()-DAY(TODAY())+1+MOD(w-WEEKDAY(TODAY()-DAY(TODAY())+1),7)+7*(n-1)
w=曜日 (日1,月2,火3,水4,木5,金6,土7)
n=第n週
ほかの月をやりたい場合は、TODAY()の部分を適当な月の適当な日にちにしてやればできます。
例えば、セルc3に2011/1/1が入っているとすると
=c3-DAY(c3)+1+MOD(w-WEEKDAY(c3-DAY(c3)+1),7)+7*(n-1)
また、c3のセルから2月を求めたい場合は
=DATE(YEAR($C$3),MONTH($C$3)+1,1)


違うシートの一部分を表示するには (excel)

indirect関数を使います。
第2パラメータにfalseを入れるとR1C1形式になります。
aというシートを作り、そのシートを列で指定して表示させます。
同じセル番地を出すには
=INDIRECT("a!"&"R"&ROW()&"C"&COLUMN(),FALSE))
でよいですが、必要な部分のみ違うシートに出力するために
aシートの$a$1のシートに出したい列の開始列番号を入れます。
=INDIRECT("a!"&"R"&ROW()&"C"&COLUMN()+a!$A$1-1,FALSE)
ただし、エラーにならないようにするためif文を入れて回避します。
=IF(INDIRECT("a!"&"R"&ROW()&"C"&COLUMN()+a!$A$1-1,FALSE)=0,"",INDIRECT("a!"&"R"&ROW()&"C"&COLUMN()+a!$A$1-1,FALSE))

・download

条件付書式で矢印キーを使って編集するには (excel)

条件付書式の編集画面でそのままで矢印キーを使うとセルの選択になります。[F2]キーを押すと回避できます


交代勤務表 (excel)

3人ローテーションの交代勤務表です。
まず土日を調べ、行の値がが1、2、3となるようにします。
交代する順番ができたら、INDEX関数を用い、担当者を配列より表示します。
土日、会社の休業日は「1」にし、他は空白にします。3つの条件のORです。
会社の休業日はB列に何か入れることにします。
=IF(OR(WEEKDAY(C4)=1,WEEKDAY(C4)=7,B4<>""),1,"")

会社が休みでないとき(E列が空白のとき)、1行下にいくと数字が1増えるようにします。
=IF(E5="",MOD(F4-3,3)+1,MOD(F4-3,3))

会社が休みのときは「-」を表示し、営業日は担当者をいれます。
ここでINDEX関数を使います。担当者は配列で定義するため、{}で囲みます
=IF($E5<>"","-",INDEX({"鈴木","佐藤","田中","鈴木","佐藤"},1,$F5))

・download

一列のデータを4列(行)ずつ表示するには (excel)

1
2
3
4
5
6
↓
1　2　3　4
5　6

indirect関数を使います。
横
=INDIRECT("sheet1!$A"&(MOD(COLUMN()-1,4)+1)+(ROW()-1)*4)
縦

=INDIRECT("sheet1!$A"&(MOD(ROW()-1,4)+1)+(COLUMN()-1)*4)

数字の4を変えると、別の刻みでできます。
・download

列の右側のデータでvlookupのようにするには (excel)

1　one
2　two
3　three
4　four
5　five
のテーブルがあったとき
↓
two　2
five　5
のように右側にあるデータではvlookupできません。
この場合はindex関数とmatch関数を使います。
sheet2にテーブルがある場合、下記のようにします
=INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0),1)

これを使うと、離れたところにある場合も余計の部分を参照する必要がなくなり
vlookup関数より処理スピードが速くなります。

・download

配列を使ったNOから名称の取得(excel)

例えば、曜日を自分が好きな表示したい場合の例です
A1のセルから下記のようにデータが入っているとき、b1のセルは[=weekday(a1)],c1のセルは=a1とし、書式を「aaa」

　 [2016/10/1][7][土][ど]
　 [2016/10/2][1][日][にち]
　 [2016/10/3][2][月][げつ]
　 [2016/10/4][3][火][か]
　 [2016/10/5][4][水][すい]
　 [2016/10/6][5][木][もく]
　 [2016/10/7][6][金][きん]
d1のセルに[=INDEX({"にち","げつ","か","すい","もく","きん","ど"},B1)]
といれた後、下のセルにコピーします。


文字項目、数字項目に設定したセルが反映されないとき(excel)

書式設定で文字項目、数字項目しても反映されずVLOOKUPで#N/Aになるときがあります。

　 そのときはセルが変更したことをexcelに伝えればよいので、次のどれかをやるとよいです。
セルの書式を変更した後
　 1.空白セルをコピーして、形式を指定して「値」の貼り付け、演算「加算」で貼り付け
　 2.メモ帳などに一旦コピーしてから、貼り付け


また、ホームページなどをコピーしたときなどは、書式が付いてくるのでこの場合も一旦メモ帳などにコピーしてから貼り付けします。


xlsxの作成日時、前回保存者を変更するには (excelなど)

xlsx,docx,pptxなどはzip形式となっていますので拡張子をZIPに変更後、解凍します。

[_rels][docProps][xl]のフォルダおよび[Content_Types].xmlがありますが
[docProps]の中のcore.xmlをエディタで修正します。

creatorとlastModifiedByにある作成者、修正者を変更し
時刻は「2016-12-12T13:34:18Z」のように入っていますが、日本時間の9時間前をグリニッジ標準時を指定します

修正後はZIP圧縮して、拡張子をもとに戻すとできあがります


excelでワイルドカード「*」「?」を検索するには (excel)

通常は「*」「?」はワイルドカードとして検索はできませんが、先頭にチルダ「~」を入れることで検索ができます

[~*][~?]


文字列を数字の部分で分けるには (excel)

「A1」セルに「ABCDE12345」が入っているとします。
「B1」セルに LEFT(E18,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(E18)&"0123456789"))-1)
[C1]セルに SUBSTITUTE(A1,B1,"")
または[C1]セルに =RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&"0123456789"))+1)
を数式を入れると「ABCDE」「12345」と分けることができます。
文字列に"0123456789"を結合しているのは、FIND関数がエラーにならないようにするためです。


データをn個ずつ表示するには (excel)

　　　　　　　[a]
　　　　　　　[b]
　　　　　　　[c]
のようなデータがあり、違う場所に
　　　　　　　[a]
　　　　　　　[a]
　　　　　　　[a]
　　　　　　　[b]
　　　　　　　[b]
　　　　　　　[b]
　　　　　　　[c]
　　　　　　　[c]
　　　　　　　[c]
と同じ値を繰り返し表示したい場合です。
元のデータが(A列)にあるとすると
B列に入れたい場合、セル(B1)に =INDEX(A:A,INT((ROW(A1)-1)/3)+1)を入れて必要な分だけ下にコピーします。
n個繰り返したい場合は、 =INDEX(A:A,INT((ROW(A1)-1)/n)+1)となります。

Information

2.Ms Access,Excel

Access,Excelのサンプルです。