haku1569　Excel でらくらく データ分析！

Excelでは選択範囲に名前（範囲名）をつけて、それを数式や関数等で使用することが出来ますが、列（だけではないですけど）のデータに簡単に範囲名を付ける方法があるので、やってみます。


さて、こんな試験の結果を集計した表があります。


この表の範囲を選択します。


そこで、「数式」から「選択範囲から作成」を選択します。


「選択範囲から名前を作成」の画面が開きます。
「上端行」にチェックをいれてOKします。
「上端行」と言うのは、一番上の行つまりこの表では“氏名”、“国語”、“数学”の列の見出しになりますので、これにチェックすると言うことは、それぞれの列に範囲名が設定されることになります。
「左端列」にすると各行に氏名が範囲名に設定されます。


すると、この様に“氏名”、“国語”、“数学”の範囲名が出来ているのがわかります。


国語の範囲


数学の範囲


では、この範囲名を活用してみましょう
国語と数学の平均点をAVERAGE関数で求めてみましょう。


“=AVERAGE(国語)”と入力します。
通常は平均値を求める範囲（C3:C52）を指定しますが、代わりに範囲名で設定できます。


と、国語の平均点が“63点”と表示されます。
同様に数学の平均を求めます。


今度は、“数学”を手入力しないで登録してある範囲名から選択してみます。
「数式」の「数式で使用」から「数学」を選択します。


と、数式に“数学”が入力されました。


で、国語と数学の平均点が範囲名で計算できました。


で、この平均点が計算されているセルを右下の方へコピーペーストしてみます。
普通にセルの範囲指定で計算している場合は、セルの範囲を絶対範囲に指定していないと、移動した分だけ参照範囲も移動してしまい正しい結果が表示されなくなってしまいますが、範囲指定ならそんな心配も不要です。



関連ページ

Excelで金額を入力した時に単位を千円にして表示桁を3桁減らしてみます。セルの書式設定からユーザ定義で設定します。

せるに“5300”、“1243000”が入力されています。


書式を設定する範囲を選択して右クリし、「セルの書式設定」を選択します。
「セルの書式設定」画面から「表示形式」で「ユーザー定義」を選択します。


種類に
#,###.###,"千円"
と入力して書式を設定します。
ちなみにこの記号意味はコチラをご覧ください


とこのような表示になりました。


ちなみに、最後の“，”を2つにすると1,000,000で割った値になります。

値を一旦1000で割ったりしなくてもこの方法なら書式で表示を変えることができますね！




関連ページ
ユーザー定義の書式に使う記号の意味

Excelでセルの書式設定では「ユーザー定義」で書式をカスタマイズすることが出来ますが、そこで使われている様々な記号の使い方をまとめてみました！

書式を設定したいセルまたは範囲を選択して右クリして「セルの書式設定」を選択します。


「セルの書式設定」画面の分類で「ユーザー定義」を選択すると色々な記号の組合せが選択できます。
その中から選択するか、「種類」に直接記号を手入力することで書式をカスタマイズすることができます。


数値に関する書式記号
まずは数値の書式記号です。
#：1桁の数字を表示します。“.”（小数点）を付けることで、整数部と小数部に分けることができます。いずれも指定された桁で四捨五入されます。整数部は# の数に関係なく入力値の整数桁が表示されます。
小数部は、#の数だけ桁数が指定され、その有効桁数しか表示されません。但し、入力値よりも多い小数部の桁数を指定しても入力値の桁数だけの表示にります。（余分な0表示はしません）

0：１桁の数字を表示します。“.”（小数点）を付けることで、整数部と小数部に分けることができます。いずれも指定された桁で四捨五入されます。整数部は0 の数が入力値の整数部桁より多い場合は頭に“0”が付加されます。
小数部は、0の数だけ桁数が指定され、その有効桁数しか表示されません。但し、入力値よりも多い桁数の場合指定した桁だけ常にゼロが表示されます。

