Tổng hợp Hỏi đáp về Excel (3 người xem)

[handung107]

Lỗi #N/A

Câu hỏi 3 : Chữa lỗi #N/A khi dùng công thức VLOOKUP, HLOOKUP :

- Bạn có thể điều khiển những lỗi này của hàm Vlookup, Hlookup bằng các hàm thông tin Iserror(), IsNA()

= If(ISNA(vlookup()),"không tìm thấy", "Giá trị tìm thấy: " & Vlookup())
= If(ISERROR(vlookup()),"không tìm thấy", "Giá trị tìm thấy: " & Vlookup())

- Hàm VLookup (hay bất kỳ hàm thư viện nào khác) đều có đặc tả chức năng rõ ràng và xác định, người dùng phải hiểu và sử dụng đúng theo đặc tả của hàm. Thí dụ nếu bạn tra cứu tài liệu chỉ dẫn về hàm VLookup thì sẽ biết rõ rằng nếu dò tìm không có (và nếu tham số thứ tư của hàm là False) thì hàm sẽ trả về mã lỗi “#N/A”. Lưu ý rằng nếu chọn giá trị 0 để báo lỗi như bạn đề nghị thì không tổng quát vì giá trị 0 có thể trùng với kết quả tìm được. Tuy nhiên để giải quyết yêu cầu của bạn, cách thông thường là định nghĩa 1 hàm user-defined mới có đặc tả y như hàm VLookup rồi hiệu chỉnh lại mã lỗi “#N/A” về 0. Thí dụ hàm MyVLookup do chúng tôi viết như sau:
‘Hàm MyVlookup có danh sách tham số y như VLookup
Public Function MyVLookup(val As Variant, r As Range, c As Integer, flag As Boolean) As Variant
‘gọi VLookup
MyVLookup = Application.VLookup(val, r, c, flag)
‘kiểm tra xem có lỗi không, nếu có trả về 0
If IsError(MyVLookup) Then MyVLookup = 0
End Function
Nếu bạn đặt hàm trên trong 1 file add-ins (thí dụ file *.xla) rồi add vào Excel thì bạn có thể gọi nó ở bất kỳ tài liệu Excel nào. Còn nếu bạn đặt hàm trong 1 module code của 1 tài liệu Excel thì chỉ có tài liệu Excel đó dùng được thôi.

 

[handung107]

Câu hỏi 4 : Cách đổi dấu ngăn cách địa chỉ trong công thức EXCEL

Hỏi : Công thức trong EXCEL từ:
=sumif(A1:A10;A11;B1:B10)

biến thành:
=sumif(A1:A10.11.1:B10)

Nên bay giờ, cong thức ko cho kết quả tính nữa, mà gõ thêm công thức mới cũng không được, vì EXCEl cứ nhầm lẫn địa chỉ.

Xin chỉ giúp cách đổi dấu ngăn cách địa chỉ trong công thức EXCEL từ "." thành ";" hay "," .

Trả lời :

- Dùng lệnh Replace trong menu Edit

Đầu tiên là bôi đen vùng công thức cần thay đổi, nếu chọn cả bảng thì dùng Ctrl+A.
Sau đó dùng lệnh trong menu Edit \ Replace...

- Vào Control Panel - Regional And Languege Options - Customize, vào Tab Number....

 

[handung107]

Lặp lại tiêu đề

Câu hỏi 5 : Lặp lại tiêu đề trong Excel bằng cách nào ?

Hỏi : Trong Exel có thể lặp lại tiêu đề như ở trong Word bằng cách vào Table/headings được không?

Trả lời :

Excel có chức năng Row to repeat at top để in lặp lại 1 hay nhiều dòng nào đó ở đầu mỗi trang (dưới Header). Bạn vào File - Page Setup, chọn Tab Sheet sẽ thấy ngay. Ngoài ra còn có chức năng in lặp lại các cột bên trái (Column to repeat at left)

 

[handung107]

Format

Câu hỏi 6 : Format số trong dãy text

