Cho đến bây giờ, đôi khi tôi mỉm cười nhớ lại một trong những khóa đào tạo thực địa đầu tiên của công ty cách đây 10 năm.
Hãy tưởng tượng: văn phòng không gian mở của văn phòng đại diện một công ty FMCG quốc tế, rộng như một sân bóng đá. Thiết kế sang trọng, thiết bị văn phòng đắt tiền, quy định về trang phục, người nước ngoài thủ thỉ trong các góc - chỉ vậy thôi 🙂 Tại một trong các phòng họp, tôi bắt đầu khóa đào tạo nâng cao kéo dài hai ngày về phiên bản Excel 2003 hiện tại cho 15 nhân viên chủ chốt của bộ phận kinh tế , cùng với thủ lĩnh của họ. Chúng tôi làm quen, tôi hỏi họ về các nhiệm vụ, vấn đề kinh doanh, tôi yêu cầu họ cho xem một số hồ sơ công việc điển hình. Họ cho thấy chiều dài hàng km dỡ hàng từ SAP, các tờ báo cáo mà họ thực hiện về việc này, v.v. Chà, đó là một điều quen thuộc – tôi hình dung ra các chủ đề và thời gian, điều chỉnh cho phù hợp với khán giả. Liếc qua khóe mắt, tôi nhận thấy một trong những người tham gia, trình bày một phần báo cáo của mình, kiên nhẫn kéo ô có công thức xuống dưới bằng dấu thập màu đen ở góc dưới bên phải trong vài nghìn dòng, sau đó bỏ qua phần cuối của cái bàn đang bay, kéo nó lại, v.v. Không thể chịu đựng được, tôi ngắt lời anh ta cuộn chuột quanh màn hình và hiển thị nhấp đúp vào chữ thập màu đen, giải thích về việc tự động hoàn thành cho đến hết điểm dừng.
Đột nhiên tôi nhận ra khán giả im lặng một cách đáng ngờ và mọi người đều nhìn tôi một cách kỳ lạ. Tôi lặng lẽ nhìn xung quanh mình bất cứ nơi nào có thể - mọi thứ đều ổn, tay và chân của tôi đã ở đúng vị trí, cánh của tôi đã được cài nút. Tôi thầm tua lại những lời cuối cùng của mình để tìm kiếm một điều khoản khủng khiếp nào đó - có vẻ như không có gì phạm tội cả. Sau đó, người trưởng nhóm lặng lẽ đứng dậy, bắt tay tôi và nói với vẻ mặt lạnh lùng: “Cảm ơn Nikolai. Việc đào tạo này có thể được hoàn thành.
Chà, tóm lại, hóa ra không ai trong số họ biết cách nhấp đúp vào dấu thập màu đen và tự động hoàn thành. Bằng cách nào đó trong lịch sử đã xảy ra rằng không có ai chỉ cho họ một điều đơn giản nhưng cần thiết như vậy. Cả khoa lôi công thức thủ công ra hàng nghìn dòng, tội nghiệp các bác. Và ở đây tôi. Cảnh dầu mỏ. Trưởng phòng sau đó yêu cầu rất nhiều không được tiết lộ tên công ty của mình cho bất kỳ ai 🙂
Vài lần sau, cũng có những tình huống tương tự, nhưng chỉ với từng người nghe – tất nhiên hầu hết bây giờ đều biết chức năng này.
Câu hỏi thì khác. Sau niềm vui đầu tiên khi làm chủ được một tính năng tuyệt vời như vậy, hầu hết người dùng đều hiểu rằng việc tự động sao chép công thức bằng cách nhấp đúp vào dấu thập màu đen (điểm đánh dấu tự động hoàn thành) có tất cả các mặt tích cực và tiêu cực:
- Việc sao chép không phải lúc nào cũng xảy ra ở cuối bảng. Nếu bảng không nguyên khối, tức là có các ô trống trong các cột liền kề, thì thực tế không phải tính năng tự động hoàn thành sẽ hoạt động cho đến cuối bảng. Rất có thể, quá trình sẽ dừng lại ở ô trống gần nhất trước khi kết thúc. Nếu có ô nào đó bị chiếm bởi thứ gì đó bên dưới cột thì tính năng tự động hoàn thành sẽ dừng chính xác trên chúng.
- Khi sao chép chiến lợi phẩm thiết kế tế bào, bởi vì Theo mặc định, không chỉ công thức mà cả định dạng cũng được sao chép. Để sửa, hãy nhấp vào nút tùy chọn sao chép và chọn Chỉ có giá trị (Điền không có định dạng).
- Không có cách nào nhanh chóng để kéo dài công thức một cách thuận tiện không phải ở dưới mà là ở bên phảingoại trừ việc kéo bằng tay. Click đúp vào chữ thập màu đen là vừa xuống.
Hãy thử khắc phục những thiếu sót này bằng một macro đơn giản.
Nhấn phím tắt bên trái Alt + F11 hoặc nút Visual Basic chuyển hướng nhà phát triển (Nhà phát triển). Chèn mô-đun trống mới qua menu Chèn - Mô-đun và sao chép văn bản của các macro này vào đó:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Nếu rng.Cells.Count > 1 Then n = rng.Cells(1).Row + rng.Rows. Đếm - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues End If End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion Nếu rng.Cells.Count > 1 Thì n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Loại: =xlFillValues Kết thúc nếu kết thúc phụ
Các macro như vậy:
- có thể điền không chỉ xuống (SmartFillDown) mà còn ở bên phải (SmartFillRight)
- không làm hỏng định dạng của các ô bên dưới hoặc bên phải – chỉ sao chép công thức (giá trị)
- các ô liền kề trống sẽ bị bỏ qua và quá trình sao chép diễn ra chính xác ở cuối bảng chứ không phải đến khoảng trống gần nhất trong dữ liệu hoặc ô được chiếm giữ đầu tiên.
Để thuận tiện hơn, bạn có thể gán phím tắt cho các macro này bằng nút Macro - Tùy chọn (Macro — Tùy chọn) ngay trên tab. nhà phát triển (Nhà phát triển). Bây giờ chỉ cần nhập công thức hoặc giá trị mong muốn vào ô đầu tiên của cột và nhấn tổ hợp phím đã chỉ định để macro tự động điền vào toàn bộ cột (hoặc hàng):
Làm đẹp.
PS Một phần vấn đề sao chép công thức vào cuối bảng đã được giải quyết trong Excel 2007 với sự ra đời của “bảng thông minh”. Đúng, chúng không phải lúc nào và không phải ở đâu cũng phù hợp. Và ở bên phải, Excel chưa bao giờ học cách tự sao chép.
- Macro là gì, cách sử dụng chúng, lấy mã Visual Basic ở đâu và dán nó vào đâu.
- Bảng thông minh trong Excel 2007-2013
- Sao chép công thức mà không cần dịch chuyển liên kết