FC2ブログ

おジさん学習帳

LibreOffice などOffice ソフトなどについてのブログです
おジさん学習帳 TOP  >  エクセル

リスト 二重設定 リストからリストになる

だいぶ前に
入力規則から入力規則 2重設定 
アップしたことがありましたが、
Excel の バージョンも 変わったので
もう一度
きっかけは、今日見つけた Yahoo 知恵袋の質問

エクセル初心者ですので 丁寧に教えて頂ければ嬉しいです。
ワード(単語)によって
ドロップダウンリストの内容を 変えることはできますか?

例えば
A1 を 文房具 としたら
B1 のドロップダウンリストの内容が
 ホチキス、定規、ハサミ
A1 を 食料品 としたら
B1 の ドロップダウンリストに
 おかし、パン、ジュース

まずは、設定したい内容を表にします
 これは、「名前」 を 付けるためにも、必要な作業です
 同じシートでも 別のシートでも構わないので、邪魔にならない箇所に
 以下の表を作成します

  201905160002.png

 縦横は、どちらでもかまいませんが
 最初のセルで、選択する文字列のリストを一番端(この場合は「上」)
 その文字列を選択した場合に、表示したい文字列をその列へ入力する

 次に、「名前」の設定です
 「文房具」を 選択した場合に
 表示したい文字列のセル範囲を選択
 この場合 G2:G4 セル範囲

 201905160003.png

 左上の 「名前ボックス」 に、「文房具」 と 入力し
 [Enter] キーを 2回押します
 ※ 1度目は、文字の確定 

 201905160004.png

 ※ 2度目が、名前の定義の設定
   ▼ が、表示される

 201905160005.png

 確認したい場合は、
 [ 数式 ] タブ 「定義された名前」グループの「名前の管理」をクリック

 201905160006.png

 すると
 [ 名前の管理 ] ダイアログが表示される
 「名前」の列に、「文房具」
 「参照範囲」には、シート名からセル範囲(絶対参照)が表示されている
 
 201905160007.png

同様に、「食料品」も 「名前」の設定をする

準備は、終了

 リストの設定
 
 まずは、
 設定するセルを選択
  A1 A2 セルを選択
 [ データ ] タブ 「データツール」グループ
 「データの入力規則」 を クリック

 201905160008.png

 [データの入力規則]ダイアログが表示される
 「入力値の種類」で、「すべての値」になっているのを
 ▼ を クリックし 「リスト」 を 選択する
 
 201905160009.png

 表示が変化する
 「 元の値 」欄に、カーソルを移動
 「文房具」・「食料品」と入力されているセル範囲を選択する
 G1:H1 を 選択する
 [OK] クリック

 201905160010.png

 A1 セルを 選択すると セルの右側に、▼ が、表示される
 その ▼ をクリックすると 「文房具」・「食料品」と表示される
 「文房具」 を 選択しておく

 201905160011.png

 次は、B1:B2 セルに、リストを設定する
 B1:B2 セル範囲を選択
 先ほどと同様の操作
 [ データ ] タブ 「データツール」グループ
 「データの入力規則」 を クリック
 「リスト」 を 選択
 「 元の値 」欄に、カーソルを移動
 今度は、以下の数式を入力する
 【 =INDIRECT(A1) 】

 201905160012.png
 ※ A1 セルが、空欄の場合、注意文が表示される 
   元の値はエラーと判断されます。続けますか?
   この場合 [ はい ] を クリック
   201905160013.png

 B1 セルを 選択すると 右側に ▼ が、表示される
 ▼ を クリックすると、A1 が、「文房具」の場合
 「ホッチキス」・「定規」・「ハサミ」 が、表示される
 A1 が 「食料品」の場合は
 「おかし」・「パン」・「ジュース」 と 表示される

 201905160014.png

以上です

要点

 1.名前の定義を使用する
 2.リストの 元の値 欄に、 INDIRECT 関数 を 使用する
   A1 に入力されている文字列が 「名前」で定義されている場合
   文字列をセル範囲として認識できる関数
スポンサーサイト



[ 2019年05月16日 11:37 ] カテゴリ:エクセル | TB(0) | CM(0)

色のついたセルを判別

質問内容

エクセル 2013です。
特定のセル(仮にA3)を 
黄色で塗りつぶしたら
自動的に 
別のセル(E3)も
黄色で塗りつぶしされる
式を教えていただけますか

色のついたセルを判別するには
「4.0マクロ関数」を使います

この関数は、
* 単体では再計算しない
* セルに入力しても関数としては使用できない

1.A1 セルをクリック
2.[数式]タブ 「名前の定義」クリック

  201810101001.png

3.「新しい名前」ダイアログが表示される」
  「名前」欄に、適当な名前を設定
  例:COLOR1