Hỏi : Ví dụ mình có hai cell, cell A1: chứa số 12.250.785, ô thứ 2 chứa công thức: ="số tiền là: "&A1&" đồng" thì sẽ trả kết quả là số tiền là: 12250785 đồng , mình muốn kết quả định dạng thế này: số tiền là: 12.250.785 đồng. có nghĩa là phải có dấu phân cách hàng đơn vị, hàng nghìn, triệu chứ không viết liền như kết quả trên. Vậy có cách gì giải quyết vấn đề trên không?

Trả lời : Có 2 cách :

Cách 1 :

="số tiền là: "&text(A1,"#,##0") &" đồng" : Không đồng thì hiện số 0
hoặc
="số tiền là: "&text(A1,"#,###") &" đồng" : Không đồng thì chẳng hiện gì cả "số tiền là: đồng"

Hoặc:
="số tiền là: "&text(A1,"#,###0[$ đồng]"

Cách 2 :

Dùng phím Ctrl +1 và sử dụng customize với mã định dạng như sau:
"Số tiền là "#.###" đồng"
Bằng cách này thì bác có thể +-*/ giá trị với cell đó được còn nếu dùng công thức thì... lại phải dùng công thức tiếp.

 

[handung107]

Câu hỏi 7 : Format ngày tháng theo VN

Hỏi : Nếu định dạng format là ngày tháng theo kiểu Việt Nam thì công thức khai báo thế nào? Mình khai báo theo kiểu m/d/yyyy cũng không được?????

Trả lời :

Hoặc :

="Ngày tháng : "&text(A1,"dd/mm/yyyy") &" "

Hoặc :

"Ngày tháng là "dd/mm/yyyy" "

Hoặc

- Bạn vào Format / Cells (hoặc nhấn Ctrl+1).

Chọn thẻ Number, sau đó vào phần Custom.

Trong ô Type, bạn gõ "ngày" dd "tháng" mm "năm" yyyy

Hoặc :

-="Ngày "&DAY(TODAY())&" Tháng "&MONTH(TODAY())&" Năm "&YEAR(TODAY())

- Bạn có thể tạo 1 hàm đa năng tự tạo như sau:
Function NgayVN(So as Integer, Optional Dat as Date ) as String
Dim Chu as String
If IsDate(Dat)= 0 then Dat=Date()
Chu = Right("0" & CStr(day(Dat)), 2) & " tháng " & Right("0" & CStr(Month(Dat)), 2) & " năm " & CStr(Year(Dat)) & "."

Select case So
case 1 'Dùng cuối bảng BC
NgayVN="Kiến an, ngày " & Chu
Case 2 'Dùng để viết thư & lập biên bản. . .
NgayVN = "Hôm nay ngày " & Chu
case ...
. . . . . . . . . . . . .
Case Else
NgayVN="Tào lao!"
End Select
End Function

 

[handung107]

Điền số TT

Câu hỏi 8 : Điền số TT lẻ hay chẵn

Hỏi : Bạn nào có cách điền số TT 1 -3 -5 -7 ... và 2 -4 -6 -8 kô?

Trả lời :

- Cách 1:
Bạn điền vào hai ô đầu lần lượt, VD A3 là 1; A4 là 3 (hoặc 2; 4), chọn (bôi đen) 2 ô này đưa chuột ở góc dưới bên phải ô A4 (chuột có biểu tượng dấu +) bạn kéo tiếp xuống đến các ô tiếp theo và thả chuột.
- Cách 2:
Gõ vào ô A3 thứ tự 1 (hoặc 2), chọn vùng đánh STT (có cả ô A3) vào menu Edit/Fill -> Series trong mục Step value gõ vào giá trị bước nhảy là 2 và OK.
- Cách 3 :
Ô A1, bạn gõ 1 hoặc 2
Ô A2, bạn gõ "=A1+2"
Kéo công thức xuống.

Dùng cách này được cái là khi bạn chèn thêm dòng vào giữa bảng dữ liệu thì bạn chỉ cần sửa lại công thức ở ngay chỗ bạn chèn dòng mới, thì các STT sau tự nhảy.

Còn nếu không dùng công thức, để Excel làm tự động thì khi chèn dòng thì phải làm lại.

 

[Pansy_flower]

Bổ sung câu hỏi 6

Cách 1 :

="số tiền là: "&text(A1,"#,##0") &" đồng" : Không đồng thì hiện số 0
hoặc
="số tiền là: "&text(A1,"#,###") &" đồng" : Không đồng thì chẳng hiện gì cả "số tiền là: đồng"

Hoặc:
="số tiền là: "&text(A1,"#,###0[$ đồng]")
Hoặc:
Bạn định dạng tại ô muốn hiển thị số tiền :

"số tiền là: "#,##0" đồng"

 

[handung107]

Xóa một số hàng trong công thức mảng

Câu hỏi 9 : Làm cách nào để xóa một số hàng trong công thức mảng ?

Hỏi : Nếu xóa một số hàng trong công thức mảng, Excel sẽ báo lỗi "Cannot change part of an array". Phải làm thế nào khi gặp trường hợp này ?

Trả lời : Chuyển công thức thành dạng Text bằng cách

- Đưa thanh sáng đến vùng công thức
- Trên thanh soạn thảo công thức, xóa dấu = và chuỗi dùng để nhận kết quả. Cách này sẽ chuyển toàn bộ các ký tự trong chuỗi công thức sang một vùng các giá trị của văn bản trừ dấu () và dấu =
- Đưa công thức ở dạng văn bản vào ít nhất một ô
- Sau đó, xóa các ô bạn muốn
- Đưa thanh sáng đến vùng mới, bắt đầu với một ô chứa công thức ở dạng văn bản
- Điền dấu = vào đầu công thức và nhấn Ctrl + Shift + Enter vào chuỗi công thức để thiết lập lại công thức mảng trong vùng thích hợp

 

[handung107]

Hòi về công thức

Câu hỏi 10 : Vì sao khi Merge các ô xong, kết quả công thức không đúng ?

Hỏi :

Bạn Merge các ô : F65:H65; I65:K65; L65:N65; O65:Q65 thành 4 ô lớn và nhập các số hạng lần lượt 1, 2, 3, 4 vào các ô đó. Bạn muốn tính giá trị trung bình của 4 ô đó với công thức sau :
{=AVERAGE(IF(ISERROR(F65:Q65),"",F65:Q65))}
Kết quả là 0.83333, tương ứng với (1+2+3+4)/12, trong khi lẽ ra phải cho kết quả là 2.5 = (1+2+3+4)/4

Trả lời :

Khi bạn sử dụng công thức mảng như trên, Excel sẽ đếm tất cả các ô trong dãy F65:Q65 là 12 ô , nên cho kết quả là (1+2+3+4)/12. Để có kết quả như bạn mong muốn, bạn phải làm sao để Excel hiểu và đếm chỉ những ô có số hạng và lấy trung bình cộng của chúng. Do đó, bạn phải sử dụng hàm COUNT(F65:Q65) để đếm các ô có số hạng, và công thức của bạn trở thành :
=SUM(F65:Q65)/COUNT(F65:Q65)
Kết quả sẽ là 2.5, đúng như ý bạn đấy

 

[handung107]

Excel và PowerPoint

Câu hỏi 11 : Chuyển biểu đồ trong các Sheets sang PowerPoint

Hỏi :

Mình có một workbook có nhiều sheet chứa biểu đồ và dữ liệu trên Excel, bây giờ mình muốn chuyển toàn bộ workbook này sang powerpoint thì phải làm ntn??

Trả lời :

Cách 1 :

- Copy vùng số liệu từ Excel sang PowerPoint, vùng số liệu sẽ nằm dưới dạng bảng,
Copy biểu đồ qua Powerpoint bình thường

Cách 2 :

Bạn sử dụng lệnh Insert/Object với Object Type là MS Excel Chart hoặc Apply Slide Layout để lựa chọn Slide có chứa Object chart - nếu bạn muốn thiết kế biểu đổ mới.

 

[Peter_pan]

Câu hỏi 12: Chữa lỗi #Value

Câu hỏi của anh thanhtam348

Không biết chương trình Excel của tôi tại sao hôm nay khi mở lên những ô công thức đều biến dạng cả, chỉ còn thấy #VALUE! tòan bộ các ô, tôi dùng Norton quét Virus thỉ cũng không thấy có, khi vừa mở Excel lên thì có hiện một bảng đề chử " Unable to read file " .

Đầu tiên khi hôm nay mở bảng tính ra là mình đã thấy nó như vậy, chưa tham chiếu một ô nào, và tất cả các Sheet khác trong 1 file đều cũng giống như 1, nó chỉ biến dạng ở các hàng hoặc cột có chứa chử, cho dù ô đó có công thức hay không có, còn các cột và hàng chứa số thì không bị biến dạng, nhưng đặc biệt là công thức không bị mất.những ô công thức bằng số thì số vẩn còn và vẩn đúng. thí dụ như công thức tham chiếu với tên "A" đáp số bằng 20, thì hiện giờ tham chiếu với #Value con số cũng còn là 20........
Xin cầu cứu giúp cho. Cám ơn nhiều.

Anh workman trả lời:

Xem ra lỗi này không thuôc trách nhiệm của Virus đâu, bạn nghi oan cho nó đấy.

#Value: Hai lỗi cơ bản dẫn đến lỗi này. Một là bạn gõ sai công thức. Thứ hai là do "toán hạng" (hì hì không biết dịch chữ operand là gì) ví dụ bạn tham chiếu vào một ô sai hoặc bị lỗi.

Để fix lỗi này: Click vào ô bị lỗi #Value. Click vào cái ô vuông vuông có dấu chấm than, sau đó chọn "Trace Error".

Anh Adam_tran trả lời:

• Lỗi #VALUE! thì vô số, rất khó hình dung. Nhưng tất cả các công thức đều bị lỗi thì tớ nghỉ không phải do file Excel mà do bạn thiết lập 1 options nào đấy của Excel bị sai. Bạn thử copy tất cả các sheet (nhấp chuột phải vào tên sheet, chọn "Move and Copy", nhớ chọn create copy) để tạo ra 1 file khác. Tắt file cũ rồi lưu file mới, đem sang máy khác dùng xem sao.
  Bạn upload gửi file về adam_tvn@yahoo.com để tôi upload lên xem. Tôi dùng NA 2005 luôn update virus definition nên tôi cũng có thể kiểm tra file bạn có bị virus không.

• Tớ đã nhận được file của bạn thanhtam, nhưng file này thực chẳng có lỗi gì. Những ô chứa #VALUE hoàn toán là giá trị mà chẳng có công thức nào cả. Khi copy dữ liệu từ file này sang file khác, có thể Excel chỉ cho dán Value, nguyên nhân có thể do sheet nguồn được protect - hiddden formular, hoặc sheet nguồn chứa quá nhiều link, bạn copy 1 lần dữ liệu lớn quá, Excel chỉ dán có giá trị.
  Về lỗi #VALUE trong file gốc của bạn (mà bạn nói là 20MB) có thể do dữ liệu quá nhiều, máy tính có cấu hình thấp khiến Excel xử lý không được. Đây chỉ là phỏng đoán thôi vì tớ chỉ gặp lỗi trên khí sử dụng công thức mảng quá phức tạp hoặc dùng hàm IRR với khoảng nội suy quá rộng ... Cũng có thể 2 Array trong 1 công thức mảng không tương đương, nó cũng báo lỗi #VALUE
  Cũng có thể 1 giá trị nằm trong 1 Array kiểu ngày có giá trị không thuộc kiểu ngày, nó cũng báo #VALUE. Một ô lỗi #VALUE thì các ô khác lấy dữ liệu từ ô này cũng báo lỗi #VALUE...
  Tóm lại, lỗi #VALUE là rất nhiều, bạn phải gửi file gốc mới biết được chính xác. Bạn có thể Save As file gốc thành 1 file mới, xóa hết dữ liệu chỉ chừa lại một vài dòng, nén lại thành ZIP hoặc RAR và gửi cho tôi.

 

[OverAC]

Thông báo, kể từ hôm nay box này sẽ được khóa lại, và chỉ được post bởi các Mod, Smod hay Admin. Dùng để tổng hợp những câu hỏi về những vấn đề hay của các thành viên. Nhưng vấn đề chớp nhoáng. Nếu các thành viên có thắc mắc nhanh, nhỏ có thể hỏi tại topic này bên này:
http://www.giaiphapexcel.com/forum/showthread.php?t=328