?：小数部の桁数を?の数に揃えます。入力値よりも?の数が多い場合最後にスペースが付加されて小数点の位置が揃います。


数値の桁区切りと3桁丸め
桁区切りと言うのは3桁毎に“，”を入れること、3桁丸めって言うのは、1,000や1,000,000で割って桁数を減らすことです。
桁区切りには“，”を入れます。3桁丸めには最後に“，”を入れます。“，”は1,000で割り、“，，”は1,000,000で割ります。
桁区切りと3桁丸めの記号が一緒ですが入れる場所で区別されます。


年月日に関する記号
y：西暦による年数（数で桁数を指定）
e：和暦による年数（数で桁数を指定）
g：和暦の元号のアルファベット頭文字
gg：和暦の元号の漢字頭文字
ggg：和暦の元号の漢字
m：月（mmは1桁月に0を付けて2ケタ表示）
mmm：英語月（アルファベット）頭文字3文字
mmmm：英語月（アルファベット）
mmmmm：英語月（アルファベット）頭文字
d：日にち（ddは1桁日に0を付けて2ケタ表示）
ddd：英曜日（アルファベット）頭文字3文字
dddd：英曜日（アルファベット）
aaa：曜日（漢字）頭文字
aaaa：曜日（漢字）

エクセルの表示例のセルの入力値の表示はあえてセルの書式を「標準」に直してシリアル値で表示させています。実際はセルには「1/3」と入力しています


時分秒に関する記号
h：時刻（24時間表示、hhは1桁時刻に0を付けて2ケタ表示）
m：分（mmは1桁分に0を付けて2ケタ表示）
注）hの直後、sの直前でないと分と認識されません。それ以外は月と認識されます。
s：秒（ssは1桁秒に0を付けて2ケタ表示）
AM/PM：12時間表示にしてAMまたはPMを表示します。
A/P：12時間表示にしてAまたはPを表示します。
[h]:24時間を超えた分（日に繰上がった分）も含めて時間の合計を表示します。
[m]：60分を超えた分（時間に繰上がった分）も含めて分の合計を表示します
[s]：60秒を超えた分（分に繰上がった分）も含めて秒の合計を表示します

こちらも、エクセルの表示例のセルの入力値の表示はあえてセルの書式を「標準」に直してシリアル値で表示させています。実際はセルには「1900/1/1 9:30:25」と入力しています。


その他の書式
[青]：文字の色を青にします。指定色は他に[黒]、[水]、[緑]、[紫]、[赤]、[白]、[黄]
[<10]：数値が10未満の場（条件付き書式）他に指定できる条件は[>]、[=]、[>=]、[<=]、[<>]
"文字列"@：文字列が入力されているセルに対し、指定した文字列に入力された文字列を@で併せて表示させる。
;：書式の組合せ。書式を";"で繋げることで、左から「正の数の書式」、「負の数の書式」、「ゼロの書式」、「文字列の書式」の4つの書式まで繋げることが出来ます。
_特定文字列：指定した文字列の代わりにその分の間隔を開けて位置を揃えます。（特定文字列がある場合だけ間隔を開けません）
*記号：セル幅に表示できるまで指定した記号を表示します。



関連ページ

ラベル：セルの書式

Excelのセルの書式設定で「選択範囲内で中央に配置」させると、「セルを結合して中央揃え」と同じ体裁にすることができます。

なんのことかというと。。。

例えば、こんな表があります。


��3のデータは商品1〜3が同じ"D"なので、「セルを結合して中央揃え」で表示させます。


3つのセルに"D"が入力されているので、結合させようとすると左端のデータだけが保持される、つまり右側にある2つの"D3"、"E3"のセルのデータは削除されるというメッセージが表示されます。


OKするとこの様な表になります。
ありがちな表ですよね。


例えばここで、「商品1」でソート（並べ替え）してみます。


すると、「この操作には、同じサイズの結合セルが必要です」とメッセージが表示されます。


「ヘルプの表示」をクリックすると、
セルが結合されている行があるので、並べ替えが出来ないということです。
並べ替えをする場合は、セルの結合を解除する必要があります。


