Skip i'r prif gynnwys
 

Dilysu data Excel: ychwanegu, defnyddio, copïo a dileu dilysu data yn Excel

Yn rhagori, mae'r Dilysu Data yn nodwedd ddefnyddiol y gallwch ei defnyddio i gyfyngu ar yr hyn y gall defnyddiwr ei roi mewn cell. Er enghraifft, gall y nodwedd dilysu data eich helpu i gyfyngu hyd llinynnau testun, neu destun sy'n dechrau / gorffen gyda chymeriadau penodol, neu werthoedd unigryw i'w nodi ac ati.

Y tiwtorial hwn, byddwn yn siarad am sut i ychwanegu, defnyddio a chael gwared ar ddilysiad data yn Excel, bydd rhai gweithrediadau sylfaenol ac uwch y nodwedd hon yn cael eu dangos yn fanwl hefyd.

Tabl cynnwys:

1. Beth yw dilysu data yn Excel?

2. Sut i ychwanegu dilysiad data yn Excel?

3. Enghreifftiau sylfaenol ar gyfer dilysu data

4. Rheolau arfer uwch ar gyfer dilysu data

5. Sut i olygu dilysiad y data yn Excel?

6. Sut i ddod o hyd i gelloedd a'u dilysu â data yn Excel?

7. Sut i gopïo'r rheol dilysu data i gelloedd eraill?

8. Sut i ddefnyddio dilysiad data i gylchredeg cofnodion annilys yn Excel?

9. Sut i gael gwared ar ddilysu data yn Excel?

1. Beth yw dilysu data yn Excel?

Mae adroddiadau Dilysu Data gall nodwedd eich helpu i gyfyngu ar gynnwys mewnbwn yn eich taflen waith. Fel rheol, gallwch greu rhai rheolau dilysu ar gyfer atal neu ganiatáu dim ond rhyw fath o ddata sydd i'w nodi mewn rhestr o gelloedd dethol.

Rhai defnyddiau sylfaenol o'r nodwedd Dilysu Data:

  • 1. Unrhyw werth: ni pherfformir unrhyw ddilysiad, gallwch fewnbynnu unrhyw beth i'r celloedd penodedig.
  • 2. Gwerth cyfan: dim ond rhifau cyfan a ganiateir.
  • 3. Degol: caniatáu nodi rhifau cyfan yn ogystal â degolion.
  • 4. Rhestr: dim ond gwerthoedd o'r rhestr a ddiffiniwyd ymlaen llaw y caniateir eu nodi neu eu dewis. Mae'r gwerthoedd yn cael eu harddangos mewn rhestr ostwng.
  • 5. Dyddiad: dim ond dyddiadau a ganiateir.
  • 6. amser: dim ond amseroedd a ganiateir.
  • 7. Hyd testun: dim ond caniatáu hyd penodol y testun gael ei nodi.
  • 8. arferiad: creu rheolau fformiwla arfer ar gyfer dilysu mewnbwn defnyddwyr.

2. Sut i ychwanegu dilysiad data yn Excel?

Yn nhaflen waith Excel, gallwch ychwanegu dilysiad data gyda'r camau canlynol:

1. Dewiswch restr o gelloedd lle rydych chi am osod y dilysiad data, ac yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, gweler y screenshot:

2. Yn y Dilysu Data blwch deialog, o dan y Gosodiadau tab, crëwch eich rheolau dilysu eich hun. yn y blychau meini prawf, gallwch gyflenwi unrhyw un o'r mathau canlynol:

  • Gwerthoedd: Teipiwch rifau yn y blychau meini prawf yn uniongyrchol;
  • Cyfeirnod celloedd: Cyfeiriwch gell yn y daflen waith neu daflen waith arall;
  • Fformiwlâu: Creu fformwlâu mwy cymhleth fel amodau.

Er enghraifft, byddaf yn creu rheol sy'n caniatáu nodi rhifau cyfan rhwng 100 a 1000 yn unig, yma gosodwch y meini prawf fel y dangosir isod y llun:

3. Ar ôl ffurfweddu'r amodau, gallwch fynd i'r Neges Mewnbwn or Rhybudd Gwall tab i osod y neges fewnbwn neu'r rhybudd gwall ar gyfer y celloedd dilysu fel y dymunwch. (Os nad ydych chi am osod y rhybudd, cliciwch OK i orffen yn uniongyrchol.)

3.1) Ychwanegu neges fewnbwn (dewisol):

Gallwch greu neges sy'n ymddangos wrth ddewis cell sy'n cynnwys dilysu data. Mae'r neges hon yn helpu i atgoffa'r defnyddiwr o'r hyn y gallant ei fewnbynnu i'r gell.

Ewch i'r Neges Mewnbwn tab a gwneud y canlynol:

  • Gwiriwch y Dangos neges mewnbwn pan ddewisir cell opsiwn;
  • Rhowch y teitl a'r neges atgoffa rydych chi eu heisiau yn y meysydd cyfatebol;
  • Cliciwch OK i gau'r blwch deialog hwn.

Nawr, pan ddewiswch gell wedi'i dilysu, dangosir blwch neges fel a ganlyn:

3.2) Creu negeseuon gwall ystyrlon (dewisol):

Yn ogystal â chreu'r neges fewnbwn, gallwch hefyd ddangos rhybuddion gwall pan fydd data annilys yn cael ei roi mewn cell gyda dilysiad data.

Ewch i'r Rhybudd Gwall tab y Dilysu Data blwch deialog, gwnewch fel hyn:

  • Gwiriwch y Dangos rhybudd gwall ar ôl mewnbynnu data annilys opsiwn;
  • Yn y arddull rhestr ostwng, dewiswch un math rhybuddio a ddymunir sydd ei angen arnoch:
    • Stopio (diofyn): Mae'r math hwn o rybudd yn atal defnyddwyr rhag mewnbynnu data annilys.
    • rhybudd: Yn rhybuddio defnyddwyr bod y data yn annilys, ond nad yw'n atal ei nodi.
    • Gwybodaeth: Yn hysbysu defnyddwyr am gofnod data annilys yn unig.
  • Rhowch y teitl a'r neges rhybuddio rydych chi eu heisiau yn y meysydd cyfatebol;
  • Cliciwch OK i gau'r blwch deialog.

Ac yn awr, wrth nodi gwerth annilys, bydd y blwch rhybuddio negeseuon yn cael ei nodi fel y nodir isod.

Stop opsiwn: Gallwch glicio Ailadroddwch i deipio gwerth arall neu Diddymu i gael gwared ar y cofnod.

rhybudd opsiwn: Cliciwch Ydy i nodi'r cofnod annilys, Na i'w addasu, neu Diddymu i gael gwared ar y cofnod.

Gwybodaeth opsiwn: Cliciwch OK i nodi'r cofnod annilys neu Diddymu i gael gwared ar y cofnod.

Nodyn: Os na osodwch eich neges arfer eich hun yn y Rhybudd Gwall blwch, diofyn Stop bydd blwch prydlon rhybuddio yn cael ei arddangos fel y dangosir isod:

3. Enghreifftiau sylfaenol ar gyfer dilysu data

