excel中的資料有效性自定義格式,如何使用

2022-02-17 07:38:00 字數 5627 閱讀 4542

1樓:杜茗本代天

[應用一]下拉選單輸入的實現

例1:直接自定義序列

有時候我們在各列各行中都輸入同樣的幾個值,比如說,輸入學生的等級時我們只輸入四個值:優秀,良好,合格,不合格。我們希望excel2000單元格能夠象下拉框一樣,讓輸入者在下拉選單中選擇就可以實現輸入。

操作步驟:先選擇要實現效果的行或列;再點選"資料\有效性",開啟"資料有效性"對話方塊;選擇"設定"選項卡,在"允許"下拉選單中選擇"序列";在"資料**"中輸入"優秀,良好,合格,不合格"(注意要用英文輸入狀態下的逗號分隔!);選上"忽略空值"和"提供下拉選單"兩個核取方塊。

點選"輸入資訊"選項卡,選上"選定單元格顯示輸入資訊",在"輸入資訊"中輸入"請在這裡選擇"。

例2:利用表內資料作為序列源。

有時候序列值較多,直接在表內列印區域外把序列定義好,然後引用。

操作步驟:先在同一工作表內的列印區域外要定義序列填好(假設在在z1:z8),如「單親家庭,殘疾家庭,殘疾學生,特困,低收人,突發事件,孤兒,軍烈屬」等,然後選擇要實現效果的列(資助原因);再點選"資料\有效性",開啟"資料有效性"對話方塊;選擇"設定"選項卡,在"允許"下拉選單中選擇"序列";「**」欄點選右側的按鈕(有一個紅箭頭),用滑鼠拖動滾動條,選中序列區域z1:

z8(如果記得,可以直接輸入=$z$1:$z$8;選上"忽略空值"和"提供下拉選單"兩個核取方塊。點選"輸入資訊"選項卡,選上"選定單元格顯示輸入資訊",在"輸入資訊"中輸入"請在這裡選擇"。

例3:橫跨兩個工作表來製作下拉選單

用indirect函式實現跨工作表

在例2中,選擇**一步把輸入=$z$1:$z$8換成=indirect("表二!$z$1:$z$8"),就可實現橫跨兩個工作表來製作下拉選單。

[應用二]自動實現輸入法中英文轉換

有時,我們在不同行或不同列之間要分別輸入中文和英文。我們希望excel能自動實現輸入法在中英文間轉換。

操作步驟:假設我們在a列輸入學生的中文名,b列輸入學生的英文名。先選定b列,點選進入"資料\有效性",開啟"資料有效性"對話方塊;選擇"輸入法"對話方塊,在"模式"下拉選單中選擇"關閉(英文模式)";然後再"確定",看看怎麼樣。

[應用三]資料唯一性檢驗

員工的身份證號碼應該是唯一的,為了防止重複輸入,我們用「資料有效性」來提示大家。

操作步驟:選中需要建立輸入身份證號碼的單元格區域(如b2至b14列),執行「資料→有效性」命令,開啟「資料有效性」對話方塊,在「設定」標籤下,按「允許」右側的下拉按鈕,在隨後彈出的快捷選單中,選擇「自定義」選項,然後在下面「公式」方框中輸入公式:=countif(b:

b,b2)=1,確定返回。以後在上述單元格中輸入了重複的身份證號碼時,系統會彈出提示對話方塊,並拒絕接受輸入的號碼。

2樓:匿名使用者

你要求的是第四位是分,第六位是秒,同時共六位,那麼,自定義為

=and(mid(a1,3,1)="分",mid(a1,6,1)="秒",len(a1)=6)

3樓:骨頭

假設要設定a列有效性,選中a列,資料有效性,自定義

公式=(find("分",a1)=3)*(find("秒",a1)=6)*(len(a1)=6)

excel資料有效性和自定義格式結合使用!

4樓:匿名使用者

據我所知,這個只能用vba來解決,因為在函式裡面沒value(值)與text(顯示值)的分別,在這裡,你的序列區裡的「黃瓜10元」是它的顯示值,而單元格的實際值是「10」

sub macro2()

dim str as string

dim ran as range

for each ran in range("e2:e3")str = str & "," & ran.textnext

str = right(str, len(str) - 1)range("a1").validation.deleterange("a1").

validation.add type:=xlvalidatelist, alertstyle:

=xlvalidalertstop, operator:= _

xlbetween, formula1:=strend sub

將上面**複製到你的**區,將**中的「e2:e3」修改成你的序列區,將「a1」修改成你的有效性區,執行巨集,便可得到你要的結果了

5樓:書玄

1.在a1單元格輸入函式公式=if(a3=false,a3,choose(randbetween(1,2),"ture","false"))

2.a2、a3、a4單元格設定資料有效性,限定為true、false。

當a3為false時,a1為false,當a3為true時,a1可通過函式公式隨機顯示為true或者false。

6樓:匿名使用者

如果你的數值僅僅是10和12

你可以設定有效性為序列:10,12

然後設定單元格的自定義格式為[=10]黃瓜0元;香蕉0元見下圖效果

7樓:匿名使用者

設定有效性的單元格格式預設只是:常規,而你的引用單元格格式卻是:"黃""瓜"0"元",選中顯示在有效性單元格只能是常規格式:10或12。

其實,你何必要自定義格式?一個單元格要自定義一次,很費事的。

直接輸入:黃瓜10元、香蕉12元不就得了?

若需要用」黃瓜10元、香蕉12元「參與計算,還可用提取公式。

8樓:e俠兔

如果只是這兩種(不超過3個)型別,可以還用自定義格式。

excel資料有效性中自定義的設定

9樓:表裡如一

假設在b4設定有效性,選擇b4

設定有效性時選擇自定義,公式輸入:

=and(b4<=if(a4<40,150,50),b4>10)點選確定即可

10樓:黑豆

自定義裡寫公式:and(ac ,ae)

a\b\c\d\e都用實際單元格替代。

警告資訊裡寫"再試一次!"

11樓:郝老師電腦教學

excel資料有效性如何設定?

excel中資料有效性中的「自定義」與「序列」的本質區別是什麼?

12樓:

excel中資料有效性中的「自定義」與「序列」區別為:形式不同、引用不同、錯誤條件不同。

一、形式不同

1、「自定義」:「自定義」是以輸入的公式形式來限定有效性。

2、「序列」:「序列」是以輸入文字序列或是直接引用單元格區域來限定有效性。

二、引用不同

1、「自定義」:「自定義」可以對任意單元格區域進行引用。

2、「序列」:「序列」它只能是單一行或是一列進行引用。

三、錯誤條件不同

13樓:s今生緣

序列是輸入一組已知資料或指定**引用區域中的資料來限定有效性,選定單元格後右側會跟隨一個下拉選單供選擇,當然也可以自己輸入,但如果輸入的內容與有效性範圍不同,會跳出提醒框。

自定義與序列不同,以公式形式限定有效性,且選定單元格後,無下拉選單可供選擇,如果判斷輸入的內容不符合限定公式則跳出錯誤提醒框。比如限定i4單元格必須是除以3餘1的數,用:=mod(i4,3)=1作為自定義公式;限定h10的第二個字元必須是「中」:

=find("中",h10)=2等等,自定義常用於常規方法無法限定有效性時。

excel如何設定這樣的資料有效性?

14樓:浪小客

1、我們首先選擇滑鼠單擊開啟我們製作的excel**。

2、我們設定性別,你不能輸入資料,我們直接點選選擇「男性」或「女性」,我們選擇性別中的單元格並單擊選單資料 - 有效性。

3、在資料有效性中設定資料有效性的條件是序列,然後源中輸入的「男性,女性」用逗號分隔英文。

4、我們現在不需要輸入男性和女性,只需使用滑鼠選擇。

5、例如,id號通常是18位數。我們首先將id號的單元格格式設定為文字。因為超過11位數字將用科學記數法表示。

6、然後我們可以設定id卡單元格中資料的有效性。設定資料有效性的條件是文字長度,資料長度設定為18。

15樓:點點科

在excel2016有時需要設定資料有效性,具體操作如下:

1、點選「資料」中的「資料有效性」,如圖:

2、然後在有效性條件中選擇對應的允許值,如圖:

4、使用序列得到的結果是下拉選單選項,如圖:

5、也可以在「出錯警告」選項中設定警告樣式及標題,如圖:

這樣就全面設定好了資料有效性了。

16樓:

使用「資料有效性」可以做到,方法如下:

1.直接選中a列(點選列標題那個a)

2.點選工具欄中的資料→資料有效性→資料有效性3.在彈出的視窗中「允許」下面的下拉框裡選擇「序列」

4.在「**」裡面輸入

李強,劉玉,蘭建,王順

5.點選上面的「出錯警告」標籤,勾選「輸入無效資料時顯示下列出錯警告」

6.如果不是這些人名的時候需要彈出自定義的錯誤警告,可以在下面的「標題」和「錯誤資訊」中填寫

7.點選確定

圖中為關於第6步中說到的內容,當輸入的內容不是這四個人名時會彈出這樣一個視窗,這裡標題填寫的是「錯誤的人名」,錯誤資訊裡填寫的是「在a列中只允許「李強,劉玉,蘭建,王順」這四個人名!」

17樓:琦秉機舉

選擇sheet2的那個序列區域,定義為名稱,再在sheet1的資料有效性引用此名稱

定義名稱方法發:

選擇單元格或單元格區域-插入-名稱-定義-輸入名稱,下面的引用位置輸入單元格區域。

如上圖。

在sheet1的資料有效性的**-裡輸入

=mincheng

18樓:匿名使用者

在【資料】【資料有效性】選【序列】在【**】裡輸入:

"李強","劉玉","蘭建","王順"

注意這裡的符號都是西文的喲,你可以把我寫的,複製到表裡去試一試!

19樓:匿名使用者

你可以在**區域之外, 在一列中輸入這四個人名,然後選擇"資料"選單下的"資料有效性", 選擇這個人名, 就可以做到(見附圖)

20樓:匿名使用者

將有效人名在不用的列中輸入,然後在a列的單元格選 資料有效性,允許裡-選序列,資料裡-選擇剛才有效人名所在的單元格。出錯警告等自己選擇吧。

21樓:葵葵演說一季

手機wps**中怎麼設定資料有效性,資料有效性控制資料長度

22樓:匿名使用者

資料——有效性——序列——李強,劉玉,蘭建,王順

注意間隔符號 系統會提示

23樓:匿名使用者

對於03版本的excel方法如上;

07版的設定如下:選定要設定的所有單元格-----選擇「資料」選單----資料有效性(在中後部)----設定選項卡中允許選「自定義」下面公式處輸入那幾個名字,用英文逗號隔開-----出錯警告選項卡中標題處自擬,錯誤資訊可以為輸入資訊不在範圍內,請選擇******(四個名字中的一個)----確定就ok了

Excel資料有效性,EXCEL中資料有效性怎麼使用

在a2設定資料有效性,看圖,a1可以繼續輸入0.45 0.56 你a1輸入的資料是一個具體數字吧?例如0.45,或者是0.56,還是整個格子裡輸入0.45 0.56?一般來說資料有效性是設定在本單元格,例如在a2設定有效性,小數,大於0.45,小於0.56,如果是臆想在a1輸入的是0.45 0.56...

關於excel中的資料有效性設定

你可以先在後面某幾列先輸入部門名稱,和姓名,比如在g列輸入部門名稱,然後選定這些名稱,點插入 名稱 定義,輸入一個名稱如aaa,在h列輸入人事部員工姓名,按上面步驟定義名稱為bbb,i列輸入市場部員工姓名,定義為ccc.做完後選定部門那列 比如是b列 資料 有效性 序列,在 那輸入 aaa按確定,再...

Excel資料有效性設定下拉選單內容有多個,但選擇時只能單選

1 首先在excel 中點選 開發工具 選項卡中的 插入 下方的三角形符號。2 在開啟的選項選單中點選 方框帶對勾 的圖示。3 拖動滑鼠在 合適的位置畫出一個方框,可以右鍵點選方框選擇 編輯文字 來更改顯示的文字內容。4 根據需要設定多個選擇框並進行排版,點選方框位置即可新增對勾形成多選的樣式。回答...