ではこれは？
さっきの表と一緒？


「商品1」でソート（昇順）するとちゃんと出来ました。


「商品3」でソート（降順）するとソートは出来ましたが、��3の行が一番下になっています。


カーソルをセル「C5」に置いてみると、セルは結合されていなく、"D"が入力されています。


今度は、カーソルをセル「D5」に置いてみると、セルは結合されていなく、空白になっています。


これは、セル「C5」だけに"D"が入力されている状態で、セル「C5」から「E5」を選択し、セルの書式設定で横位置の配置を「選択範囲内で中央」に設定しているからです。


この設定をしておくと、セルは結合されていませんが、体裁は結合されているのと同じようになり、罫線も表示されません。
但し、セル「D5」、「E5」は空白セルにしなければならないので、本来のデータとは異なった内容になってしまいますから要注意です。



span style="color:#0000FF;">関連ページ

Excelでオートフィルを使っても連続データが入力出来ない時があります。
と、その前にオートフィルについて簡単にまとめてみました。

フィルハンドルとは？
値が入力されているセルを選択するとセルの右下の角に■が表示されます。
これを「フィルハンドル」と言い、これをドラッグすることでセルの値をドラッグ＆ドロップで、ドラッグした範囲にコピーすることが出来ます。


フィルハンドルにカーソルを合わせると、カーソルが“＋”に変わります。


んで、ドラッグ＆ドロップすると値がコピーされます。値によっては連続した値がコピーされます。
一番最後のセルの右下には“オートフィルオプション”が表示されます。


オートフィルオプションを開くと、コピーの方法を選択できます。


で、このフィルハンドルやオートフィルオプションは非表示にすることが出来ます。
もし何かの拍子で消えてしまった時は、再表示させることが出来ます。

フィルハンドルやオートフィルオプションを非表示（表示）する
オフィスボタンを押します。


「詳細設定」で「フィルハンドルおよびセルのドラッグアンドドロップを使用する」のチェックを外します。


すると、フィルハンドルが表示されなくなり、ドラッグ＆ドロップでコピー出来なくなりました。


今度は、フィルハンドルは表示させておいて「[貼り付けオプション]ボタンを表示する」のチェックを外すと。。


オートフィルオプションが表示されなくなりました。


さて本題の、
オートフィルで連続データが入力できない原因。。。は？
連続数をコピーする為に予め“1”、“2”を入力しておきます。
この2つのセルを選択してフィルハンドルをドラッグ＆ドロップします。


すると、増分値“1”の連続数（1、2、3、4、・・・）がコピーできるはずなのですが？
出来ません！
しかも、オートフィルオプションも表示されていません。（「詳細設定」では表示する設定になっているのに。。。）


と、、、よく見ると行番号が飛んでます。
んで、左の列をみると。。
セルがフィルタで抽出されていました。


つまり、これってフィルタでセルが抽出されている時に、可視化セルの範囲設定をしなくてもオートフィルを使えば、抽出されているセルだけにそのセルの値がコピーされる（連続数にはならない）このと裏返しの動作なのです。。。（分かり辛いか？）



関連ページ
フィルタで抽出したセルに「形式を選択して貼り付け」をしてみると！？
非表示行が範囲に入っていても貼り付けられないようにしてみた（可視化セルの選択）
抽出したセルに連続数を入力してみた（可視化セルにオートフィルをかける）

さて、フィルタを適用させて抽出したセルに「形式を選択して貼り付け」でデータを貼り付けると、抽出されている行の間の抽出されていないセルにも値が貼り付いてしまいましたが、フィルタではなく単に「非表示」にした行に貼り付けをするとどうなるでしょうか？

まず、こんなシートがあります。


ここで、"A"以外の行を非表示にします。
非表示にする行を選択範囲にして、右クリから「非表示」を選択します。


すると、"A"だけの行（レコード）が抽出されました。


