Yuav ua li cas los tsim ib khub Dov tsuav hauv Excel

Cov txheej txheem:

Yuav ua li cas los tsim ib khub Dov tsuav hauv Excel
Yuav ua li cas los tsim ib khub Dov tsuav hauv Excel
Anonim

Siv ob peb lub luag haujlwm, koj tuaj yeem tsim cov tsuav rau cov menyuam uas ua duab qhia ob peb lub tsuav ntawm koj daim ntawv ua haujlwm.

Txhua qhov tuag qhia tus lej random tsim los ntawm RANDBETWEEN muaj nuj nqi. Cov dots ntawm lub ntsej muag tuag siv Wingdings font, thiab kev sib xyaw ua ke ntawm THIAB, IF, thiab LOSSIS ua haujlwm tswj thaum cov dots tshwm hauv txhua lub xovtooj.

Nyob ntawm tus lej random uas RANDBETWEEN muaj nuj nqi tsim, cov dots yuav tshwm sim hauv cov hlwb tsim nyog hauv daim ntawv ua haujlwm. Cov tsuav tuaj yeem rov dov rov ua dua los ntawm kev suav cov ntawv ua haujlwm.

Cov lus qhia no siv rau Excel rau Microsoft 365, Excel 2019, 2016, 2013, thiab 2010.

Tsim lub tsuav

Ua ntej, koj yuav tsum siv ob peb txoj hauv kev los tso saib cov tsuav ntawm daim ntawv ua haujlwm Excel. Cov no suav nrog kev hloov pauv qhov loj ntawm lub xov tooj ntawm tes thiab hloov kho ntawm tes, nrog rau hom font thiab qhov loj me.

  1. Qhib ib daim ntawv ua haujlwm dawb paug hauv Excel thiab xaiv cells D1 rau J3.

    Image
    Image
  2. Xaiv Format hauv pawg Cells ntawm Home tab.

    Image
    Image
  3. Xaiv Raw Height thiab nkag mus 24.75. Xaiv Kab Dav thiab sau 5.

    Image
    Image
  4. Xaiv Format Cells nyob hauv qab ntawm Format drop-down menu thiab mus rau Alignmenttab. Teem lub Kab rov tav Cell Alignment thiabVertical Cell Alignment rau center thiab xaiv OK.

    Image
    Image
  5. Xaiv Wingdings hauv Font daim ntawv thiab teeb tsa Font Size rau36.

    Image
    Image
  6. Select cells D1 to F3.

    Image
    Image
  7. Right-click the selected cells and xaiv Format Cells. Mus rau qhov Fill tab thiab xaiv Blue rau xim Background. Xaiv OK txhawm rau siv cov xim thiab kaw lub dialog box.

    Image
    Image
  8. Select cells H1 to J3.

    Image
    Image
  9. Right-click the selected cells and xaiv Format Cells. Mus rau qhov Fill tab thiab xaiv Red rau xim Background. Xaiv OK txhawm rau siv cov xim thiab kaw lub dialog box.

    Image
    Image

Ntxiv rau RANDBETWEEN Function

Thaum koj ua tiav formatting cov hlwb, koj yuav tsum nkag mus rau RANDBETWEEN ua haujlwm hauv ob lub hlwb los tsim cov lej random qhia ntawm cov tsuav ua dots.

  1. Xaiv cell E5 hauv qab xiav tuag.

    Image
    Image
  2. Select the Formulas tab.

    Image
    Image
  3. Xaiv Math & Trig los ntawm pawg tsev qiv ntawv Function.

    Image
    Image
  4. Xaiv RANDBETWEEN hauv daim ntawv los nqa lub thawv muaj nuj nqi.

    Image
    Image
  5. Sau 1hauvhauv qab teb thiab6nyob rau hauv tus Top teb.

    Image
    Image
  6. Xaiv OK. Tus lej random ntawm 1 txog 6 yuav tshwm sim hauv cell E5.

    Image
    Image
  7. Tau tib lub mis rau hauv cell I5. Tus lej random ntawm 1 txog 6 yuav tshwm sim hauv cell I5.

    Image
    Image

Cov Haujlwm Tom Qab Dots

txhawm rau tsim kom muaj cov lej raug, koj yuav tsum siv IF muaj nuj nqi hauv hlwb D1 txog D3, E2, thiab F1 rau F3 hauv thawj cov tsuav, thiab hauv hlwb H1 txog H3, I2, thiab J1 rau J3 hauv qhov thib ob. Qee lub hlwb kuj suav nrog AND lossis OR ua haujlwm.