4.「参照範囲」欄に、以下の数式
  =GET.CELL(63,!$A1)+NOW( )*0
5.[OK]クリック

  201810101002.png
  
6.確認のために
  E3セルに、【 =COLOR1 】と入力
  表示される番号をメモする
  ※ これがA3セルの色の番号になる

  201810101003.png

7.E3 セルをクリック
  [ Delete]キーで内容をクリア
8.[ホーム]タブ
  「条件付き書式設定」をクリック
  「新しいルール」クリック

  201810101004.png

9.「数式を使用して、書式設定するセルを決定」を選択
10.次の数式を満たす場合に値を書式設定欄に
  =COLOR1=6
 ※ 6 は、おそらく黄色の場合の番号
  [書式]をクリックし、塗りつぶしの色を設定する
  「OK]クリックする

  201810101005.png

設定したら、一度別のセルで[Enter]キーを押す
※ シートに変化がないと設定が起動しないことがある

.
[ 2018年10月12日 11:52 ] カテゴリ:エクセル | TB(0) | CM(0)

Excel シート名をセルに表示する

CELL 関数
セルの書式、位置、または内容に関する情報を返します


書式
 CELL ( 検査の種類, [対象範囲] )


検査の種類」に入れる内容によって
さまざまな用途に使えます

今回は、"filename" というものを使います

なので、以下のような数式

 =CELL("filename",A1)

 ※ 「A1」は、どのセルを指定しても問題なし
   シート名を求めたいシート内にあるセルならばどれでもよい

この数式の意味は、セル A1 の情報を求めるという意味

このままでは、
例えば
 「Cドライブ」
 -「 User 」
 -「ユーザー名 」
 -「 Documents 」
 -「 フォルダ名 」
 -「 ファイル名 」
 -「 シート名 」


※ C:\Users\(ユーザー名)\Documents\(フォルダ名)\[ファイル名.xlsx]シート名

なので、「シート名」以外を除きます

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

数式の意味は
CELL関数で求めた内シート名は、
一番最後(文字列の右端)の数文字
なので、RIGHT 関数
シート名の文字数は
全ての文字数から 直前の「 ] 」までの文字数を引いた数


注意事項
CELL関数で求められるものは、
 保存してあるファイルのみです
 新規作成のファイルは使えません
 あと 
 通常は無いはずですが
 ファイル名に 「 ] 」 が含まれるファイル


参考

"検査の種類" として指定できる文字列値

"address"
 対象範囲の左上隅にあるセルの参照を表す文字列

"col"
 対象範囲の左上隅にあるセルの列番号

"color"
 負の数を色で表す書式がセルに設定されている場合は 1 それ以外の場合は 0 (ゼロ)
 注: この値は、Excel Online、Excel Mobile、および Excel Starter ではサポートされていません

"contents"
 対象範囲の左上隅にあるセルの値 (数式ではない)

"filename"
 対象範囲を含むファイルのフル パス名 (文字列)
 対象範囲を含むワークシートが保存されていなかった場合は、空白文字列 ("")
 注: この値は、Excel Online、Excel Mobile、および Excel Starter ではサポートされていません

"format"
 セルの表示形式に対応する文字列定数。
 各種の表示形式に対応する文字列定数については、次の表を参照してください
 セルが負数に対応する色で書式設定されている場合、文字列定数の末尾に "-" が付きます
 正数またはすべての値をかっこで囲む書式がセルに設定されている場合、
 結果の文字列定数の末尾に "()" が付きます
 注: この値は、Excel Online、Excel Mobile、および Excel Starter ではサポートされていません

"parentheses"
 正の値またはすべての値をかっこで囲む書式がセルに設定されている場合は 1 それ以外の場合は 0
 注: この値は、Excel Online、Excel Mobile、および Excel Starter ではサポートされていません

"prefix"
 セルの "文字位置" に対応する文字列定数
 セルが 左詰めの文字列を含むときは単一引用符 (')
 右詰めの文字列を含むときは二重引用符 (")
 中央揃えの文字列を含むときはキャレット (^)
 両揃えの文字列を含むときは円記号 (¥)
 また、セルにそれ以外のデータが入力されているときは空白文字列 ("") になります
 注: この値は、Excel Online、Excel Mobile、および Excel Starter ではサポートされていません

"protect"
 セルがロックされていない場合は 0 ロックされている場合は 1
 注: この値は、Excel Online、Excel Mobile、および Excel Starter ではサポートされていません

"row"
 対象範囲の左上隅にあるセルの行番号

"type"
 セルに含まれるデータのタイプに対応する文字列定数
 セルが空白の場合は "b" (Blank の頭文字)
 セルに文字列定数が入力されている場合は "l" (Label の頭文字)
 その他の値が入力されている場合は "v" (Value の頭文字) になります

"width"
 小数点以下を切り捨てた整数のセル幅
 セル幅の単位は、既定のフォント サイズの 1 文字の幅と等しくなります。
 注: この値は、Excel Online、Excel Mobile、および Excel Starter ではサポートされていません

[ 2018年05月30日 14:05 ] カテゴリ:エクセル | TB(0) | CM(0)

色を条件に○○したい Excel 2013

色を条件に何か設定したい場合
  [名前の管理]を利用する
  [4.0マクロ関数]を利用する
  作業列を使用する


まずは、「4.0マクロ関数」を使用するために
オプションの設定を変更します

 1. [ファイル] タブをクリック、[オプション] をクリック
 2. [セキュリティ センター] をクリック、
 3. [セキュリティ センターの設定] をクリック
 4. [マクロの設定] をクリック
 5.  [すべてのマクロを有効にする
   (推奨しません。危険なコードが実行される可能性があります)] をクリック


重要
 この設定は使用しないことをお勧めします。
 使用する場合は、マクロのセキュリティ設定をできるだけ早く
 [警告を表示せずにすべてのマクロを無効にする] に戻すことをお勧めします

 [ファイル制限機能の設定] をクリックし、[ファイルの種類] で [Excel 4 マクロ シート] をクリック
 [開く] のチェック ボックスがオンになっていることを確認します
 [選択した形式のファイルを開く処理] で
 [選択した形式のファイルを保護されたビューで開き、編集可能にする] をクリック


例 セル範囲 B1:B3 のセルに色が着いている場合の設定
   セル範囲 T1:T3 に 「色の数値」を表示させる


[名前の管理]から設定します

 1.作業列にするセル範囲 T1:T3 をドラッグ選択する
 2..[数式]タブ [定義された名前]グループ [名前の管理]クリック
 3.[名前の管理]ダイアログ [新規作成]クリック
 4.[新しい名前]ダイアログ [名前]欄に、例えば「色番号」と入力
 5.[参照範囲]欄に、=GET.CELL(63,!B1)+NOW()*0
 この「GET.CELL」関数が、4.0マクロ関数 変に考えずそのまま真似する


GET.CELL(63,!B1) 
 ※ 「63」はセルの背景色を検査する場合のGET.CELL関数の引数
   「!B1」は検査を行う対象セルアドレスを指定します。

NOW()*0
 ※ 4.0マクロ関数単体で使用した場合、入力時点での計算しか行いません。
   ワークシートの再計算時には再計算の対象になりません
   そこで再計算の対象となる関数NOWを組み合わせることで再計算を有効にします。
   ただ、数値は影響して欲しくないので「0」(ゼロ)を掛けている


セル範囲 T1:T3 が選択されている状態で
  数式バーに、「=色番号」と入力
  [Ctrl]キーと[Enter]キーを同時押し 一気に入力する

 ※ セル範囲 B1:B3 が塗りつぶされている場合
    この瞬間に 数値が表示されます


 この表示される数値は、
 色により違うので その数値を確認し 
数値による設定を行うことも可能



[ 2015年01月27日 20:08 ] カテゴリ:エクセル | TB(0) | CM(1)

重複の削除

別々のシートにあるデータで、重複しないデータを新しいシートへ表示したい
シート1のデータ
  201403020001.png
シート2のデータ
  201403020002.png
シート3へ重複していないデータだけを表示したい

 データの多いシート1のデータをコピーし、シート3へ貼り付ける 
 [データ]タブ 「並べ替えとフィルター」グループ
  「詳細設定」をクリック
  201403020003.png
 「フィルターオプションの設定」画面が表示される
  「抽出先」 「選択範囲内」にチェックが入っている
  「リスト範囲」には、コピーしたデータ範囲
  「検索条件範囲」の右端にある 201403020005.png をクリックする
  201403020004.png
 シート2のデータ範囲をドラッグ選択する
  201403020006.png
 「フィルターオプションの設定」画面の右端の 201403020007.png をクリックす
 「重複するレコードは無視する」にチェックを入れる
  201403020008.png
 [OK]クリック
  抽出される

  201403020009.png
  「3行」「6行」「8行」が非表示になっている
 [ホーム]タブ 「セル」グループ 「削除▼」クリック
  表示されるメニューから「シートの行を削除」をクリックする

  201403020010.png
 選択されていたデータが削除される
  ただ、残っているはずのデータは、非表示のままなので、行番号は「4」から表示されている

  201403020011.png
 [データ]タブ 「並べ替えとフィルター」グループ 「クリア」をクリックする
  201403020012.png

上へスクロールすれば全て確認できる

201403020013.png


注意事項
このように「フィルタオプション」を使用する場合
データには、「列見出し」を付けましょう
[ 2014年03月02日 15:07 ] カテゴリ:エクセル | TB(0) | CM(0)
カウンター
検索フォーム
ブロとも申請フォーム
QRコード
QR