そこで、最初の「値」のセルに"10"を入力し、セルの右下角（フィルハンドル）をドラッグして下のセルに貼り付けします。


すると、"A"の行に"10"が貼り付けられました。
ちなみに、フィルハンドルによる貼り付けは、フィルタで抽出された行への貼り付けの場合は、抽出されていない行への貼り付けはされませんでした。


んで、非表示行を再表示します。
非表示にした行の範囲を選択します。


右クリから「再表示」を選択します。


するとなんと！？
非表示にしていた行にも"10"が貼りついてしまっています。


つまり、「非表示」にした場合は、表示されていないだけなので選択範囲に含まれると値は貼り付けられます。

表示されているセルだけにコピー（貼り付け）させるには（可視セルの選択）
では、非表示にした行に貼り付けられないようにするにはどうしたらいいかと言うと、
貼り付ける範囲を「可視セル」として選択すると見えているセルのみが選択範囲になり、非表示の行や、フィルタで抽出されていない行などは強制的に選択範囲から除外することができます。

では、さっそくまた"A"以外の行を非表示にして、8行と12行の"A"の範囲を選択します。


そこで、「ホーム」、「編集」、「検索と選択」を選択します。


「条件を選択してジャンプ」を選択します。


「選択オプション」画面が開きます。
「可視セル」のボタンを選択し、「OK」します。


すると、選択した範囲があたかもCtrlキーを押しながら選択したのと同じように表示されています。


そこで、右クリから「貼り付け」を選択します。


"A"に"10"が貼り付けられて、、


非表示だった行を再表示してみると、、、
そこには"10"は貼り付けられておらず、"A"だけに貼り付けが出来ています。


この、可視セルの選択はショートカットキー「Alt + ;」が用意されています。こっちの方が一発で選択できるので推しです！

バックナンバー
フィルタで抽出したセルに「形式を選択して貼り付け」をしてみると！？

関連ページ
Alt + ; で表示されているセル（可視セル）だけを選択範囲にしてみた
オートフィルで連続データが入力できない！？
抽出したセルに連続数を入力してみた（可視化セルにオートフィルをかける）


今日の物欲
やっぱ、ハイレゾよりアナログでしょ！？

DENON　DP-1300MK2＋ortofon MC-Q10 セット【台数限定 36% off】

Excelのツールバーに"カメラ"コマンドを追加して、図のリンク貼り付けを簡単に行うことが出来ます。

まず、ツールバーにカメラコマンドを追加します。
クイックアクセススールバーのアイコンの右端の▼をクリックして「クイックアクセスツールバーのカスタマイズ」から「その他のコマンド」を選択します。
（Excel2007）


「コマンドの選択」で"リボンにないコマンド"を選択し、「カメラ」を選択したら、「追加」を押します。


すると、ツールバーにカメラのアイコンが現れました。


例えば、コピーしたいセルを範囲選択して、カメラをクリックします。
すると選択範囲が点滅する点線で囲まれます。


んで、貼りつけたいシートに移動するとカーソルが十字に変化します。


そこで、ドラッグして放すと選択した範囲が図として張り付けられます。
但し、数式ウィンドウをみるとリンク元が表示されています。
図として張り付いているので、張り付いたシートのセルの幅などには何ら影響されずに拡大・縮小等ができます。


例えば、貼り付け元のデータを"喫茶たいむましん"から"喫茶タイムマシン"に変更してみます。


すると、貼りつけられた表も更新されています。
これが、"リンク貼り付け"ということです。


応用例
よく、仕事の提出書類等では、作成・審査・承認等の捺印欄を付ける場合が多くありますが、Excelの罫線で作ろうとすると、本文の表のセル幅と捺印欄の幅が合わずにセルを細かく分割したり、統合したり、結構面倒だったりします。
そんな時に、この図の貼り付けを行うと簡単に捺印欄を作る事ができます！

例えば、こんな勤怠管理表（Excelの新規フォームからダウンロードしました）の右上に捺印欄を追加してみましょう。