Hloov lossis muab cov qauv hauv qab no tso rau hauv cov qauv bar, tsis ncaj qha rau ntawm tes, vim qhov no yuav ua rau lub cell formatting.

  1. In cells D1 thiab F1, ntaus cov haujlwm hauv qab no:

    =IF(AND(E5>=2, E5<=6), "l", "")

    Qhov kev sim ua haujlwm no kom pom tias tus lej random hauv cell E5 yog nyob nruab nrab ntawm 2 thiab 6; yog tias muaj, qhov ua haujlwm tso tus lej L hauv cells D1 thiab F1, uas yog qhov teev hauv Wingdings font. Yog tias tsis yog, nws tawm ntawm cov hlwb tsis muaj. Txhawm rau kom tau txais cov txiaj ntsig zoo ib yam rau qhov thib ob tuag, ntaus cov haujlwm hauv qab no rau cells H1 thiab J1:

    =IF(AND(I5>=2, I5<=6), "l", "")

    Image
    Image
  2. In cells D2 thiab F2, ntaus cov haujlwm hauv qab no:

    =IF(E5=6, "l", "")

    Qhov kev ntsuas no ua kom pom tias tus lej random hauv cell E5 yog sib npaug rau 6; Yog hais tias yog li ntawd, nws tso ib qho chaw nyob rau hauv cells D2 thiab F2Yog tias tsis yog, nws tawm ntawm lub xov tooj ntawm tes. Txhawm rau kom tau txais cov txiaj ntsig zoo ib yam rau qhov thib ob tuag, ntaus cov haujlwm hauv qab no rau hauv cells H2 thiab J2:

    =IF(I5=6, "l", "")

    Image
    Image
  3. In cells D3 thiab F3, ntaus cov haujlwm hauv qab no:

    =IF(AND(E5>=4, E5<=6), "l", "")

    Qhov kev ntsuas no ua kom pom tias tus lej random hauv cell E5 yog nyob nruab nrab ntawm 4 thiab 6; Yog hais tias yog li ntawd, nws tso ib lub dot rau hauv cells D3 thiab F3 Yog tsis yog, nws tawm hauv lub hlwb. Txhawm rau kom tau txais cov txiaj ntsig zoo ib yam rau qhov thib ob tuag, ntaus cov haujlwm hauv qab no rau hauv cov hlwb H3 thiab J3:

    =IF(AND(I5>=4, I5<=6), "l", "")

    Image
    Image
  4. In cell E2, ntaus cov haujlwm hauv qab no:

    =IF(OR(E5=1, E5=3, E5=5), "l", "")

    Qhov kev ntsuas no ua kom pom tias tus lej random hauv cell E5 yog sib npaug rau 1, 3, lossis 5; Yog tias muaj, nws tso tus "l" hauv cell E2 . Yog tias tsis yog, nws tawm ntawm lub xov tooj ntawm tes. Txhawm rau kom tau txais cov txiaj ntsig zoo ib yam rau qhov thib ob tuag, ntaus cov haujlwm hauv qab no rau cells I2:

    =IF(OR(I5=1, I5=3, I5=5), "l", "")

Yuav lub tsuav

Yuav yob cov tsuav, rov xam koj daim ntawv ua haujlwm. Koj tuaj yeem xaiv Xaiv tam sim no hauv qab Formula ribbon (lub cim zoo li lub laij lej), lossis koj tuaj yeem nias lub F9tus yuam sij hauv koj siv lub desktop version ntawm Excel.

Image
Image

Rau suav ua rau RANDBETWEEN ua haujlwm hauv cells E5 thiab I5 los tsim lwm tus lej random nruab nrab ntawm 1 thiab 6.

Hiding the RANDBETWEEN Function

Thaum cov tsuav ua tiav thiab tag nrho cov haujlwm tau raug sim los xyuas kom meej tias lawv ua haujlwm raug, koj tuaj yeem nkaum qhov RANDBETWEEN ua haujlwm hauv cells E5thiab I5 :

  1. Select cells E5 to I5.
  2. Ntawm Home tab, hloov font xim ntawm cov hlwb kom phim xim tom qab, uas, qhov no, yog dawb.

Pom zoo: