không chơi cột phụ chỉ sài hàm làm sao nên...

[xuanquynh197]

chào các bác, hix em lại có 1 vài vấn đề nan giải với Excel...
Trong sheet 1, Từ A1 đến A4, có 4 giá trị 5,2,6,8, A6 có giá trị 1
Từ B1 đến B4 có 4giá trị: j,k,d,e, B6 có giá trị M
Từ C1 đến C4 có 4 giá trị : 2,q,5,w, C6 có giá trị V
Bây h làm sao để trong sheet2, chỉ dùng công thức, không tạo thêm cột phụ, mà trong vùng A1:C5 hiển thị đúng theo thứ tự các giá trị của sheet1 là A1:A5 là 5.2.6.8.1
B1:B5 là j,k,d,e,M
C1:C5 là 2,q,5,w,V
Em không chơi VBA đâu nhé
Vấn đề thứ 2. hu hu ghét kinh khủng cũng không chơi cột phụ...
ở A1:A5 em có 5 giá trị 11,51,86,12,17
ở ô B1, em có thể điền tùy ý giá trị từ 1 đến 5, vấn đề là làm sao ( ko thêm cột phụ) mà ở C1 hiện lên chẳng hạn với 1 là min(A1:A5), với 2 là giá trị nhỏ thứ 2, 3 là giá trị nhỏ thứ 3....
Và 1 vấn đề nữa, huhu em vẫn chưa biết làm sao.... 1 có 1sheet công thức rất lớn, là file chuẩn dùng để sử lý để tạo ra hệ thống hàm khác. Em muốn khóa file đó lại ko cho ai mở ra xem và sửa.... Em biết làm sao đây....
Mong các bác giúp em, em nghĩ hoài mà vẫn chưa ra.....

 

[hocexcelS]

Tui dung offset tại ô a1 của sheet2 như sau:

IF(Sheet1!A1="",OFFSET(Sheet1!A1,1,0,1,1),OFFSET(Sheet1!A1,0,0,1,1))

Vấn đề thứ 2: trước tiên đặt validation cho B1 chỉ được nhập giá trị từ 1 đến 5, còn ở ô C1 ta nhập công thức:
=IF(B1="","Ban hay nhap so tu 1 den 5 trong o B1",SMALL(A1:A5,B1))

Mới vào diễn đàn + mới hoc excel = nhờ mấy huynh chỉ giáo thêm

Nếu bạn muốn bảo vệ Sheet ta vào (mình đang dùng MS97) Tools/Protection/:
+nếu muốn bảo vệ Sheet nào thì ta đứng ở Sheet do để vào và chọn Protect Sheet và nhập mật khẩu muốn bảo vệ
+làm tương tự cho WorkBook(chọn Protect WorkBook)
Hình như trong Excel 2003 có cho đặt Pass bảo vệ File : vào Tools/Option/Security(hay Protect gì đó) và đặt Pass+nhắc lại Pass là được)

 

[hoangdanh282vn]

chào các bác, hix em lại có 1 vài vấn đề nan giải với Excel...
Trong sheet 1, Từ A1 đến A4, có 4 giá trị 5,2,6,8, A6 có giá trị 1
Từ B1 đến B4 có 4giá trị: j,k,d,e, B6 có giá trị M
Từ C1 đến C4 có 4 giá trị : 2,q,5,w, C6 có giá trị V
Bây h làm sao để trong sheet2, chỉ dùng công thức, không tạo thêm cột phụ, mà trong vùng A1:C5 hiển thị đúng theo thứ tự các giá trị của sheet1 là A1:A5 là 5.2.6.8.1
B1:B5 là j,k,d,e,M
C1:C5 là 2,q,5,w,V
Em không chơi VBA đâu nhé
Vấn đề thứ 2. hu hu ghét kinh khủng cũng không chơi cột phụ...
ở A1:A5 em có 5 giá trị 11,51,86,12,17
ở ô B1, em có thể điền tùy ý giá trị từ 1 đến 5, vấn đề là làm sao ( ko thêm cột phụ) mà ở C1 hiện lên chẳng hạn với 1 là min(A1:A5), với 2 là giá trị nhỏ thứ 2, 3 là giá trị nhỏ thứ 3....
Và 1 vấn đề nữa, huhu em vẫn chưa biết làm sao.... 1 có 1sheet công thức rất lớn, là file chuẩn dùng để sử lý để tạo ra hệ thống hàm khác. Em muốn khóa file đó lại ko cho ai mở ra xem và sửa.... Em biết làm sao đây....
Mong các bác giúp em, em nghĩ hoài mà vẫn chưa ra.....