別のシートに捺印欄を作ります。何も制限されないので簡単に出来ちゃいます。


これをカメラで撮ります。


で、勤怠管理表に貼り付けます。
っと、あっという間に出来ちゃいました！


オーソドックスに図のリンク貼り付けをやってみる。
カメラを使わないでやってみると、、
まず、貼りつける範囲を選択して、右クリからコピーします。


で、勤怠管理表の貼りつけたい位置のセルにカーソルを置いて、「クリップボード」、「貼り付け」、「図」から「図のリンク貼り付け」を選択します。


と、カメラと同じように捺印欄が貼りつきました。


ちなみに、元データの"作成"を"担当"に変更してみると、、、


どちらも更新されました。




関連ページ

Excelのデータの入力規制の設定でセル毎に半角（英数）と全角（ひらがな/漢字）を設定し、いちいち「半角/全角」キーを押して切り替えなくてもIMEを自動で変更することが出来ます。
とっても便利な機能です！


では、さっそくやってみましょう！
こんなテーブルがあります。
"受注コード"や"受注日"は半角英数、"在籍支社"、"部署名"、"氏名"は全角（漢字）で入力されています。


まず、"受注コード"と"受注日"の列を選択します。


「データ」、「データツール」、「データの入力規制」から「データの入力規制」を選択します。


「データの入力規制」画面が開きます。
タブ「日本語入力」を開き、"オフ（英語モード）"を選択し「OK」します。


次は、"在籍支社"、"部署名"、"氏名"の列を選択します。


同様に「データの入力規制」画面で「日本語入力」を"ひらがな"に設定します。


すると、"受注コード"の列の空白セルにカーソルを置くと勝手に"英数半角"IMEが設定されます。


こんどは"在籍支社"の列のセルにカーソルと置くと"全角ひらがな"に変わりました。


これ、是非お勧めです！思った以上に便利で、ストレスなくデータ入力が出来ますよ！



関連ページ

Excelでデータ入力を行う時に、いちいち数字を入力しなくてもチェックボックスにレ点入れるだけで数値入力をさせることが出来ます。

「開発」のメニュー表示
まずメニューの「開発」からチェックボックスを設定していくわけなんだけど、メニューに「開発」がない場合は。。。
「Office」ボタンを押して、、


「Excelのオプション」を押します。


「Excelのオプション画面」から「基本設定」で"[開発]タブをリボンに表示する"にチェックを入れて「OK」します。


すると、メニューの右端に「開発」が追加されました。


チェックボックスの挿入
んで、「開発」、「コントロール」、「挿入」から「フォームコントロール」のチェックボックスを選択します。


すると、マウスカーソルが＋字に変わるので、シートの上でドラッグしてチェックボックスを置く場所と大きさを決めます。
すると、こんなチェックボックスがワークシートに挿入されました。


チェックボックスにカーソルを置くと人差し指の形に変わります。
そこで、押すとレ点が入ります。


チェックボックスのコントロール
さて、チェックボックスが出来たところで次に入力されたレ点の"コントロール（データ化）"を始めましょう。

チェックボックスの上にカーソルを置いて右クリします。
表示されたメニューから一番下の「コントロールの書式設定」を選択します。


「コントロールの書式設定」画面が開きます。
「コントロール」タブを開きます。んで、ちょこっと寄り道して「3-D表示」をチェックしてそれっぽい体裁にします。
"リンクするセル"にレ点の結果を表示させるセルを指定します。
レ点の結果というのは、、
レ点が入ると"TRUE（真）"、入らなかった時は"FALSE（偽）"が表示されます。


すると、このようにレ点で"TRUE"

レ点が入らないと"FALSE"が表示されました。


んで、これを数値化するわけですが、"IF関数"を使ってもいいのですが一番簡単なのは、何か数字を掛けてしまうことです。
"TRUE"は"1"、"FALSE"は"0"として計算されるので例えば1を掛けるセルを設定すると


レ点で"1"が表示され


レ点が入らないと"0"が表示されます。