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.
-
Qhib ib daim ntawv ua haujlwm dawb paug hauv Excel thiab xaiv cells D1 rau J3.
-
Xaiv Format hauv pawg Cells ntawm Home tab.
-
Xaiv Raw Height thiab nkag mus 24.75. Xaiv Kab Dav thiab sau 5.
-
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.
-
Xaiv Wingdings hauv Font daim ntawv thiab teeb tsa Font Size rau36.
-
Select cells D1 to F3.
-
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.
-
Select cells H1 to J3.
-
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.
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.
-
Xaiv cell E5 hauv qab xiav tuag.
-
Select the Formulas tab.
-
Xaiv Math & Trig los ntawm pawg tsev qiv ntawv Function.
-
Xaiv RANDBETWEEN hauv daim ntawv los nqa lub thawv muaj nuj nqi.
-
Sau 1hauvhauv qab teb thiab6nyob rau hauv tus Top teb.
-
Xaiv OK. Tus lej random ntawm 1 txog 6 yuav tshwm sim hauv cell E5.
-
Tau tib lub mis rau hauv cell I5. Tus lej random ntawm 1 txog 6 yuav tshwm sim hauv cell I5.
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.
-
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", "")
-
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", "")
-
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", "")
-
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.
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 :
- Select cells E5 to I5.
- Ntawm Home tab, hloov font xim ntawm cov hlwb kom phim xim tom qab, uas, qhov no, yog dawb.