Để khóa sheet hay Workbook thì dùng chức năng Protect trong Tool
Để Tìm số nhỏ nhất thì dùng Min()
Để tìm số nhỏ thứ thì dùng Small()
Để bóc dữ liệu thì dùng công thức (dành cho ai không thích cột phụ, thích mãng và Name)
Tại ô A1 sheet BC : =COTA (nhớ bấm Ctrl+shift+Enter)
Với COTA đặt tên)
=IF(NOT(ISERR(SMALL((sheet1!A$1:A$100<>"")*(ROW(sheet1!$1:$100)),ROW()+COUNTBLANK(sheet1!A$1:A$100)))),INDIRECT("sheet1!A"&SMALL((sheet1!A$1:A$100<>"")*(ROW(sheet1!$1:$100)),ROW()+COUNTBLANK(sheet1!A$1:A$100))),"")

Còn chức năng Vâlidtion chỉ tạo list danh sách để chọn, hay dùng để kiểm tra nhập liệu chứ ko trích lọc được dữ liệu.
Thân!

 

[anhtuan1066]

Vầy ngắn gọn hơn nè.. 1 name duy nhất!
ANH TUẤN

 

[hoangdanh282vn]

Vầy ngắn gọn hơn nè.. 1 name duy nhất!
ANH TUẤN

Em cũng chơi 1 name luôn

 

[anhtuan1066]

Em cũng chơi 1 name luôn

