Excel(エクセル) データの入力規則で「ユーザー設定」を使って複数の特定の数値しか入力できなくする方法

シェアする

2019年1月28日に ZOOM でのオンラインセミナー開催致します!

30代半ばから税理士になった体験談セミナー

The following two tabs change content below.

松井 元(まつい はじめ)

静岡県三島市の松井会計事務所に勤務する理系税理士。 文理両方のスキルの融合を考えており、このブログは以下を中心に更新している。
●税金・会計に関すること
●IT(Excel、VBAなど)を使った業務効率化
●自分のこと(考え、私生活)

さらに詳細なプロフィールはこちら
松井会計事務所へのお仕事ご依頼・お問い合わせはこちら
税金・会計の無料メルマガはじめます

Excel(エクセル)でセルに数値を入力する場合、複数の特定の数値以外を入力できなくしたい場合があります。

「データの入力規則」の入力値の種類を「ユーザー設定」で設定することによってできます。

具体例を取り上げながら説明します。

レクタングル大 広告

1、「データの入力規則」の入力値の種類を「ユーザー設定」にする

次のシートで、各科目の支払額を税抜金額に変換することを考えみます。

C列に各科目の支払額を入力し、またセル「C2」に消費税率を入力すると E列で税抜金額を計算します。

Image(1)

E列のセル「E6」は「=IF(D6<>”非課税”,C6/(1+$C$2/100),C6)」と入力し、下の行にもコピペしてあります。

Image(2)

IF 関数を使っています。

D列に「非課税」と入力してある科目については、次の式で支払額を税抜金額に変換しています。

税抜金額 = 支払額 / (1 + 消費税率 / 100)

それ以外の場合は C列に入力した支払額をそのまま E列に出力するようにしています。

さて、ここからが本題です。

消費税率を入力するセル「C2」には消費税率として正しい数値以外は入力できなくしたいです。

消費税率は 8 と 10 のどちらかとしておきます(少し気が早いですが、、)。

セル「C2」を選択した状態で、「データ」タブの「データの入力規則」を出します。

Image(3)

「設定」の「入力値の種類」として「ユーザー設定」を選択しましょう。

Image(4)

そして「数式」に「=OR(C2=8,C2=10)」と入力します。

Image(10)

これにより、セル「C2」への入力は「C2=8」となる場合、または「C2=10」となる場合以外は受け付けられなくなります。

誤入力した場合のエラーメッセージも入力しておきましょう。

「エラーメッセージ」で「スタイル」は「停止」を選択し、

・タイトルに「注」

・エラーメッセージに「消費税率は 8 か 10 を入力して下さい。」

zoom を使ったオンラインセミナー開催致します。
◆1/28 30代半ばから税理士になった体験談セミナー

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録

と入力します。

Image(6)

これでセル「C2」に 8 か 10 以外の数値が入力された場合には、上で入力したエラーメッセージが表示されます。

Image(7)

正しい消費税率を入力すれば、E列の「税抜金額」もちゃんと計算されます。

消費税率を「8」にした場合 ↓

Image(8)

消費税率を「10」にした場合↓

Image(9)

2、入力可能な数値を3つ以上にすることも可能

さて、先ほどのセル「C2」に消費税率として入力可能な数値を3つ以上にすることもできます。

3、5、8、10 を入力できるようにしたい場合は、「数式」に「=OR(C2=3,C2=5,C2=8,C2=10)」と入力します。

Image(11)

入力可能な数値を1つ1つ直接指定できるのが、ユーザー設定で OR を使った数式を使うメリットですね。

範囲で指定しようとすると余計な数値の入力も可能になってしまいます。

なお、入力可能な数値を1つに絞りたい場合は、同じくユーザー設定で OR を使った数式を入力してもできますがユーザー設定を使わない方法でも対応可能です。

「入力値の種類」として「整数」を選択し、「データ」として「次の値に等しい」を選択します。そして「値」には入力可能としたい数値を入れます(今回8としておきます)。

Image(12)

入力可能な数値を 1つに絞るのであれば、このやり方でも大丈夫ですね。

3、まとめ

セルに複数の特定の数値しか入力できなくするためには、データの入力規則の「ユーザー設定」を使う必要があります。

作成したシートを複数人で使いまわす場合など特に誤入力を防止できる仕組みを作っておくことは大事です。

zoom を使ったオンラインセミナー開催致します。
◆1/28 30代半ばから税理士になった体験談セミナー

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録
税理士試験まとめ、税法論文の書き方

————————————————

税理士試験についてのまとめはこちらです!

税法2科目免除大学院についてのまとめはこちらです!

kindle電子書籍を出版しました。

税理士試験の税法2科目免除のために大学院で税法論文(修士論文)を書いた経験を元に、大学院生としての目線から税法論文の書き方を体系化した書籍です↓

大学院での税法論文の書き方

本書籍の内容についてはこちらの記事をご確認下さい。

お問い合わせ・フォローボタン

———————————

よろしければフォロー下さい↓

Twitter フォローボタン:

Feedly フォローボタン:
follow us in feedly

Facebookページ:
*いいね!を押して頂くと更新情報が届きます。

レクタングル大 広告
レクタングル大 広告
関連コンテンツ



シェアする

フォローする