Không thể thêm hàm này vào name manager (1 người xem)

[votrongnghia]

CÓ AI GIÚP MÌNH CÁCH NÀO RÚT GỌN HÀM NÀY LẠI VỚI .
THANKS

=IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$21,G3<=[VTN.xlsx]COIN!$B$21),G3+[VTN.xlsx]COIN!$C$21,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$22,G3<=[VTN.xlsx]COIN!$B$22),G3+[VTN.xlsx]COIN!$C$22,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$23,G3<=[VTN.xlsx]COIN!$B$23),G3+[VTN.xlsx]COIN!$C$23,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$24,G3<=[VTN.xlsx]COIN!$B$24),G3+[VTN.xlsx]COIN!$C$24,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$25,G3<=[VTN.xlsx]COIN!$B$25),G3+[VTN.xlsx]COIN!$C$25,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$26,G3<=[VTN.xlsx]COIN!$B$26),G3+[VTN.xlsx]COIN!$C$26,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$27,G3<=[VTN.xlsx]COIN!$B$27),G3+[VTN.xlsx]COIN!$C$27,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$28,G3<=[VTN.xlsx]COIN!$B$28),G3+[VTN.xlsx]COIN!$C$28,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$29,G3<=[VTN.xlsx]COIN!$B$29),G3+[VTN.xlsx]COIN!$C$29,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$30,G3<=[VTN.xlsx]COIN!$B$30),G3+[VTN.xlsx]COIN!$C$30,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$31,G3<=[VTN.xlsx]COIN!$B$31),G3+[VTN.xlsx]COIN!$C$31,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$32,G3<=[VTN.xlsx]COIN!$B$32),G3+[VTN.xlsx]COIN!$C$32,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$33,G3<=[VTN.xlsx]COIN!$B$33),G3+[VTN.xlsx]COIN!$C$33,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$34,G3<=[VTN.xlsx]COIN!$B$34),G3+[VTN.xlsx]COIN!$C$34,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$35,G3<=[VTN.xlsx]COIN!$B$35),G3+[VTN.xlsx]COIN!$C$35,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$36,G3<=[VTN.xlsx]COIN!$B$36),G3+[VTN.xlsx]COIN!$C$36,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$37,G3<=[VTN.xlsx]COIN!$B$37),G3+[VTN.xlsx]COIN!$C$37,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$38,G3<=[VTN.xlsx]COIN!$B$38),G3+[VTN.xlsx]COIN!$C$38,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$39,G3<=[VTN.xlsx]COIN!$B$39),G3+[VTN.xlsx]COIN!$C$39,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$40,G3<=[VTN.xlsx]COIN!$B$40),G3+[VTN.xlsx]COIN!$C$40,IF(AND([VTN.xlsx]COIN!$A$2<=G3,G3<=[VTN.xlsx]COIN!$B$2),G3+[VTN.xlsx]COIN!$C$2,IF(AND([VTN.xlsx]COIN!$A$3<=G3,G3<=[VTN.xlsx]COIN!$B$3),G3+[VTN.xlsx]COIN!$C$3,IF(AND([VTN.xlsx]COIN!$A$4<=G3,G3<=[VTN.xlsx]COIN!$B$4),G3+[VTN.xlsx]COIN!$C$4,IF(AND([VTN.xlsx]COIN!$A$5<=G3,G3<=[VTN.xlsx]COIN!$B$5),G3+[VTN.xlsx]COIN!$C$5,IF(AND([VTN.xlsx]COIN!$A$6<=G3,G3<=[VTN.xlsx]COIN!$B$6),G3+[VTN.xlsx]COIN!$C$6,IF(AND([VTN.xlsx]COIN!$A$7<=G3,G3<=[VTN.xlsx]COIN!$B$7),G3+[VTN.xlsx]COIN!$C$7,IF(AND([VTN.xlsx]COIN!$A$8<=G3,G3<=[VTN.xlsx]COIN!$B$8),G3+[VTN.xlsx]COIN!$C$8,IF(AND([VTN.xlsx]COIN!$A$9<=G3,G3<=[VTN.xlsx]COIN!$B$9),G3+[VTN.xlsx]COIN!$C$9,IF(AND([VTN.xlsx]COIN!$A$10<=G3,G3<=[VTN.xlsx]COIN!$B$10),G3+[VTN.xlsx]COIN!$C$10,IF(AND([VTN.xlsx]COIN!$A$11<=G3,G3<=[VTN.xlsx]COIN!$B$11),G3+[VTN.xlsx]COIN!$C$11,IF(AND([VTN.xlsx]COIN!$A$12<=G3,G3<=[VTN.xlsx]COIN!$B$12),G3+[VTN.xlsx]COIN!$C$12,IF(AND([VTN.xlsx]COIN!$A$13<=G3,G3<=[VTN.xlsx]COIN!$B$13),G3+[VTN.xlsx]COIN!$C$13,IF(AND([VTN.xlsx]COIN!$A$14<=G3,G3<=[VTN.xlsx]COIN!$B$14),G3+[VTN.xlsx]COIN!$C$14,IF(AND([VTN.xlsx]COIN!$A$15<=G3,G3<=[VTN.xlsx]COIN!$B$15),G3+[VTN.xlsx]COIN!$C$15,IF(AND([VTN.xlsx]COIN!$A$16<=G3,G3<=[VTN.xlsx]COIN!$B$16),G3+[VTN.xlsx]COIN!$C$16,IF(AND([VTN.xlsx]COIN!$A$17<=G3,G3<=[VTN.xlsx]COIN!$B$17),G3+[VTN.xlsx]COIN!$C$17,IF(AND([VTN.xlsx]COIN!$A$18<=G3,G3<=[VTN.xlsx]COIN!$B$18),G3+[VTN.xlsx]COIN!$C$18,IF(AND([VTN.xlsx]COIN!$A$19<=G3,G3<=[VTN.xlsx]COIN!$B$19),G3+[VTN.xlsx]COIN!$C$19,IF(AND([VTN.xlsx]COIN!$A$20<=G3,G3<=[VTN.xlsx]COIN!$B$20),G3+[VTN.xlsx]COIN!$C$20,G3)))))))))))))))))))))))))))))))))))))))

 