Wrth ddefnyddio'r nodwedd Dilysu Data hon, darperir 8 opsiwn adeiledig i chi osod y dilysiad data. Megis: unrhyw werth, rhifau cyfan a degolion, dyddiad ac amser, rhestr, hyd testun a fformiwla arfer. Yn yr adran hon byddwn yn trafod sut i ddefnyddio rhai o'r opsiynau adeiledig yn Excel?

3.1 Dilysu data ar gyfer rhifau cyfan a degolion

1. Dewiswch restr o gelloedd lle rydych chi am ganiatáu rhifau cyfan neu ddegolion yn unig, ac yna cliciwch Dyddiad > Dilysu Data > Dilysu Data.

2. Yn y Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • Dewiswch yr eitem gyfatebol Rhif cyfan or Degol yn y Caniatáu blwch gwympo.
  • Ac yna, dewiswch un o'r meini prawf sydd eu hangen arnoch chi yn y Dyddiad blwch (Yn yr enghraifft hon, dwi'n dewis rhwng opsiwn).
  • Awgrymiadau: Mae'r meini prawf yn cynnwys: rhwng, nid rhwng, hafal i, ddim yn hafal i, mwy na, llai na, yn fwy na neu'n hafal i, yn llai na neu'n hafal i.
  • Nesaf, rhowch y Isafswm ac Uchafswm gwerthoedd sydd eu hangen arnoch (rwyf am gael y rhifau rhwng 0 ac 1 00).
  • O'r diwedd, cliciwch OK botwm.

3. Nawr, dim ond y rhifau cyfan o 0 i 100 y caniateir eu nodi yn eich celloedd dethol.

3.2 Dilysu data ar gyfer dyddiad ac amser

I ddilysu dyddiad neu amser penodol i'w nodi, mae'n hawdd trwy ddefnyddio hwn Dilysu Data, gwnewch fel a ganlyn:

1. Dewiswch restr o gelloedd lle rydych chi am ganiatáu dim ond y dyddiadau neu'r amseroedd penodol, ac yna cliciwch Dyddiad > Dilysu Data > Dilysu Data.

2. Yn y Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • Dewiswch yr eitem gyfatebol dyddiad or amser yn y Caniatáu blwch gwympo.
  • Ac yna, dewiswch un o'r meini prawf sydd eu hangen arnoch chi yn y Dyddiad blwch (Dyma fi'n dewis fwy na opsiwn).
  • Awgrymiadau: Mae'r meini prawf yn cynnwys: rhwng, nid rhwng, hafal i, ddim yn hafal i, mwy na, llai na, yn fwy na neu'n hafal i, yn llai na neu'n hafal i.
  • Nesaf, rhowch y Dyddiad cychwyn mae ei angen arnoch (rwyf am i'r dyddiadau fod yn fwy na 8/20/2021).
  • O'r diwedd, cliciwch OK botwm.

3. Nawr, dim ond y dyddiadau sy'n fwy na 8/20/2021 y caniateir eu nodi yn eich celloedd dethol.

3.3 Dilysu data ar gyfer hyd testun

Os oes angen i chi gyfyngu ar nifer y nodau y gellir eu teipio i mewn i gell. Er enghraifft, i gyfyngu'r cynnwys i ddim mwy na 10 nod ar gyfer ystod benodol, hyn Dilysu Data hefyd yn gallu gwneud ffafr i chi.

1. Dewiswch restr o gelloedd lle rydych chi am gyfyngu hyd testun, ac yna cliciwch Dyddiad > Dilysu Data > Dilysu Data.

2. Yn y Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Hyd testun oddi wrth y Caniatáu blwch gwympo.
  • Ac yna, dewiswch un o'r meini prawf sydd eu hangen arnoch chi yn y Dyddiad blwch (Yn yr enghraifft hon, dwi'n dewis llai na opsiwn).
  • Awgrymiadau: Mae'r meini prawf yn cynnwys: rhwng, nid rhwng, hafal i, ddim yn hafal i, mwy na, llai na, yn fwy na neu'n hafal i, yn llai na neu'n hafal i.
  • Nesaf, rhowch y Uchafswm rhif y mae angen i chi ei gyfyngu (dwi eisiau hyd y testun ddim mwy na 10 nod).
  • O'r diwedd, cliciwch OK botwm.

3. Nawr, dim ond llai na 10 nod y mae'r celloedd a ddewiswyd yn caniatáu teipio'r llinyn testun.

3.4 Rhestr dilysu data (gwymplen)

Gyda hyn pwerus Dilysu Data nodwedd, gallwch greu gwymplen mewn celloedd yn gyflym ac yn hawdd hefyd. Gwnewch fel hyn:

1. Dewiswch y celloedd targed ble i fewnosod y gwymplen, ac yna cliciwch Dyddiad > Dilysu Data > Dilysu Data.

2. Yn y Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch rhestr oddi wrth y Caniatáu rhestr ostwng.
  • Yn y ffynhonnell blwch testun, teipiwch yr eitemau rhestr sydd wedi'u gwahanu'n uniongyrchol gan atalnodau. Er enghraifft, i gyfyngu mewnbwn y defnyddiwr i dri dewis, teipiwch Not Started, In Progress, Completed, neu gallwch ddewis rhestr o gelloedd sy'n cynnwys y gwerthoedd i fewnosod y gwymplen yn seiliedig ar.
  • O'r diwedd, cliciwch OK botwm.

3. Nawr, mae'r gwymplen wedi'i chreu i'r celloedd fel y dangosir isod:

Cliciwch i wybod gwybodaeth fanylach o'r gwymplen ...

4. Rheolau arfer uwch ar gyfer dilysu data

Yn yr adran hon, byddaf yn cyflwyno sut i wneud rhai rheolau dilysu data arfer datblygedig i ddatrys eich mathau o broblemau, megis: creu fformwlâu dilysu i ganiatáu rhifau neu dannau testun yn unig, dim ond gwerthoedd unigryw, dim ond rhifau ffôn penodedig, cyfeiriadau e-bost ac ati. .

4.1 Mae dilysu data yn caniatáu rhifau neu destunau yn unig

 Caniatáu i rifau yn unig gael eu nodi gyda'r swyddogaeth Dilysu Data

Er mwyn caniatáu rhifau mewn ystod o gelloedd yn unig, gwnewch hyn:

1. Dewiswch ystod o gelloedd yr ydych am i rifau yn unig gael eu nodi.

2. Cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla hon: = ISNUMBER (A2) i mewn i'r Fformiwla blwch testun. (A2 yw'r gell gyntaf o'r ystod a ddewiswyd rydych chi am ei chyfyngu)
  • Cliciwch OK botwm i gau'r ymgom hwn.

3. O hyn ymlaen, dim ond rhifau y gellir eu rhoi yn y celloedd a ddewiswyd.

Nodyn: Mae hyn yn YNYS swyddogaeth yn caniatáu unrhyw werthoedd rhifol mewn celloedd dilysedig, gan gynnwys cyfanrifau, degolion, ffracsiynau, dyddiadau ac amseroedd.

 Caniatáu i linynnau testun yn unig gael eu nodi gyda'r swyddogaeth Dilysu Data

I gyfyngu cofnodion celloedd i destun yn unig, gallwch ddefnyddio'r Dilysu Data nodwedd gyda fformiwla arfer yn seiliedig ar y ISTEXT swyddogaeth, gwnewch fel hyn:

1. Dewiswch ystod o gelloedd yr ydych am i dannau testun yn unig gael eu nodi.

2. Cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla hon: = ISTEXT (A2) i mewn i'r Fformiwla blwch testun. (A2 yw'r gell gyntaf o'r ystod a ddewiswyd rydych chi am ei chyfyngu)
  • Cliciwch OK botwm i gau'r ymgom hwn.

3. Nawr, wrth fewnbynnu data i'r celloedd penodol, dim ond data fformat testun y gellir ei ganiatáu.

4.2 Mae dilysu data yn caniatáu gwerthoedd alffaniwmerig yn unig

At rai dibenion, rydych chi am ganiatáu i wyddor a gwerthoedd rhifol gael eu nodi, ond cyfyngu'r cymeriadau arbennig fel ~,%, $, gofod, ac ati, bydd yr adran hon yn cyflwyno rhai triciau i chi.

 Caniatáu gwerthoedd alffaniwmerig yn unig sydd â swyddogaeth Dilysu Data

Er mwyn atal y cymeriadau arbennig ond dim ond caniatáu gwerthoedd alffaniwmerig, dylech greu fformiwla arferiad i mewn i'r Dilysu Data swyddogaeth, gwnewch fel a ganlyn:

1. Dewiswch ystod o gelloedd yr ydych am i werthoedd alffaniwmerig yn unig gael eu nodi.

2. Cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformwlâu uchod, A2 yw'r gell gyntaf o'r ystod a ddewiswyd rydych chi am ei chyfyngu.

3. Nawr, dim ond yr wyddor a'r gwerthoedd rhifol y caniateir eu nodi, a bydd y cymeriadau arbennig yn cael eu cyfyngu wrth deipio fel isod y llun a ddangosir:

 Caniatáu gwerthoedd alffaniwmerig yn unig sydd â nodwedd anhygoel

Efallai bod y fformiwla uchod yn gymhleth i ni ei deall a'i chofio, yma, byddaf yn cyflwyno nodwedd ddefnyddiol - Atal Teipio of Kutools ar gyfer Excel, gyda'r nodwedd hon, gallwch chi ddatrys y swydd hon yn gyflym yn rhwydd.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel hyn:

1. Dewiswch ystod o gelloedd yr ydych am i werthoedd alffaniwmerig yn unig gael eu nodi.

2. Yna, cliciwch Kutools > Atal Teipio > Atal Teipio, gweler y screenshot:

3. Yn y popped allan Atal Teipio blwch deialog, dewiswch Atal math mewn cymeriadau arbennig opsiwn, gweler y screenshot:

4. Yna, cliciwch Ok botwm, ac yn y blychau prydlon canlynol, cliciwch Ydy > OK i orffen y llawdriniaeth. Nawr, yn y celloedd a ddewiswyd, dim ond yr wyddor a'r gwerthoedd rhifol sy'n cael eu caniatáu, gweler y screenshot:

4.3 Mae dilysu data yn caniatáu i destunau ddechrau neu ddiweddu gyda nodau penodol

Os dylai'r holl werthoedd mewn ystod benodol ddechrau neu ddiweddu gyda chymeriad neu is-haen benodol, gallwch ddefnyddio dilysiad data gyda fformiwla arfer yn seiliedig ar y swyddogaeth EXACT, CHWITH, DDE neu COUNTIF.

 Caniatáu i destunau ddechrau neu ddiweddu gyda chymeriadau penodol sydd ag un cyflwr yn unig

Er enghraifft, rwyf am i'r testunau ddechrau neu ddiweddu â “CN” wrth nodi'r llinynnau testun mewn celloedd penodol, gwnewch fel hyn:

1. Dewiswch ystod o gelloedd sydd ond yn caniatáu testunau sy'n dechrau neu'n gorffen gyda rhai cymeriadau.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • Begin with: =EXACT(LEFT(A2,2),"CN")
    End with: =EXACT(RIGHT(A2,2),"CN")
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformwlâu uchod, A2 yw cell gyntaf yr ystod a ddewiswyd, y rhif 2 yw nifer y nodau a nodwyd gennych, CN yw'r testun rydych chi am ddechrau neu ddiweddu ag ef.

3. O hyn ymlaen, dim ond y llinyn testun sy'n dechrau neu'n gorffen gyda'r nodau penodedig y gellir eu rhoi yn y celloedd a ddewiswyd. Fel arall, bydd rhybudd rhybuddio yn cael ei roi allan i'ch atgoffa fel isod dangosir y llun:

Awgrym: Mae'r fformwlâu uchod yn sensitif i achosion, os nad oes angen achos-sensitif arnoch, defnyddiwch y fformwlâu CONTIF isod:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")

Nodyn: Mae'r seren * yn gerdyn gwyllt sy'n cyfateb i un neu fwy o gymeriadau.

 Caniatáu i destunau ddechrau neu ddiweddu gyda chymeriadau penodol â meini prawf lluosog (NEU resymeg)

Er enghraifft, os ydych chi am i'r testunau ddechrau neu ddiweddu gyda “CN” neu “UK” fel y dangosir isod y llun, mae angen i chi ychwanegu enghraifft arall o EXACT trwy ddefnyddio arwydd plws (+). Gwnewch y camau canlynol:

1. Dewiswch ystod o gelloedd sydd ond yn caniatáu testunau sy'n dechrau neu'n gorffen gyda meini prawf lluosog.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
    End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformwlâu uchod, A2 yw cell gyntaf yr ystod a ddewiswyd, y rhif 2 yw nifer y nodau a nodwyd gennych, CN ac UK yw'r testunau penodol rydych chi am ddechrau neu ddiweddu â nhw.

3. Nawr, dim ond llinyn y testun sy'n dechrau neu'n gorffen gyda'r nodau penodedig y gellir eu rhoi yn y celloedd a ddewiswyd.

Awgrym: I anwybyddu achos-sensitif, defnyddiwch y fformwlâu CONTIF isod:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Nodyn: Mae'r seren * yn gerdyn gwyllt sy'n cyfateb i un neu fwy o gymeriadau.

4.4 Mae dilysu data yn caniatáu i gofnodion gynnwys / rhaid cynnwys testun penodol

Yn yr adran hon, byddaf yn siarad am sut i gymhwyso'r Dilysiad Data i ganiatáu i werthoedd gynnwys neu beidio â chynnwys un is-haen benodol neu un o lawer o is-haenau yn Excel.

 Rhaid i gofnodion gynnwys un neu un o lawer o destunau penodol

Rhaid i gofnodion gynnwys un testun penodol

Er mwyn caniatáu cofnodion sy'n cynnwys llinyn testun penodol, er enghraifft, dylai'r holl werthoedd a gofnodwyd gynnwys y testun “KTE” fel islaw'r screenshot a ddangosir, gallwch gymhwyso dilysiad y data gyda fformiwla arfer yn seiliedig ar y swyddogaethau FIND ac ISNUMBER. Gwnewch fel hyn:

1. Dewiswch ystod o gelloedd sydd ond yn caniatáu testunau sy'n cynnwys testun penodol.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom o'r gwymplen Caniatáu.
  • Ac yna, nodwch un o'r fformwlâu isod yn y Fformiwla blwch testun.
  • =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
    =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformwlâu uchod, A2 yw cell gyntaf yr ystod a ddewiswyd, y testun KTE yw'r llinyn testun y mae'n rhaid i'r cofnodion ei gynnwys.

