ตั้งค่าไฟล์... :
column A = user1 user2 ... user120. [starting at A1]
column B,C,D = "choice1 choice2 choice3"
column G,H = "pick 2 from the 3 choices"
column F is used for G & H determination.
ส่วนเรื่องค่านิยมนั้น
A1 = user1
A2 = user2 ...
และสูตร:
B1 ---> =RANDBETWEEN(1,10)
C1 ---> =INDEX(IF(B1=1,{2,3,4,5,6,7,8,9,10},IF(B1=2,{1,3,4,5,6,7,8,9,10},IF(B1=3,{1,2,4,5,6,7,8,9,10},IF(B1=4,{1,2,3,5,6,7,8,9,10},IF(B1=5,{1,2,3,4,6,7,8,9,10},IF(B1=6,{1,2,3,4,5,7,8,9,10},IF(B1=7,{1,2,3,4,5,6,8,9,10},IF(B1=8,{1,2,3,4,5,6,7,9,10},IF(B1=9,{1,2,3,4,5,6,7,8,10},IF(B1=10,{1,2,3,4,5,6,7,8,9},"ERROR")))))))))),RANDBETWEEN(1,9))
D1 ---> =INDEX(IF(OR(AND(B1=1,C1=2),AND(B1=2,C1=1)),{3,4,5,6,7,8,9,10},IF(OR(AND(B1=1,C1=3),AND(B1=3,C1=1)),{2,4,5,6,7,8,9,10},IF(OR(AND(B1=1,C1=4),AND(B1=4,C1=1)),{2,3,5,6,7,8,9,10},IF(OR(AND(B1=1,C1=5),AND(B1=5,C1=1)),{2,3,4,6,7,8,9,10},IF(OR(AND(B1=1,C1=6),AND(B1=6,C1=1)),{2,3,4,5,7,8,9,10},IF(OR(AND(B1=1,C1=7),AND(B1=7,C1=1)),{2,3,4,5,6,8,9,10},IF(OR(AND(B1=1,C1=8),AND(B1=8,C1=1)),{2,3,4,5,6,7,9,10},IF(OR(AND(B1=1,C1=9),AND(B1=9,C1=1)),{2,3,4,5,6,7,8,10},IF(OR(AND(B1=1,C1=10),AND(B1=10,C1=1)),{2,3,4,5,6,7,8,9},IF(OR(AND(B1=2,C1=3),AND(B1=3,C1=2)),{1,4,5,6,7,8,9,10},IF(OR(AND(B1=2,C1=4),AND(B1=4,C1=2)),{1,3,5,6,7,8,9,10},IF(OR(AND(B1=2,C1=5),AND(B1=5,C1=2)),{1,3,4,6,7,8,9,10},IF(OR(AND(B1=2,C1=6),AND(B1=6,C1=2)),{1,3,4,5,7,8,9,10},IF(OR(AND(B1=2,C1=7),AND(B1=7,C1=2)),{1,3,4,5,6,8,9,10},IF(OR(AND(B1=2,C1=8),AND(B1=8,C1=2)),{1,3,4,5,6,7,9,10},IF(OR(AND(B1=2,C1=9),AND(B1=9,C1=2)),{1,3,4,5,6,7,8,10},IF(OR(AND(B1=2,C1=10),AND(B1=10,C1=2)),{1,3,4,5,6,7,8,9},IF(OR(AND(B1=3,C1=4),AND(B1=4,C1=3)),{1,2,5,6,7,8,9,10},IF(OR(AND(B1=3,C1=5),AND(B1=5,C1=3)),{1,2,4,6,7,8,9,10},IF(OR(AND(B1=3,C1=6),AND(B1=6,C1=3)),{1,2,4,5,7,8,9,10},IF(OR(AND(B1=3,C1=7),AND(B1=7,C1=3)),{1,2,4,5,6,8,9,10},IF(OR(AND(B1=3,C1=8),AND(B1=8,C1=3)),{1,2,4,5,6,7,9,10},IF(OR(AND(B1=3,C1=9),AND(B1=9,C1=3)),{1,2,4,5,6,7,8,10},IF(OR(AND(B1=3,C1=10),AND(B1=10,C1=3)),{1,2,4,5,6,7,8,9},IF(OR(AND(B1=4,C1=5),AND(B1=5,C1=4)),{1,2,3,6,7,8,9,10},IF(OR(AND(B1=4,C1=6),AND(B1=6,C1=4)),{1,2,3,5,7,8,9,10},IF(OR(AND(B1=4,C1=7),AND(B1=7,C1=4)),{1,2,3,5,6,8,9,10},IF(OR(AND(B1=4,C1=8),AND(B1=8,C1=4)),{1,2,3,5,6,7,9,10},IF(OR(AND(B1=4,C1=9),AND(B1=9,C1=4)),{1,2,3,5,6,7,8,10},IF(OR(AND(B1=4,C1=10),AND(B1=10,C1=4)),{1,2,3,5,6,7,8,9},IF(OR(AND(B1=5,C1=6),AND(B1=6,C1=5)),{1,2,3,4,7,8,9,10},IF(OR(AND(B1=5,C1=7),AND(B1=7,C1=5)),{1,2,3,4,6,8,9,10},IF(OR(AND(B1=5,C1=8),AND(B1=8,C1=5)),{1,2,3,4,6,7,9,10},IF(OR(AND(B1=5,C1=9),AND(B1=9,C1=5)),{1,2,3,4,6,7,8,10},IF(OR(AND(B1=5,C1=10),AND(B1=10,C1=5)),{1,2,3,4,6,7,8,9},IF(OR(AND(B1=6,C1=7),AND(B1=7,C1=6)),{1,2,3,4,5,8,9,10},IF(OR(AND(B1=6,C1=8),AND(B1=8,C1=6)),{1,2,3,4,5,7,9,10},IF(OR(AND(B1=6,C1=9),AND(B1=9,C1=6)),{1,2,3,4,5,7,8,10},IF(OR(AND(B1=6,C1=10),AND(B1=10,C1=6)),{1,2,3,4,5,7,8,9},IF(OR(AND(B1=7,C1=8),AND(B1=8,C1=7)),{1,2,3,4,5,6,9,10},IF(OR(AND(B1=7,C1=9),AND(B1=9,C1=7)),{1,2,3,4,5,6,8,10},IF(OR(AND(B1=7,C1=10),AND(B1=10,C1=7)),{1,2,3,4,5,6,8,9},IF(OR(AND(B1=8,C1=9),AND(B1=9,C1=8)),{1,2,3,4,5,6,7,10},IF(OR(AND(B1=8,C1=10),AND(B1=10,C1=8)),{1,2,3,4,5,6,7,9},IF(OR(AND(B1=9,C1=10),AND(B1=10,C1=9)),{1,2,3,4,5,6,7,8},"error"))))))))))))))))))))))))))))))))))))))))))))),RANDBETWEEN(1,8))
F1 ---> =RANDBETWEEN(1,3)
G1 ---> =IF(F1=1,C1,B1)
H1 ---> =IF(F1<>3,D1,C1)
หวังว่านี่จะช่วยได้ .. (:
person
p._phidot_
schedule
19.08.2018