1> Bạn cố gắng tránh xài hàm ISERR nếu có thể... vì nó làm cho tốc độ tính toán giãm đáng kể đấy!
2> Đoạn này:
=CHOOSE(COLUMN(sheet1A1),"A","B","C","D.... ko ổn lắm... Nếu dử liệu nhập vượt khỏi cột Z thậm chí đến cột IV thì bạn làm sao?
Nếu muốn 1 hàm lấy tên cột thì nên dùng cái này theo tôi là ngắn nhất:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

3> Nếu muốn dùng hàm INDIRECT để lấy dử liệu ở sheet khác thì nên cố gắng tạo 1 công thức lấy tên sheet (công thức này tốt nhất nên nằm ở sheet cần lấy tên), nếu ko sheet bị đổi tên thì công thức cũng die luôn!
Làm ra dc kết quả là niềm vui lớn, tuy nhiên ta ko nên dừng ở đó mà nên tìm cách tối ưu thêm nữa... càng ngắn gọn, càng nhanh thì càng tốt
Vài góp ý nhỏ!
Mến
ANH TUẤN

 

[xuanquynh197]

cảm ơn các bác nhiều, các bác giúp em được mở rộng hơn nữa tầm mắt về excel và thấy 1 điều mình chẳng biết j về excel cả huhu thế mới khổ chứ...
trong các bảng tính các bác tạo ra em thấy có công thức trong ngoặc{}, em đã đọc mấy cái sách dạy excel bán ngoài các hiệu sách rồi nhưng ko hiểu lắm về nó, các bác có thể giải thích cho em hiểu hơn được không ạ??
Vấn đề thứ 2 lại là vấn đề nan giải nhất huhu các bác hơi hiểu sai ý em 1 tý... ở đây em muốn trong sheet BC sẽ hiển thị đúng như ở sheet1 chỉ khác ở 1 chỗ là tất cả các dòng mà không có 1 chút dữ liệu nào thì sẽ không hiển thị.. cụ thể em đã gửi cùng file đính kèm..
1 lần nữa xin cảm ơn các bác nhiều,,, hihi công nhận diễn đàn có nhiều bác siêu cao thủ thật nếu có điều kiện em rất muốn được bái sư

 

[hoangdanh282vn]

1> Bạn cố gắng tránh xài hàm ISERR nếu có thể... vì nó làm cho tốc độ tính toán giãm đáng kể đấy!
2> Đoạn này:
=CHOOSE(COLUMN(sheet1A1),"A","B","C","D.... ko ổn lắm... Nếu dử liệu nhập vượt khỏi cột Z thậm chí đến cột IV thì bạn làm sao?
Nếu muốn 1 hàm lấy tên cột thì nên dùng cái này theo tôi là ngắn nhất:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

3> Nếu muốn dùng hàm INDIRECT để lấy dử liệu ở sheet khác thì nên cố gắng tạo 1 công thức lấy tên sheet (công thức này tốt nhất nên nằm ở sheet cần lấy tên), nếu ko sheet bị đổi tên thì công thức cũng die luôn!
Làm ra dc kết quả là niềm vui lớn, tuy nhiên ta ko nên dừng ở đó mà nên tìm cách tối ưu thêm nữa... càng ngắn gọn, càng nhanh thì càng tốt
Vài góp ý nhỏ!
Mến
ANH TUẤN

Cảm ơn Anhtuan nhiều.
Quả thật khi làm thì mình cũng nghĩ đến, Mình đã dùng thử như Cell, Column.. nhưng nó sẽ làm cho công thức dài và chậm đáng kể.mình làm ở trên chỉ mang tính gợi ý, không áp dụng cho thực tế.Mình thiết nghĩ nếu ứng vào thực tế thì chỉ cần 1 cột là đủ, khi cần thì tác giả tự thêm vào,
Trong công thức ta nên chọn vùng tham chiếu tối giản nhất(chỉ mang tính gợi ý) để cho file chạy nhanh. khi cần thì tác giả tự mở rộng tham chiếu và điều kiện.

 

[hoangdanh282vn]

cảm ơn các bác nhiều, các bác giúp em được mở rộng hơn nữa tầm mắt về excel và thấy 1 điều mình chẳng biết j về excel cả huhu thế mới khổ chứ...
trong các bảng tính các bác tạo ra em thấy có công thức trong ngoặc{}, em đã đọc mấy cái sách dạy excel bán ngoài các hiệu sách rồi nhưng ko hiểu lắm về nó, các bác có thể giải thích cho em hiểu hơn được không ạ??
Vấn đề thứ 2 lại là vấn đề nan giải nhất huhu các bác hơi hiểu sai ý em 1 tý... ở đây em muốn trong sheet BC sẽ hiển thị đúng như ở sheet1 chỉ khác ở 1 chỗ là tất cả các dòng mà không có 1 chút dữ liệu nào thì sẽ không hiển thị.. cụ thể em đã gửi cùng file đính kèm..
1 lần nữa xin cảm ơn các bác nhiều,,, hihi công nhận diễn đàn có nhiều bác siêu cao thủ thật nếu có điều kiện em rất muốn được bái sư

Dấu {} trong công thức dùng để chỉ hàm mãng. Sau khi gõ xong công thức ta nhấn CTRL+SHIFT+ENTER thì công thức thường sẽ trở thành công thức mãng và xuất hiện dấu {} bao lấy công thức đóCòn về VD mà bạn đưa ra thì quả thật mình ko hiểu và làm nó để làm gì?Bạn chỉ cần đặt một hàm if nho nhỏ và dùng autofillter rồi xóa cái rẹt là xong, mất 30 giâyCông thức phức tạp chỉ nên áp dụng cho những trường hợp phức tạp.Nếu bạn muốn hiểu thêm công thức thì ok? Chứ để áp dụng thì hơi bị lãng phí vì làm chậm máy.

 

[anhtuan1066]

Vấn đề thứ 2 lại là vấn đề nan giải nhất huhu các bác hơi hiểu sai ý em 1 tý... ở đây em muốn trong sheet BC sẽ hiển thị đúng như ở sheet1 chỉ khác ở 1 chỗ là tất cả các dòng mà không có 1 chút dữ liệu nào thì sẽ không hiển thị..

Là sao ta? Giãi pháp mọi người đưa lên ko đúng ý bạn ở chổ nào?
Tôi thấy file ví dụ bạn đưa lên nếu có nhiều dòng rổng thì bạn trích lấy 1... nếu có 1 dòng rổng thì bỏ luôn... Ý bạn như vậy à? Nó có tác dụng gì? Sao ko bỏ dòng rổng luôn?
Bạn giãi thích thêm 1 chút nhé!
ANH TUẤN

 

[hoangdanh282vn]

Là sao ta? Giãi pháp mọi người đưa lên ko đúng ý bạn ở chổ nào?
Tôi thấy file ví dụ bạn đưa lên nếu có nhiều dòng rổng thì bạn trích lấy 1... nếu có 1 dòng rổng thì bỏ luôn... Ý bạn như vậy à? Nó có tác dụng gì? Sao ko bỏ dòng rổng luôn?
Bạn giãi thích thêm 1 chút nhé!
ANH TUẤN

Theo mình thì bác nói đúng rồi.
Theo tác giả thì nếu có 3 cell rổng trong một dòng thì không lấy dòng này qua, các dòng sau đôn dòng lên trên 1 dòng so với dữ liệu gốc.
Thế thì if +autofillter+delete hay advanfillter la nhanh nhất Bác nhỉ?

 

[anhtuan1066]

Ah... tôi hiểu rồi... Vậy xem file này nhé!
Giãi pháp đơn giản, tuy rằng ko dc hay cho lắm.. nhưng bạn có thể tham khảo!
ANH TUẤN

 

[xuanquynh197]

hìhì dạ như trong ví dụ em đã nói ý ạ, tức là hiện nay em có 1 mảng công dữ liệu lớn, vấn đề là ở chỗ nếu xây dựng hệ thống hàm lớn và dài thì sẽ rất khó để chạy nhanh, giả sử em có 1.000 dòng dữ liệu dữ liệu này có thể chỉnh sửa bằng cách xoá, thêm dòng hay bớt dòng.... với công thức nhỏ thì việc thêm bớt dòng sẽ làm sai giá trị hiển thị ở các báo cáo mà ta cần, vì vậy em đã tạo ra 1 sheet xử lý với các cột phụ nhỏ để giảm bớt mức tính của hàm excel... nhưng làm sao để các dữ liệu này là liên tiếp và đảm bảo rằng khi ta thêm hay bớt dòng, hoặc trong dữ liệu gốc có dòng trống thì nó sẽ không hiện lên. Như vậy việc lên báo cáo mới liền mạch và chính xác đựơc....nếu giải quyết được bài toán trên thì sẽ xong khâu xử lý dữ liệu đầu vào và ta có thể chỉnh sửa thêm bớt cột ở bất kỳ đâu 1 cách thoải mái mà không sợ dữ liệu bị "break out" hìhì...
Và 1 vấn đề nữa là file báo cáo này em tạo ra còn để gửi vào 1 số nơi, ở đó họ chỉ biết nhập và tạo ra file cuối cùng họ ko rành về excel vì vậy ở cơ sở dữ liệu gốc em muốn cho họ tuỳ nhập chỉnh sửa thêm bớt dòng mà không ảnh hưởng đến công thức,.....em cũng muốn làm VBA nhưng khó quá em chưa " thạo" nên đành làm cái này trước

 

[anhtuan1066]

Nếu dử liệu nhiều quá thì bạn Filter đi... Filter với tùy chọn Blank cho tất cả các cột... Việc còn lại là .. chọn và xóa bằng tay.. cũng đơn giãn mà
Còn nếu như có gì trục trặc e rằng bạn phải post dử liệu lên cho mọi người nhìn thấy thì mới hình dung dc bạn à!
ANH TUẤN

 

[xuanquynh197]

huhu em biết thế nhưng mà khủng khiếp lắm anh ạ, nó đã khá đồ sộ rồi, hệ thống hàm em xây dựng cho nó là 10.000 dòng và các công thức trong bảng link với nhau từ sheet xử lý ( sheet xử lý lấy từ sheet nhập ) huhu tổng dung lượng của file đó giờ đã vào tầm 12mb ( cho khoảng 10sheet).... nặng dã man, và cái cuối cùng em chưa nghĩ ra cách xử lý là lấy dữ liệu từ sheet nhập vào sheet xử lý. Vì mọi thứ được làm từ sheet xử lý. còn mọi thứ chỉnh sửa từ sheet nhập... hìhì... 1 lần nữa cảm ơn nhiều nhé anh Anh Tuấn và tất cả mọi người...