3. Nawr, pan nad yw'r gwerth a gofnodwyd yn cynnwys y testun a ddyluniwyd, bydd blwch procio rhybudd yn popio allan.

Rhaid i gofnodion gynnwys un o lawer o destunau penodol

Mae'r fformiwla uchod yn gweithio ar gyfer un llinyn testun yn unig, os oes angen i unrhyw un o lawer o dannau testun gael eu caniatáu yn y celloedd fel y dangosir y screenshot a ganlyn, dylech ddefnyddio'r swyddogaethau CYFLWYNO, DERBYN ac ISNUMBER gyda'i gilydd i greu fformiwla.

1. Dewiswch ystod o gelloedd sydd ond yn caniatáu testunau sy'n cynnwys unrhyw un o lawer o eitemau.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Yna, nodwch un o'r fformwlâu isod yn ôl yr angen yn y Fformiwla blwch testun.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
  • Ac yna, cliciwch OK i gau'r blwch deialog.

Nodyn: Yn y fformwlâu uchod, A2 yw cell gyntaf yr ystod a ddewiswyd, C2: C4 yw'r rhestr o werthoedd rydych chi am ganiatáu i gofnodion gynnwys unrhyw un ohonyn nhw.

3. Ac yn awr, dim ond y cofnodion sy'n cynnwys unrhyw un o'r gwerthoedd yn y rhestr benodol y gellir eu nodi.

 Rhaid i gofnodion beidio â chynnwys un neu un o lawer o destunau penodol

Rhaid i gofnodion beidio â chynnwys un testun penodol

I ddilysu rhaid i'r cofnodion beidio â chynnwys testun penodol, er enghraifft, er mwyn caniatáu gwerthoedd na ddylai gynnwys y testun “KTE” mewn cell, gallwch ddefnyddio'r swyddogaethau ISERROR a FIND i greu rheol dilysu data. Gwnewch fel hyn:

1. Dewiswch ystod o gelloedd sydd ond yn caniatáu testunau nad ydynt yn cynnwys testun penodol.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch un o'r fformwlâu isod yn y Fformiwla blwch testun.
  • =ISERROR(FIND("KTE",A2))                  (Case sensitive)
    =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformwlâu uchod, A2 yw cell gyntaf yr ystod a ddewiswyd, y testun KTE yw'r llinyn testun na ddylai'r cofnodion ei gynnwys.

3. Nawr, bydd y cofnodion sy'n cynnwys y testun penodol yn cael eu hatal rhag cael eu nodi.

Rhaid i gofnodion beidio â chynnwys un o lawer o destunau penodol

Er mwyn atal nodi un o lawer o dannau testun mewn rhestr fel y dangosir isod y llun, dylech wneud fel hyn:

1. Dewiswch ystod o gelloedd rydych chi am i rai testunau gael eu hatal.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Yna, rhowch y fformiwla isod yn y Fformiwla blwch testun.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
  • Ac yna, cliciwch OK i gau'r blwch deialog.

Nodyn: Yn y fformwlâu uchod, A2 yw cell gyntaf yr ystod a ddewiswyd, C2: C4 yw'r rhestr o werthoedd yr ydych am eu hatal os yw cofnodion yn cynnwys unrhyw un ohonynt.

3. O hyn ymlaen, bydd y cofnodion sy'n cynnwys unrhyw un o'r testunau penodol yn cael eu hatal rhag cael eu nodi.

4.5 Mae dilysu data yn caniatáu gwerthoedd unigryw yn unig

Os ydych chi am atal data dyblyg rhag cael ei roi mewn ystod o gell, bydd yr adran hon yn cyflwyno rhai dulliau cyflym ar gyfer datrys y dasg hon yn Excel.

 Caniatáu gwerthoedd unigryw yn unig sydd â swyddogaeth Dilysu Data

Fel rheol, gall y nodwedd Dilysu Data gyda fformiwla arfer yn seiliedig ar swyddogaeth COUNTIF eich helpu chi, gwnewch y camau canlynol:

1. Dewiswch y celloedd neu'r golofn nad ydych ond am i werthoedd unigryw gael eu nodi.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • =COUNTIF($A$2:$A$9,A2)=1
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2: A9 yw'r ystod o gelloedd yr ydych am ganiatáu gwerthoedd unigryw yn unig, a A2 yw cell gyntaf yr ystod a ddewiswyd.

3. Nawr, dim ond gwerthoedd unigryw sy'n caniatáu eu nodi, a bydd neges rhybuddio yn ymddangos pan fydd data dyblyg yn cael ei fewnbynnu, gweler y screenshot:

 Caniatáu gwerthoedd unigryw yn unig gyda chod VBA

Gall y cod VBA canlynol hefyd eich helpu i atal gwerthoedd dyblyg rhag cael eu nodi, gwnewch fel hyn:

1. Cliciwch ar y dde ar y tab dalen rydych chi am ganiatáu gwerthoedd unigryw yn unig, a dewiswch Gweld y Cod o'r ddewislen cyd-destun, yn y popped allan Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïwch a gludwch y cod canlynol i'r Modiwl gwag:

Cod VBA: Dim ond caniatáu gwerthoedd unigryw mewn ystod o gelloedd:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Nodyn: Yn y cod uchod, mae'r A1: A100 ac A: A yw'r celloedd yn y golofn yr ydych am eu hatal rhag dyblygu, newidiwch nhw i'ch angen.

2. Yna arbedwch a chau'r cod hwn, nawr, wrth nodi gwerth dyblyg yng nghell A1: A100, mae blwch procio rhybudd yn cael ei nodi fel y nodir isod y llun:

 Caniatáu gwerthoedd unigryw yn unig sydd â nodwedd ddefnyddiol

Os oes gennych Kutools ar gyfer Excel, Gyda'i Atal Dyblyg nodwedd, gallwch osod dilysiad data i atal dyblygu ar gyfer ystod o gelloedd gyda dim ond sawl clic.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel hyn:

1. Dewiswch yr ystod o gelloedd rydych chi am atal y gwerthoedd dyblyg ond caniatewch ddata unigryw yn unig.

2. Yna, cliciwch Kutools > Atal Teipio > Atal Dyblyg, gweler y screenshot:

3. A bydd neges rybuddio yn ymddangos i'ch atgoffa y bydd y Dilysiad Data yn cael ei ddileu os ydych chi'n defnyddio'r nodwedd hon, cliciwch Ydy ac yn y blwch prydlon canlynol, cliciwch OK, gweler sgrinluniau:

4. Nawr, pan fyddwch chi'n mewnbynnu rhywfaint o ddata dyblyg yn eich celloedd penodedig, mae blwch prydlon yn cael ei arddangos i'ch atgoffa nad yw'r data dyblyg yn ddilys, gweler y screenshot:

4.6 Mae dilysu data yn caniatáu uwchgynhadledd / llythrennau bach / achos priodol yn unig

Mae'r Dilysiad Data hwn yn nodwedd bwerus, gall hefyd helpu i ganiatáu i ddefnyddiwr fynd i mewn i gofnodion uwchsain, llythrennau bach neu achosion cywir yn unig mewn ystod o gell. Gwnewch y camau canlynol:

1. Dewiswch yr ystod o gelloedd yr ydych chi am i uwchgynhadledd, llythrennau bach neu destun achos cywir gael eu nodi yn unig.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch un o'r fformwlâu isod sydd eu hangen arnoch chi yn y Fformiwla blwch testun.
  • =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
    =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
    =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio.

3. Nawr, dim ond y cofnodion sy'n cydymffurfio â'r rheol a grëwyd gennych a dderbynnir.

4.7 Mae dilysu data yn caniatáu gwerthoedd sy'n bodoli / ddim yn bodoli mewn rhestr arall

Gall caniatáu i'r gwerthoedd fodoli neu ddim yn bodoli mewn rhestr arall gael eu nodi mewn ystod o gelloedd fod yn broblem boenus i'r mwyafrif ohonom. Mewn gwirionedd, gallwch ddefnyddio nodwedd dilysu data gyda fformiwla syml yn seiliedig ar swyddogaeth COUNTIF i ddelio ag ef.

Er enghraifft, rwyf am i'r gwerthoedd yn yr ystod C2: C4 yn unig gael eu nodi mewn ystod o gelloedd fel islaw'r screenshot a ddangosir, i ddatrys y swydd hon, gwnewch fel hyn:

1. Dewiswch yr ystod o gelloedd rydych chi am gymhwyso'r dilysiad data.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch un o'r fformwlâu isod sydd eu hangen arnoch chi yn y Fformiwla blwch testun.
  • =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
    =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio, C2: C4 yw'r rhestr o werthoedd rydych chi am eu hatal neu eu caniatáu os yw cofnodion yn un ohonynt.

3. Nawr, mae'r cofnodion yn cydymffurfio yn unig â'r rheol y gwnaethoch chi ei chreu y gellir ei nodi, bydd eraill yn cael eu hatal.

4.8 Mae dilysu data yn gorfodi dim ond fformat rhif ffôn i'w nodi

Pan fyddwch yn mewnbynnu gwybodaeth gweithwyr eich cwmni, mae angen i un golofn deipio'r rhif ffôn, er mwyn sicrhau mewnbynnu rhifau ffôn yn gyflym ac yn gywir, yn yr achos hwn, gallwch osod dilysiad data ar gyfer y rhifau ffôn. Er enghraifft, rwyf am i'r rhif ffôn gan fod y fformat hwn (123) 456-7890 yn cael ei ganiatáu ar daflen waith, bydd yr adran hon yn cyflwyno dau dric cyflym ar gyfer datrys y dasg hon.

 Gorfodi fformat rhif ffôn yn unig gyda swyddogaeth Dilysu Data

Er mwyn caniatáu nodi fformat rhif ffôn penodol yn unig, gwnewch hyn:

1. Dewiswch y rhestr o gelloedd rydych chi am i fformat rhif ffôn penodol gael eu nodi, ac yna cliciwch ar y dde, dewiswch Celloedd Fformat o'r ddewislen cyd-destun, gweler y screenshot:

2. Yn y Celloedd Fformat blwch deialog, o dan y Nifer tab, dewiswch Custom yn y chwith Categori blwch rhestr, ac yna mewnbynnu'r fformat rhif ffôn sydd ei angen arnoch yn y blwch testun Math, er enghraifft, byddaf yn defnyddio hwn (###) ### - #### fformat, gweler y screenshot:

3. Yna, cliciwch OK i gau'r blwch deialog.

4. Ar ôl fformatio'r celloedd, ewch ymlaen i ddewis y celloedd, ac yna agorwch y Dilysu Data blwch deialog trwy glicio Dyddiad > Dilysu Data > Dilysu Data, yn y dialog popped allan, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla hon = AC (ISNUMBER (A2), LEN (A2) = 10) i mewn i'r blwch testun Fformiwla.
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ddilysu'r rhif ffôn.

5. Nawr, wrth nodi rhif 10 digid, bydd yn cael ei drawsnewid i'r fformat rhif ffôn penodol yn awtomatig yn ôl yr angen, gweler sgrinluniau:

Nodyn: Os nad yw'r rhif a gofnodwyd yn 10 digid, bydd blwch neges rhybuddio yn galw allan i'ch atgoffa, gweler y screenshot:

 Gorfodi fformat rhif ffôn yn unig gyda nodwedd ddefnyddiol

Kutools ar gyfer Excel'S Dilysu Rhif Ffôn gall nodwedd hefyd eich helpu i orfodi fformat rhif ffôn yn unig i gael ei nodi gyda dim ond sawl clic.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel hyn:

1. Dewiswch y rhestr o gelloedd sy'n caniatáu rhif ffôn penodol yn unig, yna, cliciwch Kutools > Atal Teipio > Dilysu Rhif Ffôn, gweler y screenshot:

2. Yn y Rhif ffôn blwch deialog, dewiswch y fformat rhif ffôn penodol sydd ei angen arnoch neu gallwch greu eich fformatio eich hun trwy glicio ar y Ychwanegu botwm, gweler y screenshot:

3. Ar ôl dewis neu osod fformat y rhif ffôn, cliciwch OK, nawr, dim ond y rhif ffôn gyda'r fformatio penodol y gellir ei nodi, fel arall, bydd neges rybuddio yn ymddangos i'ch atgoffa, gweler y screenshot:

4.9 Grym dilysu data Cyfeiriadau e-bost yn unig sydd i'w nodi

Gan dybio, mae angen i chi deipio cyfeiriadau e-bost lluosog mewn colofn o daflen waith, er mwyn atal rhai fformatau cyfeiriadau e-bost anghywir rhag cael eu nodi, fel rheol, gallwch chi osod rheol dilysu data ar gyfer caniatáu fformatio cyfeiriadau e-bost yn unig.

 Grym yn unig fformat cyfeiriadau E-bost gyda swyddogaeth Dilysu Data

Trwy ddefnyddio'r nodwedd Dilysu Data gyda fformiwla arfer, gallwch greu rheol ar gyfer atal y cyfeiriadau e-bost annilys rhag cael eu nodi'n gyflym, gwnewch hyn:

1. Dewiswch y celloedd rydych chi am i gyfeiriadau e-bost yn unig gael eu nodi, yna cliciwch Dyddiad > Dilysu Data > Dilysu Data.

2. Yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla hon = ISNUMBER (MATCH ("*@*.?*", A2,0)) i mewn i'r Fformiwla blwch testun.
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio.

3. Nawr, os nad yw'r testun a gofnodwyd yn fformat cyfeiriad e-bost, bydd blwch neges rhybuddio yn galw allan i'ch atgoffa, gweler y screenshot:

 Gorfodi fformat cyfeiriadau E-bost yn unig gyda nodwedd ddefnyddiol

Kutools ar gyfer Excel yn cefnogi nodwedd anhygoel - Dilysu Cyfeiriad E-bost, gyda'r cyfleustodau hwn, gallwch atal y cyfeiriadau e-bost annilys gyda dim ond un clic.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel a ganlyn:

1. Dewiswch y celloedd yr ydych yn caniatáu iddynt nodi cyfeiriadau e-bost yn unig, yna cliciwch Kutools > Atal Teipio > Dilysu Cyfeiriad E-bost. Gweler y screenshot:

2. Ac yna, dim ond fformatio cyfeiriadau e-bost sy'n caniatáu mynd i mewn, fel arall, bydd blwch neges rhybuddio yn galw allan i'ch atgoffa, gweler y screenshot:

4.10 Mae dilysu data yn gorfodi cyfeiriadau IP yn unig i'w nodi

Yr adran hon, byddaf yn cyflwyno rhai triciau cyflym i osod dilysiad data i dderbyn cyfeiriadau IP yn unig mewn ystod o gelloedd.

 Gorfodi fformat cyfeiriadau IP yn unig gyda swyddogaeth Dilysu Data

Caniatáu i gyfeiriadau IP yn unig gael eu teipio i ystod benodol o gelloedd, gwnewch fel hyn:

1. Dewiswch y celloedd rydych chi am i gyfeiriad IP yn unig gael eu nodi, yna cliciwch Dyddiad > Dilysu Data > Dilysu Data.

2. Yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio.

3. Nawr, os byddwch chi'n rhoi cyfeiriad IP annilys yn y gell, bydd blwch neges rhybuddio yn galw allan i'ch atgoffa fel isod y llun a ddangosir:

 Gorfodi fformat cyfeiriadau IP yn unig gyda chod VBA

Yma, gall y cod VBA canlynol hefyd helpu i ganiatáu i gyfeiriadau IP yn unig gael eu nodi a chyfyngu ar fewnbynnu eraill, gwnewch fel hyn:

1. De-gliciwch y tab dalen a chlicio Gweld y Cod o'r ddewislen cyd-destun, yn yr agoriad Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïwch y cod VBA isod ynddo.

Cod VBA: dilysu celloedd i dderbyn cyfeiriad IP yn unig

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Nodyn: Yn y cod uchod, A2: A10 yw'r ystod gell rydych chi am ei derbyn cyfeiriadau IP yn unig.

2. Yna arbedwch a chau'r cod hwn, nawr, dim ond y cyfeiriadau IP cywir sy'n caniatáu eu rhoi yn y celloedd penodol.

 Gorfodi fformat cyfeiriadau IP yn unig gyda nodwedd hawdd

Os oes gennych Kutools ar gyfer Excel wedi'i osod yn eich llyfr gwaith, ei Dilysu Cyfeiriad IP gall nodwedd eich helpu chi i ddatrys y dasg hon hefyd.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel hyn:

1. Dewiswch y celloedd yr ydych yn caniatáu i gyfeiriadau IP yn unig gael eu nodi, yna cliciwch Kutools > Atal Teipio > Dilysu Cyfeiriad IP. Gweler y screenshot:

2. Ar ôl cymhwyso'r nodwedd hon, nawr, dim ond cyfeiriad IP sy'n caniatáu mynd i mewn, fel arall, bydd blwch neges rhybuddio yn galw allan i'ch atgoffa, gweler y screenshot:

4.11 Mae dilysu data yn cyfyngu ar werthoedd sy'n fwy na chyfanswm y gwerth

Gan dybio, mae gennych adroddiad costau misol, a chyfanswm y gyllideb yw $ 18000, nawr, mae angen i chi nad yw'r cyfanswm yn y rhestr dreuliau yn fwy na'r cyfanswm rhagosodedig $ 18000 fel islaw'r screenshot a ddangosir. Yn yr achos hwn, gallwch greu rheol dilysu data trwy ddefnyddio'r swyddogaeth SUM i atal swm y gwerthoedd rhag mynd y tu hwnt i gyfanswm rhagosodedig.

1. Dewiswch y rhestr o gelloedd lle rydych chi am i'r gwerthoedd fod yn gyfyngedig.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • =SUM($B$2:$B$7)<=18000
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, B2: B7 yw'r ystod o gelloedd rydych chi am gyfyngu ar gofnodion.

3. Nawr, wrth nodi'r gwerthoedd yn yr ystod B2: B7, os yw cyfanswm y gwerthoedd yn llai na $ 18000, mae'r dilysiad yn pasio. Os bydd unrhyw werth yn gwneud i'r cyfanswm fod yn fwy na $ 18000, bydd blwch neges rhybuddio yn galw allan i'ch atgoffa.

4.12 Mae dilysu data yn cyfyngu mynediad celloedd yn seiliedig ar gell arall

Pan fyddwch am gyfyngu cofnodion data mewn rhestr o gelloedd yn seiliedig ar y gwerth mewn cell arall, gall y nodwedd Dilysu Data helpu i ddatrys y swydd hon hefyd. Er enghraifft, os mai'r gell C1 yw'r testun “Ydw”, caniateir i'r ystod A2: A9 nodi unrhyw beth, ond, os yw'r gell C1 yn destun arall, ni chaniateir nodi unrhyw beth yn yr ystod A2: A9 fel y dangosir y sgrinluniau isod. :

I ddatrys y datrysiad hwn, gwnewch fel hyn:

1. Dewiswch y rhestr o gelloedd lle rydych chi am i'r gwerthoedd fod yn gyfyngedig.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • =$C$1="Yes"
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, C1 ydy'r gell yn cynnwys y testun penodol rydych chi am ei ddefnyddio, a'r testun “Ydy”Yw'r testun rydych chi am gyfyngu celloedd arno, newidiwch nhw i'ch angen.

3. Nawr, os oes gan gell C1 y testun “Ydw”, gellir nodi unrhyw beth yn yr ystod A2: A9, os oes gan gell C1 destun arall, ni fyddwch yn gallu nodi unrhyw werth, gweler isod demo:

4.13 Mae dilysu data yn caniatáu dim ond dyddiau'r wythnos neu benwythnosau y gellir eu nodi

Os mai dim ond dyddiau'r wythnos (o ddydd Llun i ddydd Gwener) neu benwythnosau (dydd Sadwrn a dydd Sul) sydd eu hangen arnoch i gael eich nodi mewn rhestr o gelloedd, bydd y Dilysu Data hefyd yn gallu eich helpu chi, gwnewch y camau canlynol:

1. Dewiswch y rhestr o gelloedd lle rydych chi am i'r dyddiau wythnos neu ddyddiau'r wythnos gael eu nodi.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch un o'r fformwlâu isod yn y Fformiwla blwch testun yn ôl yr angen.
  • =WEEKDAY(A2,2)<6                      (allow only weekdays)
    =WEEKDAY(A2,2)>5                      (allow only weekends)
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio.

3. Nawr, dim ond yn y celloedd penodol y gallwch chi nodi'r dyddiad yn ystod yr wythnos neu'r penwythnos yn seiliedig ar eich angen.

4.14 Mae dilysu data yn caniatáu dyddiad a gofnodwyd yn seiliedig ar y dyddiad heddiw

Weithiau, efallai y bydd angen i chi ganiatáu i'r dyddiadau sy'n fwy neu'n llai na heddiw gael eu nodi mewn rhestr o gelloedd yn unig. Mae'r Dilysu Data nodwedd gyda'r HEDDIW gall swyddogaeth wneud ffafr i chi. Gwnewch fel hyn:

1. Dewiswch y rhestr o gelloedd lle rydych chi am i'r dyddiad yn y dyfodol yn unig (dyddiad sy'n fwy na heddiw) gael ei nodi.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • =A2>Today()
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio.

