Nóng " Lại List " Auto subtotal and more....

[kongcom]

Với thao tác đơn giản : Nhấn chuột phải vào cell bất kì , chọn Creat List . Ok bạn đã sở hữu một công cụ cực mạnh của Excel
Bây giờ bạn thấy xuất hiện viền vàng và ngôi sao xanh phiá dưới cell .
Những gì kì diệu sảy ra ? Bạn nhập dữ liệu vào cell rồi nghịch nó một lúc đi và các bạn giúp mình trả lời các câu hỏi sau

1. Hãy cho biết vì sao khi kéo fill các dữ liệu thì Lists tự mở rộng ?
2. Vì sao lại có thêm hàng trong lists khi ta chọn 1 cell trong lists ?
3. Các công thức Dropdown cuối List từ đâu mà có ? Và tại sao nó toàn là SUBTOTAL ?
4. Tại sao không thay đổi công thức đó bằng formulabar được ?
5. Cơ chế nào tự động đặt tiêu đề cho các lists mỗi khi ta mở rộng lists
6. Cơ chế nào tự động dịch dữ liệu phiá dưới lists xuống mỗi khi ta thêm dữ liệu vào một hàng trống ?

 

[anhtuan1066]

Tôi chưa biết tại sao nó lại như vậy, nhưng cách đây khoãng 10 ngày tôi có phát hiện ra tính năng này và thấy rằng dùng nó đễ hổ trợ nhập liệu thì rất hay... Ít ra từ lâu mọi người cứ bị rắc rối ở chổ tạo dòng tổng ở cuối bãng tính và phải nhờ đến VBA nên quy trình thực hiện khá rắc rối, ko phải ai cũng làm dc... Có món đồ chơi này thì mọi việc dễ như trở bàn tay... Chắc chắn 1 điều rằng tính năng sẳn có luôn mạnh mẽ hơn những gì ta cố bắt chước...
Mến
ANH TUẤN

 

[BNTT]

Đây là chức năng tạo một LIST, sau này sang Excel 2007, thì bác Bill đã sửa lại thành TABLE.

Do vùng dữ liệu dạng này không phải là vùng "bình thường", nên những gì bạn hỏi ở trên, là những chức năng "mặc định" của LIST (hay là TABLE trong Excel 2007).

Những TABLE này sẽ luôn luôn có tên tiêu đề cột. Nếu như dữ liệu của bạn đã có sẵn vùng tiêu đề (hay là chuyển 1 database sang dạng TABLE), thì Excel sẽ lấy hàng trên cùng để làm hàng tiêu đề (dĩ nhiên là nó sẽ hỏi ý bạn), còn trong trường hợp chưa có (hoặc bạn không muốn) thì những tiêu đề cột sẽ là Column1, Column2, v.v... Và tự động sẽ tạo thêm cho bạn AutoFilter (bạn có thể tắt đi)

Về dòng công thức cuối cùng trong các cột, không phải nó toàn là SUBTOTAL đâu, nếu bạn nhấn vào cái mũi tên nhỏ nhỏ cạnh cái ô "tổng cộng" ấy thì bạn sẽ thấy thêm một số hàm khác. Với Excel 2003 thì số lượng hàm bị hạn chế, còn với Excel 2007 thì... tha hồ mà lựa chọn.

Cái hàm trong ô "tổng cộng" này đúng là không thể sửa trực tiếp như bình thường, tuy nhiên bạn có thể "tắt" nó đi, nghĩa là không dùng đến cái "tổng cộng" này nếu không thích.

Nói về LIST trong Excel 2003 hay TABLE trong Excel 2007 thì nhiều lắm, không thể gói gọn trong những bài nhỏ như vầy. Không dám hứa, nhưng nếu có thời gian (vì tôi đang còn mắc nợ diễn đàn 2 topic lớn chưa hoàn thành), tôi sẽ viết một bài cụ thể về vấn đề này.

 

[kongcom]

Về dòng công thức cuối cùng trong các cột, không phải nó toàn là SUBTOTAL đâu, nếu bạn nhấn vào cái mũi tên nhỏ nhỏ cạnh cái ô "tổng cộng" ấy thì bạn sẽ thấy thêm một số hàm khác

Nhưng cho dù mình chọn công thức nào đi nữa thì trên thanh công thức vấn chỉ là SUBTOTAL ... Chỉ thay đổi chút xíu trong ngoặc thôi
Hàm SUBTOTAL nó gồm cả dữ liệu dạng nhị phân là ý nghĩa gì vậy ? BNTT giải thích cho mình nghe với ? Lung bung quá đi mất .

 

[BNTT]

SUBTOTAL(function_num, ref1, ref1,...)

Trong đó, function_num là những số từ 1 đến 11 (includes hidden value - bao gồm cả những giá trị ẩn), và từ 101 đến 111 (ignores hidden value - bỏ qua những giá trị ẩn)

Giá trị ẩn ở đây thường là số liệu trong những hàng (row) đã được dấu đi, ví dụ như lọc bằng Autofilter.

Cách dùng hàm bằng các số (1 tương đương với 101, 2 tương đương 102, v.v...)
1 : AVERAGE
2 : COUNT
3 : COUNTA
4 : MAX
5 : MIN
6 : PRODUCT
7 : STDEV
8 : STDEVP
9 : SUM
10 : VAR
11 : VARP

=SUBTOTAL(102,$D$49) nghĩa là đếm trong dãy từ $D$4 đến D9 có bao nhiêu cell, bỏ qua (không đếm) nhừng cell đã bị Filter dấu đi.

Hàm SUBTOTAL() có dạng SUBTOTAL(function_num, ref1, ref2, ...)

Mấy con số mà bạn hỏi chính là cái function_num, mỗi con số đại diện cho một kiểu tính (cộng, trung bình, đếm, max, min...)

Và cũng chính vì vậy mà tôi mới nói là có nhiều công thức tính cho cái LIST (mà chúng là những công thức con của SUBTOTAL). Bạn nên tìm hiểu thêm về SUBTOTAL().

Còn tại sao chỉ có SUBTOTAL mà không có thứ khác, thì.. với Excel 2003 trở về trước, giống như hỏi tại sao con bò có 4 chân (hic). Còn nếu muốn có nhiều hàm hơn, ngoài SUBTOTAL, bạn chuyển sang Excel 2007, nhé!

 

[kongcom]

Vậy mới sáng tỏ vấn đề của List mà mình đang bàn tới .

=SUBTOTAL(102,$D$49) nghĩa là đếm trong dãy từ $D$4 đến D9 có bao nhiêu cell, bỏ qua (không đếm) nhừng cell đã bị Filter dấu đi

Nên khi ta cho ẩn đi một vài hàng có chứa một phần List thì kết quả thay đổi ngay lập tức trên hàng SubTotal

Còn nếu muốn có nhiều hàm hơn, ngoài SUBTOTAL, bạn chuyển sang Excel 2007, nhé!

Thế thì phải chuyển sang bản 12 thôi không thì thiệt lắm !

 

[anhtuan1066]

Oh... hàm SUBTOTAL nó luôn luôn như vậy chứ ko phải là khi nằm trong cái list ấy nó mới thế...
Bạn tham khảo bài "đánh số thứ tự sau khi Filter" đễ biết thêm về hàm này... Hay đấy!
Nó tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=3115
Và cũng chỉ duy nhất có SUBTOTAL mới làm dc điều này...
ANH TUẤN

 

[daibangkieuhanh]

Mình dùng Office XP, nên xin hỏi để Creat List thì vào đâu vậy

 

[anhtuan1066]

Mình dùng Office XP, nên xin hỏi để Creat List thì vào đâu vậy

Từ Office 2003 trở đi mới có tiện ích này... Bạn ra tiệm vi tính mua đĩa Office2003 về cài đi là vừa.. bạn sẽ thấy cái 2003 có những tính năng mạnh hơn 2002.. rồi 2007 lại càng mạnh... Tôi đang xài 2003 thấy cũng vừa tay, còn khi cần check file xlsx thì dùng Portable Office 2007
ANH TUẤN

 

[blk1008]

Tôi muốn làm một chương trình quản lý vật tư trên excel nhưng vì khả năng có hạn nên thưa thực hiện được. Các bác nào có cao kiến xin giúp đỡ

 

[CPH]

Các bác cho hỏi trong excel 2007 thì vào đâu để create list vậy?
Khi click chuột phải trong êxcl 2007 thì có Pick drop down lis, chức năng đó là jì vậy?
Mong các bạn chỉ giáo

 

[dvkien]

Pác nào biết cụ thể thì up lên để anh em còn học hỏi chứ, em cũng đang tìm hiểu cái này thấy nó rất hay nhưng chưa học hỏi được bao nhiêu cả, nhất là sao mà làm cho các công thức đó hiện ra ???? Bác nào biết thì úp sớm đi
cả nhà xin cảm ơn trước

 

[dvkien]

để tạo create list trong exel 2007 bạn vào Insert chon table đó, còn Pick drop down list thì em thua

 

[bebo021999]

để tạo create list trong exel 2007 bạn vào Insert chon table đó, còn Pick drop down list thì em thua

Có file này để đối chiếu giữa 2003 và 2007-2010, bạn xem thử