[huuthang_bd]

CÓ AI GIÚP MÌNH CÁCH NÀO RÚT GỌN HÀM NÀY LẠI VỚI .
THANKS

=IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$21,G3<=[VTN.xlsx]COIN!$B$21),G3+[VTN.xlsx]COIN!$C$21,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$22,G3<=[VTN.xlsx]COIN!$B$22),G3+[VTN.xlsx]COIN!$C$22,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$23,G3<=[VTN.xlsx]COIN!$B$23),G3+[VTN.xlsx]COIN!$C$23,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$24,G3<=[VTN.xlsx]COIN!$B$24),G3+[VTN.xlsx]COIN!$C$24,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$25,G3<=[VTN.xlsx]COIN!$B$25),G3+[VTN.xlsx]COIN!$C$25,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$26,G3<=[VTN.xlsx]COIN!$B$26),G3+[VTN.xlsx]COIN!$C$26,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$27,G3<=[VTN.xlsx]COIN!$B$27),G3+[VTN.xlsx]COIN!$C$27,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$28,G3<=[VTN.xlsx]COIN!$B$28),G3+[VTN.xlsx]COIN!$C$28,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$29,G3<=[VTN.xlsx]COIN!$B$29),G3+[VTN.xlsx]COIN!$C$29,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$30,G3<=[VTN.xlsx]COIN!$B$30),G3+[VTN.xlsx]COIN!$C$30,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$31,G3<=[VTN.xlsx]COIN!$B$31),G3+[VTN.xlsx]COIN!$C$31,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$32,G3<=[VTN.xlsx]COIN!$B$32),G3+[VTN.xlsx]COIN!$C$32,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$33,G3<=[VTN.xlsx]COIN!$B$33),G3+[VTN.xlsx]COIN!$C$33,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$34,G3<=[VTN.xlsx]COIN!$B$34),G3+[VTN.xlsx]COIN!$C$34,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$35,G3<=[VTN.xlsx]COIN!$B$35),G3+[VTN.xlsx]COIN!$C$35,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$36,G3<=[VTN.xlsx]COIN!$B$36),G3+[VTN.xlsx]COIN!$C$36,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$37,G3<=[VTN.xlsx]COIN!$B$37),G3+[VTN.xlsx]COIN!$C$37,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$38,G3<=[VTN.xlsx]COIN!$B$38),G3+[VTN.xlsx]COIN!$C$38,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$39,G3<=[VTN.xlsx]COIN!$B$39),G3+[VTN.xlsx]COIN!$C$39,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$40,G3<=[VTN.xlsx]COIN!$B$40),G3+[VTN.xlsx]COIN!$C$40,IF(AND([VTN.xlsx]COIN!$A$2<=G3,G3<=[VTN.xlsx]COIN!$B$2),G3+[VTN.xlsx]COIN!$C$2,IF(AND([VTN.xlsx]COIN!$A$3<=G3,G3<=[VTN.xlsx]COIN!$B$3),G3+[VTN.xlsx]COIN!$C$3,IF(AND([VTN.xlsx]COIN!$A$4<=G3,G3<=[VTN.xlsx]COIN!$B$4),G3+[VTN.xlsx]COIN!$C$4,IF(AND([VTN.xlsx]COIN!$A$5<=G3,G3<=[VTN.xlsx]COIN!$B$5),G3+[VTN.xlsx]COIN!$C$5,IF(AND([VTN.xlsx]COIN!$A$6<=G3,G3<=[VTN.xlsx]COIN!$B$6),G3+[VTN.xlsx]COIN!$C$6,IF(AND([VTN.xlsx]COIN!$A$7<=G3,G3<=[VTN.xlsx]COIN!$B$7),G3+[VTN.xlsx]COIN!$C$7,IF(AND([VTN.xlsx]COIN!$A$8<=G3,G3<=[VTN.xlsx]COIN!$B$8),G3+[VTN.xlsx]COIN!$C$8,IF(AND([VTN.xlsx]COIN!$A$9<=G3,G3<=[VTN.xlsx]COIN!$B$9),G3+[VTN.xlsx]COIN!$C$9,IF(AND([VTN.xlsx]COIN!$A$10<=G3,G3<=[VTN.xlsx]COIN!$B$10),G3+[VTN.xlsx]COIN!$C$10,IF(AND([VTN.xlsx]COIN!$A$11<=G3,G3<=[VTN.xlsx]COIN!$B$11),G3+[VTN.xlsx]COIN!$C$11,IF(AND([VTN.xlsx]COIN!$A$12<=G3,G3<=[VTN.xlsx]COIN!$B$12),G3+[VTN.xlsx]COIN!$C$12,IF(AND([VTN.xlsx]COIN!$A$13<=G3,G3<=[VTN.xlsx]COIN!$B$13),G3+[VTN.xlsx]COIN!$C$13,IF(AND([VTN.xlsx]COIN!$A$14<=G3,G3<=[VTN.xlsx]COIN!$B$14),G3+[VTN.xlsx]COIN!$C$14,IF(AND([VTN.xlsx]COIN!$A$15<=G3,G3<=[VTN.xlsx]COIN!$B$15),G3+[VTN.xlsx]COIN!$C$15,IF(AND([VTN.xlsx]COIN!$A$16<=G3,G3<=[VTN.xlsx]COIN!$B$16),G3+[VTN.xlsx]COIN!$C$16,IF(AND([VTN.xlsx]COIN!$A$17<=G3,G3<=[VTN.xlsx]COIN!$B$17),G3+[VTN.xlsx]COIN!$C$17,IF(AND([VTN.xlsx]COIN!$A$18<=G3,G3<=[VTN.xlsx]COIN!$B$18),G3+[VTN.xlsx]COIN!$C$18,IF(AND([VTN.xlsx]COIN!$A$19<=G3,G3<=[VTN.xlsx]COIN!$B$19),G3+[VTN.xlsx]COIN!$C$19,IF(AND([VTN.xlsx]COIN!$A$20<=G3,G3<=[VTN.xlsx]COIN!$B$20),G3+[VTN.xlsx]COIN!$C$20,G3)))))))))))))))))))))))))))))))))))))))

Bạn phải đưa cái file lên và giải thích công thức này dùng để làm gì chứ dịch cái công thức này ra chắc xỉu quá.
Với lại bạn lập topic này ở đây là không đúng chỗ rồi.

 

[votrongnghia]

Không cần mình chì cần tạo 1 cái hàm và đưa công thức đó vào .

Chủ yếu chó nó ngắn lại mà công thức không bị thay đổi

 

[ptm0412]

Một công thức (không sử dụng hàm macro4) muốn đưa vào Name, trước hết nó phải tính được trên bảng tính.
Bạn đã thử tính trên 1 ô nào đó của bảng tính với công thức này chưa?

=IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$21,G3<=[VTN.xlsx]COIN!$B$21),G3+[VTN.xlsx]COIN!$C$21,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$22,G3<=[VTN.xlsx]COIN!$B$22),G3+[VTN.xlsx]COIN!$C$22,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$23,G3<=[VTN.xlsx]COIN!$B$23),G3+[VTN.xlsx]COIN!$C$23,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$24,G3<=[VTN.xlsx]COIN!$B$24),G3+[VTN.xlsx]COIN!$C$24,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$25,G3<=[VTN.xlsx]COIN!$B$25),G3+[VTN.xlsx]COIN!$C$25,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$26,G3<=[VTN.xlsx]COIN!$B$26),G3+[VTN.xlsx]COIN!$C$26,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$27,G3<=[VTN.xlsx]COIN!$B$27),G3+[VTN.xlsx]COIN!$C$27,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$28,G3<=[VTN.xlsx]COIN!$B$28),G3+[VTN.xlsx]COIN!$C$28,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$29,G3<=[VTN.xlsx]COIN!$B$29),G3+[VTN.xlsx]COIN!$C$29,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$30,G3<=[VTN.xlsx]COIN!$B$30),G3+[VTN.xlsx]COIN!$C$30,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$31,G3<=[VTN.xlsx]COIN!$B$31),G3+[VTN.xlsx]COIN!$C$31,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$32,G3<=[VTN.xlsx]COIN!$B$32),G3+[VTN.xlsx]COIN!$C$32,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$33,G3<=[VTN.xlsx]COIN!$B$33),G3+[VTN.xlsx]COIN!$C$33,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$34,G3<=[VTN.xlsx]COIN!$B$34),G3+[VTN.xlsx]COIN!$C$34,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$35,G3<=[VTN.xlsx]COIN!$B$35),G3+[VTN.xlsx]COIN!$C$35,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$36,G3<=[VTN.xlsx]COIN!$B$36),G3+[VTN.xlsx]COIN!$C$36,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$37,G3<=[VTN.xlsx]COIN!$B$37),G3+[VTN.xlsx]COIN!$C$37,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$38,G3<=[VTN.xlsx]COIN!$B$38),G3+[VTN.xlsx]COIN!$C$38,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$39,G3<=[VTN.xlsx]COIN!$B$39),G3+[VTN.xlsx]COIN!$C$39,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$40,G3<=[VTN.xlsx]COIN!$B$40),G3+[VTN.xlsx]COIN!$C$40,IF(AND([VTN.xlsx]COIN!$A$2<=G3,G3<=[VTN.xlsx]COIN!$B$2),G3+[VTN.xlsx]COIN!$C$2,IF(AND([VTN.xlsx]COIN!$A$3<=G3,G3<=[VTN.xlsx]COIN!$B$3),G3+[VTN.xlsx]COIN!$C$3,IF(AND([VTN.xlsx]COIN!$A$4<=G3,G3<=[VTN.xlsx]COIN!$B$4),G3+[VTN.xlsx]COIN!$C$4,IF(AND([VTN.xlsx]COIN!$A$5<=G3,G3<=[VTN.xlsx]COIN!$B$5),G3+[VTN.xlsx]COIN!$C$5,IF(AND([VTN.xlsx]COIN!$A$6<=G3,G3<=[VTN.xlsx]COIN!$B$6),G3+[VTN.xlsx]COIN!$C$6,IF(AND([VTN.xlsx]COIN!$A$7<=G3,G3<=[VTN.xlsx]COIN!$B$7),G3+[VTN.xlsx]COIN!$C$7,IF(AND([VTN.xlsx]COIN!$A$8<=G3,G3<=[VTN.xlsx]COIN!$B$8),G3+[VTN.xlsx]COIN!$C$8,IF(AND([VTN.xlsx]COIN!$A$9<=G3,G3<=[VTN.xlsx]COIN!$B$9),G3+[VTN.xlsx]COIN!$C$9,IF(AND([VTN.xlsx]COIN!$A$10<=G3,G3<=[VTN.xlsx]COIN!$B$10),G3+[VTN.xlsx]COIN!$C$10,IF(AND([VTN.xlsx]COIN!$A$11<=G3,G3<=[VTN.xlsx]COIN!$B$11),G3+[VTN.xlsx]COIN!$C$11,IF(AND([VTN.xlsx]COIN!$A$12<=G3,G3<=[VTN.xlsx]COIN!$B$12),G3+[VTN.xlsx]COIN!$C$12,IF(AND([VTN.xlsx]COIN!$A$13<=G3,G3<=[VTN.xlsx]COIN!$B$13),G3+[VTN.xlsx]COIN!$C$13,IF(AND([VTN.xlsx]COIN!$A$14<=G3,G3<=[VTN.xlsx]COIN!$B$14),G3+[VTN.xlsx]COIN!$C$14,IF(AND([VTN.xlsx]COIN!$A$15<=G3,G3<=[VTN.xlsx]COIN!$B$15),G3+[VTN.xlsx]COIN!$C$15,IF(AND([VTN.xlsx]COIN!$A$16<=G3,G3<=[VTN.xlsx]COIN!$B$16),G3+[VTN.xlsx]COIN!$C$16,IF(AND([VTN.xlsx]COIN!$A$17<=G3,G3<=[VTN.xlsx]COIN!$B$17),G3+[VTN.xlsx]COIN!$C$17,IF(AND([VTN.xlsx]COIN!$A$18<=G3,G3<=[VTN.xlsx]COIN!$B$18),G3+[VTN.xlsx]COIN!$C$18,IF(AND([VTN.xlsx]COIN!$A$19<=G3,G3<=[VTN.xlsx]COIN!$B$19),G3+[VTN.xlsx]COIN!$C$19,IF(AND([VTN.xlsx]COIN!$A$20<=G3,G3<=[VTN.xlsx]COIN!$B$20),G3+[VTN.xlsx]COIN!$C$20,G3)))))))))))))))))))))))))))))))))))))))

Một điều chắc chắn với công thức này là sẽ không tính được trên bảng tính. Thậm chí gõ xong công thức, enter nó đã không cho rồi.

 

[votrongnghia]

Vâng đã tính rồi và thành công

 

[votrongnghia]

Vì ký tự nhiều hơn manager name cho phép nên đang tìm cách khác

 

[TranThanhPhong]

Vì ký tự nhiều hơn manager name cho phép nên đang tìm cách khác

Bạn bỏ [VTN.xlsx]COIN! khỏi công thức là sử dụng được.

TTP.

 

[votrongnghia]

Cái đó để liên kết file excel khác bỏ thì sao được

 

[vodoi2x]

CÓ AI GIÚP MÌNH CÁCH NÀO RÚT GỌN HÀM NÀY LẠI VỚI .
THANKS

=IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$21,G3<=[VTN.xlsx]COIN!$B$21),G3+[VTN.xlsx]COIN!$C$21,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$22,G3<=[VTN.xlsx]COIN!$B$22),G3+[VTN.xlsx]COIN!$C$22,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$23,G3<=[VTN.xlsx]COIN!$B$23),G3+[VTN.xlsx]COIN!$C$23,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$24,G3<=[VTN.xlsx]COIN!$B$24),G3+[VTN.xlsx]COIN!$C$24,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$25,G3<=[VTN.xlsx]COIN!$B$25),G3+[VTN.xlsx]COIN!$C$25,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$26,G3<=[VTN.xlsx]COIN!$B$26),G3+[VTN.xlsx]COIN!$C$26,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$27,G3<=[VTN.xlsx]COIN!$B$27),G3+[VTN.xlsx]COIN!$C$27,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$28,G3<=[VTN.xlsx]COIN!$B$28),G3+[VTN.xlsx]COIN!$C$28,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$29,G3<=[VTN.xlsx]COIN!$B$29),G3+[VTN.xlsx]COIN!$C$29,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$30,G3<=[VTN.xlsx]COIN!$B$30),G3+[VTN.xlsx]COIN!$C$30,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$31,G3<=[VTN.xlsx]COIN!$B$31),G3+[VTN.xlsx]COIN!$C$31,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$32,G3<=[VTN.xlsx]COIN!$B$32),G3+[VTN.xlsx]COIN!$C$32,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$33,G3<=[VTN.xlsx]COIN!$B$33),G3+[VTN.xlsx]COIN!$C$33,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$34,G3<=[VTN.xlsx]COIN!$B$34),G3+[VTN.xlsx]COIN!$C$34,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$35,G3<=[VTN.xlsx]COIN!$B$35),G3+[VTN.xlsx]COIN!$C$35,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$36,G3<=[VTN.xlsx]COIN!$B$36),G3+[VTN.xlsx]COIN!$C$36,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$37,G3<=[VTN.xlsx]COIN!$B$37),G3+[VTN.xlsx]COIN!$C$37,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$38,G3<=[VTN.xlsx]COIN!$B$38),G3+[VTN.xlsx]COIN!$C$38,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$39,G3<=[VTN.xlsx]COIN!$B$39),G3+[VTN.xlsx]COIN!$C$39,IF(AND(VALUE(ROUND(MOD(G3,1),1))=[VTN.xlsx]COIN!$A$40,G3<=[VTN.xlsx]COIN!$B$40),G3+[VTN.xlsx]COIN!$C$40,IF(AND([VTN.xlsx]COIN!$A$2<=G3,G3<=[VTN.xlsx]COIN!$B$2),G3+[VTN.xlsx]COIN!$C$2,IF(AND([VTN.xlsx]COIN!$A$3<=G3,G3<=[VTN.xlsx]COIN!$B$3),G3+[VTN.xlsx]COIN!$C$3,IF(AND([VTN.xlsx]COIN!$A$4<=G3,G3<=[VTN.xlsx]COIN!$B$4),G3+[VTN.xlsx]COIN!$C$4,IF(AND([VTN.xlsx]COIN!$A$5<=G3,G3<=[VTN.xlsx]COIN!$B$5),G3+[VTN.xlsx]COIN!$C$5,IF(AND([VTN.xlsx]COIN!$A$6<=G3,G3<=[VTN.xlsx]COIN!$B$6),G3+[VTN.xlsx]COIN!$C$6,IF(AND([VTN.xlsx]COIN!$A$7<=G3,G3<=[VTN.xlsx]COIN!$B$7),G3+[VTN.xlsx]COIN!$C$7,IF(AND([VTN.xlsx]COIN!$A$8<=G3,G3<=[VTN.xlsx]COIN!$B$8),G3+[VTN.xlsx]COIN!$C$8,IF(AND([VTN.xlsx]COIN!$A$9<=G3,G3<=[VTN.xlsx]COIN!$B$9),G3+[VTN.xlsx]COIN!$C$9,IF(AND([VTN.xlsx]COIN!$A$10<=G3,G3<=[VTN.xlsx]COIN!$B$10),G3+[VTN.xlsx]COIN!$C$10,IF(AND([VTN.xlsx]COIN!$A$11<=G3,G3<=[VTN.xlsx]COIN!$B$11),G3+[VTN.xlsx]COIN!$C$11,IF(AND([VTN.xlsx]COIN!$A$12<=G3,G3<=[VTN.xlsx]COIN!$B$12),G3+[VTN.xlsx]COIN!$C$12,IF(AND([VTN.xlsx]COIN!$A$13<=G3,G3<=[VTN.xlsx]COIN!$B$13),G3+[VTN.xlsx]COIN!$C$13,IF(AND([VTN.xlsx]COIN!$A$14<=G3,G3<=[VTN.xlsx]COIN!$B$14),G3+[VTN.xlsx]COIN!$C$14,IF(AND([VTN.xlsx]COIN!$A$15<=G3,G3<=[VTN.xlsx]COIN!$B$15),G3+[VTN.xlsx]COIN!$C$15,IF(AND([VTN.xlsx]COIN!$A$16<=G3,G3<=[VTN.xlsx]COIN!$B$16),G3+[VTN.xlsx]COIN!$C$16,IF(AND([VTN.xlsx]COIN!$A$17<=G3,G3<=[VTN.xlsx]COIN!$B$17),G3+[VTN.xlsx]COIN!$C$17,IF(AND([VTN.xlsx]COIN!$A$18<=G3,G3<=[VTN.xlsx]COIN!$B$18),G3+[VTN.xlsx]COIN!$C$18,IF(AND([VTN.xlsx]COIN!$A$19<=G3,G3<=[VTN.xlsx]COIN!$B$19),G3+[VTN.xlsx]COIN!$C$19,IF(AND([VTN.xlsx]COIN!$A$20<=G3,G3<=[VTN.xlsx]COIN!$B$20),G3+[VTN.xlsx]COIN!$C$20,G3)))))))))))))))))))))))))))))))))))))))

Có 2 cách để bạn chọn

Cách 1:

Giữ nguyên hiện trạng sử dụng nhiều IF bằng cách
- đặt G3 ra ngoài
- tách công thức dài thành 2 name (ví dụ v_a cho đoạn công thức từ $21:$40, v_b tương ứng từ $2:$20)
- Name kết quả v_kq sẽ có công thức = G3 + v_a + v_b

Tách và đặt lại name , chắc không khó với bạn đúng không?, vì không có file nên bạn tự làm nhé


Cách 2:

Thay đổi hẳn công thức không dùng nhiều IF thế, - cách này phụ thuộc đặc trưng số liệu của bạn ở COIN _ sẽ dùng công thức ngắn gọn hơn,
muốn làm cách này bạn đưa bảng COIN đó lên đây sẽ có giải pháp

 

[votrongnghia]

đã add file mong các anh chị giúp

 

[vodoi2x]

đã add file mong các anh chị giúp

Vậy thì đơn giản thôi, Dùng 1 trong 2 công thức sau


công thức 1:

=G4 +INDEX([VTN.xlsx]COIN!$C$21:$C$40, IF(OR((ROUND(MOD(G4,1),1)=[VTN.xlsx]COIN!$A$21:$A$40) *(G4<=[VTN.xlsx]COIN!$C$21:$C$40)),MATCH(1, (ROUND(MOD(G4,1),1)=[VTN.xlsx]COIN!$A$21:$A$40) *(G4<=[VTN.xlsx]COIN!$C$21:$C$40),0)),0) +IF(OR(([VTN.xlsx]COIN!$A$2:$A$20<=G4) *(G4<=[VTN.xlsx]COIN!$C$2:$C$20)),INDEX([VTN.xlsx]COIN!$C$2:$C$20, MATCH(1, ([VTN.xlsx]COIN!$A$2:$A$20<=G4) *(G4<=[VTN.xlsx]COIN!$C$2:$C$20),0)),0)

Bấm tổ hợp Ctrl+Shift+Enter


HOẶC
công thức 2:

nếu số liệu tại G4 thỏa mãn các điều kiện đặt ra là duy nhất (luôn chỉ có 1 trường hợp thỏa mãn và chỉ 1 mà thôi thì dùng công thức ngắn hơn sau (cảnh giác với công thức này, có thể sai khi không thỏa mãn giả định này)

=G4 + SUMPRODUCT((ROUND(MOD(G4,1),1)=[VTN.xlsx]COIN!$A$21:$A$40) *(G4<=[VTN.xlsx]COIN!$C$21:$C$40),[VTN.xlsx]COIN!$C$21:$C$40) +SUMPRODUCT(([VTN.xlsx]COIN!$A$2:$A$20<=G4) *(G4<=[VTN.xlsx]COIN!$C$2:$C$20),[VTN.xlsx]COIN!$C$2:$C$20)

 

[votrongnghia]

không hiện bạn ơi bạn up file lên được không

 

[huuthang_bd]

Bạn tải file về tự kiểm tra nhé.