3. Nawr, dim ond y dyddiadau sy'n fwy na dyddiad heddiw y gellir eu rhoi yn y celloedd, fel arall, bydd blwch neges rhybuddio yn galw allan i'ch atgoffa, gweler y screenshot:

Awgrym:

1. Er mwyn caniatáu nodi'r dyddiad blaenorol (dyddiad llai na heddiw), cymhwyswch y fformiwla isod yn y Dilysiad Data:

=A2<Today()

2. Caniatáu i ddyddiad o fewn ystod benodol o ddyddiad gael ei nodi, fel y dyddiadau yn y 30 diwrnod nesaf, nodwch y fformiwla isod yn y Dilysiad Data:

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 Mae dilysu data yn caniatáu amser a gofnodwyd yn seiliedig ar yr amser cyfredol

Os ydych chi am ddilysu data yn seiliedig ar yr amser cyfredol, er enghraifft, dim ond amseroedd cyn neu ar ôl yr amser cyfredol y gellir eu teipio i'r celloedd. Gallwch greu eich fformiwla dilysu data eich hun, gwnewch fel hyn:

1. Dewiswch y rhestr o gelloedd lle rydych chi eisiau dim ond yr amseroedd cyn neu ar ôl yr amser cyfredol i gael eu nodi.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch amser oddi wrth y Caniatáu rhestr ostwng.
  • Yna dewiswch llai na i ganiatáu dim ond amseroedd cyn yr amser cyfredol, neu fwy na i ganiatáu amseroedd ar ôl yr amser cyfredol yn ôl yr angen o'r Dyddiad gollwng i lawr.
  • Ac yna, yn y Amser diwedd or Amser cychwyn blwch, nodwch y fformiwla isod:
  • =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio.

3. Nawr, dim ond yr amseroedd cyn neu ar ôl yr amser cyfredol y gellir eu rhoi yn y celloedd penodol.

4.16 Dilysu data dyddiad y flwyddyn benodol neu'r flwyddyn gyfredol

Er mwyn caniatáu dyddiadau mewn blwyddyn benodol neu flwyddyn gyfredol yn unig, gallwch ddefnyddio dilysiad data gyda fformiwla arfer yn seiliedig ar swyddogaeth BLWYDDYN.

1. Dewiswch y rhestr o gelloedd lle rydych chi am i'r dyddiadau mewn blwyddyn benodol gael eu nodi yn unig.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch Custom oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, nodwch y fformiwla isod yn y Fformiwla blwch testun.
  • =YEAR(A2)=2020
  • Cliciwch OK botwm i gau'r ymgom hwn.

Nodyn: Yn y fformiwla uchod, A2 yw cell gyntaf y golofn rydych chi am ei defnyddio, 2020 yw'r rhif blwyddyn rydych chi am ei gyfyngu.

3. Ac yna, dim ond y dyddiadau ym mlwyddyn 2020 y gellir eu nodi, os na, bydd blwch negeseuon rhybuddio yn ymddangos fel islaw'r screenshot a ddangosir:

Awgrym:

Er mwyn caniatáu dyddiadau yn unig yn y flwyddyn gyfredol, gallwch gymhwyso'r fformiwla isod yn y dilysiad data:

=YEAR(A2)=YEAR(TODAY())

4.17 Dilysu data'r dyddiad yn yr wythnos neu'r mis cyfredol

Os ydych chi am ganiatáu i'r defnyddiwr allu nodi dyddiadau'r wythnos neu'r mis cyfredol mewn celloedd penodol, bydd yr adran hon yn cyflwyno rhai fformiwlâu ar gyfer delio â'r dasg hon yn Excel.

 Caniatáu nodi dyddiad yr wythnos gyfredol

1. Dewiswch y rhestr o gelloedd lle rydych chi am i'r dyddiadau yn ystod yr wythnos gyfredol gael eu nodi yn unig.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch dyddiad oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, dewiswch rhwng oddi wrth y Dyddiad gollwng i lawr.
  • Yn y Dyddiad cychwyn blwch testun, nodwch y fformiwla hon: = HEDDIW () - WYTHNOS (HEDDIW (), 3)
  • Yn y Dyddiad gorffen blwch testun, nodwch y fformiwla hon: = HEDDIW () - WYTHNOS (HEDDIW (), 3) +6
  • O'r diwedd, cliciwch OK botwm.

3. Yna, dim ond y dyddiadau yn yr wythnos gyfredol y gellir eu nodi, bydd dyddiadau eraill yn cael eu hatal fel y dangosir isod:

 Caniatáu nodi dyddiad y mis cyfredol

Er mwyn caniatáu nodi dyddiadau'r mis cyfredol yn unig, gwnewch fel a ganlyn:

1. Dewiswch y rhestr o gelloedd lle rydych chi am i'r dyddiadau yn y mis cyfredol gael eu nodi yn unig.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y popped allan Dilysu Data blwch deialog, o dan y Gosodiadau tab, gwnewch y gweithrediadau canlynol:

  • dewiswch dyddiad oddi wrth y Caniatáu rhestr ostwng.
  • Ac yna, dewiswch rhwng y Dyddiad gollwng i lawr.
  • Yn y Dyddiad cychwyn blwch testun, nodwch y fformiwla hon: = DYDDIAD (BLWYDDYN (HEDDIW ()), MIS (HEDDIW ()), 1)
  • Yn y Dyddiad gorffen blwch testun, nodwch y fformiwla hon: = DYDDIAD (BLWYDDYN (HEDDIW ()), MIS (HEDDIW ()), DYDD (DYDDIAD (BLWYDDYN (HEDDIW ()), MIS (HEDDIW ()) + 1,1) -1))
  • O'r diwedd, cliciwch OK botwm.

3. O hyn ymlaen, dim ond dyddiadau'r mis cyfredol sy'n caniatáu mynd i mewn i'r celloedd a ddewiswyd.

5. Sut i olygu dilysiad y data yn Excel?

I olygu neu newid y rheol bresennol o ddilysu data, dilynwch y camau isod:

1. Dewiswch unrhyw un o'r celloedd gyda'r rheol dilysu data.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data i fynd i'r Dilysu Data blwch deialog, yn y blwch, golygu neu newid y rheolau i'ch angen, ac yna gwirio Cymhwyso'r newidiadau hyn i bob cell arall sydd â'r un gosodiadau opsiwn i gymhwyso'r rheol newydd hon i bob cell arall sydd â'r meini prawf dilysu gwreiddiol. Gweler y screenshot:

3. Cliciwch OK i achub y newidiadau.

6. Sut i ddod o hyd i gelloedd a'u dilysu â data yn Excel?

Os ydych chi wedi creu rheolau dilysu data lluosog yn eich taflen waith, nawr, mae angen i chi ddarganfod a dewis y celloedd a gymhwysodd y rheolau dilysu data, y Ewch i Arbennig gall gorchymyn eich helpu chi i ddewis pob math o ddilysiad data neu fath penodol o ddilysu data.

1. Ysgogi'r daflen waith rydych chi am ddod o hyd iddi a dewis y celloedd gyda dilysiad data.

2. Yna, cliciwch Hafan > Dod o Hyd i a Dewis > Ewch i Arbennig, gweler y screenshot:

3. Yn y Ewch i Arbennig blwch deialog, dewiswch Dilysu data > Popeth, gweler y screenshot:

4. Ac mae'r holl gelloedd sydd â dilysiad data wedi'u dewis ar unwaith yn y daflen waith gyfredol.

Awgrymiadau: Os ydych chi am ddewis un math penodol o ddilysiad data yn unig, yn gyntaf, dewiswch un gell sy'n cynnwys y dilysiad data penodol rydych chi am ei ddarganfod, yna ewch i'r Ewch i Arbennig blwch deialog, a dewis Dilysu data > Yr un.

7. Sut i gopïo'r rheol dilysu data i gelloedd eraill?

Gan dybio, rydych chi wedi creu rheol dilysu data ar gyfer rhestr o gelloedd, ac nawr, mae angen i chi gymhwyso'r un rheol dilysu data i gelloedd eraill. Yn lle creu'r rheol eto, gallwch chi gopïo a gludo'r rheol bresennol i gelloedd eraill yn gyflym ac yn hawdd.

1. Cliciwch i ddewis un gell gyda'r rheol ddilysu rydych chi am ei defnyddio, ac yna pwyswch Ctrl + C i gopïo.

2. Yna, dewiswch y celloedd rydych chi am eu dilysu, i ddewis nifer o gelloedd nad ydyn nhw'n gyfagos, pwyso a dal y Ctrl allwedd wrth ddewis y celloedd.

3. Ac yna, de-gliciwch y dewis, dewiswch Gludo Arbennig opsiwn, gweler y screenshot:

4. Yn y Gludo Arbennig blwch deialog, dewiswch Dilysu opsiwn, gweler y screenshot:

5. Cliciwch OK botwm, nawr mae'r rheol ddilysu yn cael ei chopïo i'r celloedd newydd.

8. Sut i ddefnyddio dilysiad data i gylchredeg cofnodion annilys yn Excel?

Weithiau, efallai y bydd angen i chi greu rheolau dilysu data ar gyfer data sy'n bodoli, yn yr achos hwn, gall rhywfaint o ddata annilys ymddangos yn yr ystod o gelloedd. Sut i wirio'r data annilys a'u haddasu? Yn Excel, gallwch ddefnyddio'r Cylch Data Annilys nodwedd i dynnu sylw at y data annilys gyda chylch coch.

I gylchredeg y data annilys sydd ei angen arnoch, dylech gymhwyso'r Dilysu Data nodwedd i osod rheol ar gyfer yr ystod ddata. Gwnewch y camau canlynol:

1. Dewiswch yr ystod ddata rydych chi am ei chylchredeg data annilys.

2. Yna, cliciwch Dyddiad > Dilysu Data > Dilysu Data, Yn y Dilysu Data blwch deialog, gosodwch y rheol ddilysu yn ôl eich angen, er enghraifft, yma, byddaf yn dilysu'r gwerthoedd sy'n fwy na 500, gweler y screenshot:

3. Yna, cliciwch OK i gau'r blwch deialog. Ar ôl gosod y rheol dilysu data, cliciwch Dyddiad > Dilysu Data > Cylch Data Annilys, yna mae'r holl werthoedd annilys sy'n llai na 500 wedi'u cylchredeg â hirgrwn coch. Gweler sgrinluniau:

Nodiadau:

  • 1. Cyn gynted ag y byddwch yn cywiro data annilys, bydd y cylch coch yn mynd yn awtomatig.
  • 2. Mae hyn yn Cylch Data Annilys nodwedd yn unig all gylchu 255 o gelloedd ar y mwyaf. Pan arbedwch y llyfr gwaith cyfredol, bydd yr holl gylchoedd coch yn cael eu tynnu.
  • 3. Ni ellir argraffu'r cylchoedd hyn.
  • 4. Gallwch hefyd gael gwared ar y cylchoedd coch trwy glicio Dyddiad > Dilysu Data > Cylchoedd Dilysu Clir.

9. Sut i gael gwared ar ddilysu data yn Excel?

I gael gwared ar y rheolau dilysu data o ystod o gelloedd, taflen waith gyfredol neu'r llyfr gwaith cyfan, gall y dulliau canlynol wneud ffafr i chi.

 Dileu dilysu data mewn ystod ddethol gyda swyddogaeth dilysu data

1. Dewiswch y celloedd gyda dilysiad data rydych chi am eu tynnu.

2. Yna cliciwch Dyddiad > Dilysu Data > Dilysu Data, yn y blwch deialog popped allan, o dan y Gosodiadau tab, cliciwch Clirio'r holl botwm, gweler y screenshot:

3. Yna, cliciwch OK botwm i gau'r blwch deialog hwn. Ac mae'r rheol dilysu data sy'n berthnasol i'r ystod a ddewiswyd wedi'i dileu ar unwaith.

Awgrymiadau: I gael gwared ar y dilysiad data o'r daflen waith gyfredol, dewiswch y ddalen gyfan yn gyntaf, ac yna cymhwyswch y camau uchod.

 Tynnwch ddilysiad data mewn ystod ddethol gyda nodwedd ddefnyddiol

Os oes gennych Kutools ar gyfer Excel, ei Cyfyngiadau Dilysu Data Clir gall nodwedd hefyd helpu i gael gwared ar y rheolau dilysu data o ystod ddethol neu'r daflen waith gyfan.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel hyn:

1. Dewiswch yr ystod o gelloedd neu mae'r daflen waith gyfan yn cynnwys y dilysiad data rydych chi am ei dynnu.

2. Yna, cliciwch Kutools > Atal Teipio > Cyfyngiadau Dilysu Data Clir, gweler y screenshot:

3. Yn y blwch prydlon popped allan, cliciwch OK, ac mae'r rheol dilysu data wedi'i chlirio yn ôl yr angen.

 Tynnu dilysiad data o'r holl daflenni gwaith gyda chod VBA

I gael gwared ar y rheolau dilysu data o'r llyfr gwaith cyfan, bydd y dulliau uchod yn cymryd llawer o amser os oes llawer o daflenni gwaith, yma, gall y cod isod eich helpu chi i ddelio â'r dasg hon yn gyflym.

1. Daliwch i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Yna, Cliciwch Mewnosod > Modiwlau, a gludwch y macro canlynol yn y Modiwlau ffenestr.

Cod VBA: Dileu rheolau dilysu data ym mhob taflen waith:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Yna, pwyswch F5 allwedd i redeg y cod hwn, ac mae'r holl reolau dilysu data wedi'u dileu o'r llyfr gwaith cyfan ar unwaith.

 

  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau a Cadw Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi Dyblyg a Swm / Cyfartaledd... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Fformiwlâu Hoff a Mewnosod yn Gyflym, Meysydd, Siartiau a Lluniau; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Grwpio Tabl Pivot yn ôl rhif wythnos, diwrnod o'r wythnos a mwy ... Dangos Celloedd Datgloi, wedi'u Cloi yn ôl gwahanol liwiau; Amlygu Celloedd sydd â Fformiwla / Enw...
tab kte 201905
  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod officetab

 

Comments (1)
No ratings yet. Be the first to rate!
Ghomergher
Ghomergher
This comment was minimized by the moderator on the site
Thanks For Sharing this Great Information. I loved it.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations