How to… excel :D

–09.10.2016–Có bạn hỏi GD thì dùng excel thế nào, mình vò đầu bứt tóc mãi cũng ko biết viết dùng thế nào, thôi thì viết bừa, ai biết rồi thì bỏ qua, có gì sai thì sửa, chỗ nào cần biết thêm thì comment mình viết chi tiết hơn T_T . Viết đại khái thôi chứ cũng ko biết viết gì.
(chỉ rất căn bản thôi chứ không có tut vẽ mario đâu nhé . . .)Mình add thêm phần bổ sung chi tiết từ comment của bạn Tien Pham vào để mọi người xem cho chuẩn hơn ????

Các hàm toán:

Công trừ nhân chia:

Cái này thì đơn giản rồi, nhưng hồi mơi đi làm không ai dạy nên cũng có mấy cái ngáo lắm, cũng phải google miết mới biết . . . nên cứ viết cho bài.. đủ dài

Cộng trừ nhân chia : + – * /
Lũy thừa : ^ hoặc POW()
Căn 2 : SQRT()
Căn n của a : a^(1/n)Ứng dụng : Điền công thức vào ô , xong kéo xuông thì tính ra được str của hero 220001 ở các level 1 đến 10, điền dấu $ trước column 6 trong công thức để cố định biến số trước khi kéo xuống.

bảng stats hero vớ vẩn . . .

Bây giờ điền dấu $ vào trước row G (16) ( level ) rồi copy lại vào cột cũ, do không điền dấu $ trước Row G(6) và J(6) , nên khi kéo ngang ta có tiếp chỉ số agi và int ở lvl 1 đến 10 của hero 220001

Hàm Sum :

SUM (cell_1:cell_2) = SUM toàn bộ các ô từ cell_1 -> cell_2. VD: SUM(A1:B2) là A1 + A2 + B1 + B2
SUM (cell_1, cell_2, … cell_n) = SUM các ô riêng biệt

Tính tổng của các cell trong range, điền vào phần bên ngoài rồi kéo xuống, có tổng stats tại mỗi level.

tương tự sum còn có product, nhân tất cả các số trong range với nhau. FACT(a) , nhân từ 1 đến a, biết mấy hàm trên là có thể làm mấy cái bảng tính nho nhỏ ví dụ như :

công thức tính có thể gú gồ probability…….

bảng trên mình dùng để tính xác xuất các trường hợp xảy ra khi có skill đánh 3 mục tiêu và 6 mục tiêu, xác xuất stun 15%~45% thì xác xuất stun 0,1,2,3 mục tiêu hay 6 mục tiêu lần lượt là bao nhiêu. . . có thể áp dụng rút thẻ, mở rương, 10 lần mỗi lần 10% …v…v…Ngoài ra còn có sumproduct, min, max, cũng hay được dùng. ..

Hàm IF:

IF (logical_test, [value_if_true], [value_if_false])
logical_test = điều kiện kiểm tra, VD như A1 > 0
[value_if_true] = giá trị trả về nếu logical_test là đúng
[value_if_false] = giá trị trả về nếu logical_test là sai
value_if_true và value_if_false có thể thay bằng 1 hàm khác

hàm này chắc dùng nhiều nhất sau VLOOKUP quá …. ví dụ dưới đây là bắt đầu bằng 22 thì là hero, 23 thì là lính chẳng hạn, dùng điều kiện ID nhỏ 230k lớn hơn 220k, trả ra là hero, muốn trả ra string text thì dùng “ text ” , có thể dùng nhiều if lồng vào nhau, nhưng nếu nhiều tầng điều kiện quá thì nên phân ra nhiều ô support hoặc dùng các công thức khác, ví dụ với ID này thì rõ ràng dùng LEFT + VLOOKUP/HLOOKUP sẽ dễ hiểu hơn nhiều

Các hàm index

VLOOKUP

VLOOKUP (value, table, col_index, [range_lookup])
value = giá trị cần tìm
table = khu vực cần tìm, có thể là 1 cột, 1 hàng, 1 khoảng (A1:B2), 1 cột/hàng của 1 bảng (TableName[@TableColumn]),
col_index = tìm giá trị ở cột thứ mấy ? vd = 1 là trả về giá trị ở chính cột đó, = 2 thì lấy cột bên trái, cứ vậy đếm qua phải, VLOOKUP không tìm qua trái được
[range_lookup] = TRUE nếu tìm gần đúng, FALSE nếu tìm chính xác

ví dụ :
VLOOKUP A6 trong phạm vi S20:T27, cột 2, chính xác : 220001 trả ra giá trị DaFaShi, add thêm prefix Avatar để điền tên file image Avatar cho hero, tương tự với cột picture ta có tên file để điền vào Picture . ( Quy tắc đặt tên file image đã được thống nhất là Prefix+ “_” + Tên hero.

tương tự có hàm hlookup, đếm theo row, cách dùng y hệt
nâng cao thì dùng MATCH+INDEX , chuẩn xác và không đòi hỏi format bảng cố định,nhưng khó nhớ hơn.

Các hàm xử lý string(text)

Left, Right, Mid :

lấy 1 đoạn ký tự từ 1 ký hiệu có sẵn từ trái qua
LEFT (text, [num_chars])
text = đoạn string cần tìm
[num_chars] = số ký tự cần tìm, không khai báo thì tự hiểu = 1
RIGHT tương tự
MID (text, start_num, num_chars) cũng tương tự LEFT/RIGHT với start_num là vị trí bắt đầu tìm ( = substring)

Dùng if để xác định 2 chữ số đầu của 220001 = 22 , nếu có trả giá trị “hero”

Hàm SUBSTITUTE() , LEN()

SUBSTITUTE ( = string_replace)
SUBSTITUTE (text, old_text, new_text, [instance])
text = đoạn text cần xử lý
old_text = đoạn ký tự cần thay
new_text = đoạn ký tự mới
[instance] = thay cái nào ? nếu không khai báo thì thay hết, = 1 thì thay cái đầu tiên, = 2 thì thay cái thứ 2, cứ thế

LEN(): dùng để đo chiều dài của textVí dụ: Dùng SUB để bỏ dấu “,” trong text miêu tả duyên phận, rồi tính hiệu số chiều dài, tính ra số dấu phẩy bị bỏ, tính ra số người trong duyên phận

Hàm Find:

FIND tìm vị trí của 1 đoạn ký tự trong 1 đoạn text ( = string.indexOf / str_pos)
FIND (find_text, within_text, [start_num])
find_text = đoạn text muốn tìm ra
within_text = đoạn text chứa find_text
start_num = bắt đầu từ vị trí nào, không khai báo thì mặc định = 1Ví dụ tìm chuỗi binladen, ở x20 thì có kết quả bằng 22, x21 có kết quả bằng 15

Hàm Lookup để lọc chuỗi số ra khỏi 1 cell:

Công thức :
LOOKUP(9E+307,–MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT(‘1:’&LEN(A1)))))dùng thì đổi A1 thành mục tiêu muốn xử lý là được . . .
9E+307 là số lớn nhất trong excel
FIND (0 đến 9 ) – chữ số đầu tiên trong cell , mục tiêu = A1&1234567890 để tránh trường hợp text ko có số…
— để chuyển kết quả thành số …
lookup các kết quả có được , gần với 9E+307 nhất, là kết quả, dùng 99^99 cũng dc . . .
nếu string có nhiều số thì dùng subs để lọc dần ra . . .

MISC: (how to lười…)

F4 để lập lại thao tác vừa làm
F9 để run từng phần trong công thức, chủ yếu để check lỗi, dùng chuột kéo qua phàn công thức muốn check rồi ấn F9
Crl+Shift+arrow : chọn hết các ô từ vùng đang chọn đến hướng mũi tên vừa bấm
Chọn 1 vùng rồi nhập 1 giá trị vào ô đầu tiên rồi bấm Crl+Enter , để nhập giá trị đấy cho tất cả các ô trong vùng dc chọn
Tab, Shift + tab để tiến tới tiến lùi , enter, shift+enter để lên xuống, cho những ai lười dùng chuột…
Dùng dấu & để nối 2 chuỗi với nhauNgoài ra phần art & visual của excel cũng rất hữu dụng nếu bạn không muốn học thêm Visio, vẫn có thể dùng để vẽ chart các thứ như thường, thậm chí là cả mario . . . .

Final words :

Tất cả những gì excel làm được thì dùng ngôn ngữ lập trình bất kỳ đều có thể làm tốt hơn, nhưng tự làm được thì nên tự làm, ít nhất cũng biết được cái mình đang làm nó sẽ ra sao, chủ yếu để so sánh và dự đoán, xử lý trước trên excel rồi client chỉ output thì GD sẽ được chủ động hơn nhiều.
Chủ yếu học từ cóp nhặt nên cũng ko biết phải nói gì nói sao cho dễ hiểu, có gì thông cảm giúp hic hic.


Coyp&Paste Comment của Tiên Tiên :Bổ sung cái syntax cho bác thớt, không hẳn là đầy đủ mà là mấy cái hay xài thôi
– SUM
SUM (cell_1:cell_2) = SUM toàn bộ các ô từ cell_1 -> cell_2. VD: SUM(A1:B2) là A1 + A2 + B1 + B2
SUM (cell_1, cell_2, … cell_n) = SUM các ô riêng biệt

– IF
IF (logical_test, [value_if_true], [value_if_false])
logical_test = điều kiện kiểm tra, VD như A1 > 0
[value_if_true] = giá trị trả về nếu logical_test là đúng
[value_if_false] = giá trị trả về nếu logical_test là sai
value_if_true và value_if_false có thể thay bằng 1 hàm khác

– VLOOKUP
VLOOKUP (value, table, col_index, [range_lookup])
value = giá trị cần tìm
table = khu vực cần tìm, có thể là 1 cột, 1 hàng, 1 khoảng (A1:B2), 1 cột/hàng của 1 bảng (TableName[@TableColumn]),
col_index = tìm giá trị ở cột thứ mấy ? vd = 1 là trả về giá trị ở chính cột đó, = 2 thì lấy cột bên trái, cứ vậy đếm qua phải, VLOOKUP không tìm qua trái được
[range_lookup] = TRUE nếu tìm gần đúng, FALSE nếu tìm chính xác

– LEFT, lấy 1 đoạn ký tự từ 1 ký hiệu có sẵn từ trái qua
LEFT (text, [num_chars])
text = đoạn string cần tìm
[num_chars] = số ký tự cần tìm, không khai báo thì tự hiểu = 1
RIGHT tương tự

– MID (text, start_num, num_chars) cũng tương tự LEFT/RIGHT với start_num là vị trí bắt đầu tìm ( = substring)

– SUBSTITUTE ( = string_replace)
SUBSTITUTE (text, old_text, new_text, [instance])
text = đoạn text cần xử lý
old_text = đoạn ký tự cần thay
new_text = đoạn ký tự mới
[instance] = thay cái nào ? nếu không khai báo thì thay hết, = 1 thì thay cái đầu tiên, = 2 thì thay cái thứ 2, cứ thế

– FIND tìm vị trí của 1 đoạn ký tự trong 1 đoạn text ( = string.indexOf / str_pos)
FIND (find_text, within_text, [start_num])
find_text = đoạn text muốn tìm ra
within_text = đoạn text chứa find_text
start_num = bắt đầu từ vị trí nào, không khai báo thì mặc định = 1

Có vậy thôi, good luck & ủng hộ tư tưởng sharing của thớt (y)

Leave a Comment