Sử dụng Bảng dữ liệu sẽ xảy ra trong Excel để tạo ra một loạt các kết quả ngẫu nhiên. Ngay cả khi bạn có một công thức phức tạp là kết quả của một số bước, Bảng Dữ liệu sẽ cho phép bạn tạo ra hàng trăm câu trả lời cho mô hình mà không cần lặp lại 100 lần của mô hình.
Xem video
- Mục tiêu là tạo dữ liệu mẫu với sản phẩm; sản phẩm; sản phẩm; sản phẩm
- Mục tiêu là luôn có 2 sản phẩm trở lên, tối đa là 12 sản phẩm
- Lưu trữ danh sách sản phẩm trong danh sách tùy chỉnh để bạn có thể dễ dàng tạo một cột gồm các sản phẩm đơn lẻ
- Sử dụng RANDBETWEEN () có thể trả về các mục trùng lặp trong danh sách
- Sử dụng hàm RAND () để quyết định xem sản phẩm này có được bao gồm hay không
- Sử dụng TEXTJOIN () để nối các dấu không trống với dấu chấm phẩy ở giữa
- Bây giờ bạn có một kết quả, làm thế nào để bạn tạo ra nhiều kết quả
- Ngạc nhiên rằng một Bản sao và nhiều Giá trị Dán sẽ dán kết quả hiện tại của công thức
- Tăng tốc các giá trị dán bằng cách sử dụng F4 để làm lại
- Nhưng - một cách cực nhanh: Sử dụng Công cụ What-If và Bảng Dữ liệu với một ô trống làm Ô Nhập liệu Cột
- Cảm ơn giáo sư Simon Benninga về phương pháp này
Bản ghi video
Học Excel Từ, Tập 2155: Tạo Nhiều Kết quả Ngẫu nhiên Từ Một Công thức.
Chào. Chào mừng bạn trở lại netcast. Tôi là Bill Jelen. Ngoài ra, với podcast và viết sách, tôi viết một bài báo hàng tháng cho tạp chí Tài chính Chiến lược. Tôi đang làm bài viết vào tháng tới, nơi tôi sẽ chỉ cho họ cách sử dụng truy vấn nguồn để chia một cột của; dữ liệu được tách thành các hàng và tôi cần tạo một số dữ liệu giả cho việc đó và tại sao tôi không mở tệp từ tập 2097, tôi không biết. Tôi chỉ muốn tạo một số dữ liệu giả. Vì vậy, bất cứ nơi nào có từ 2 đến 12 sản phẩm trong một ô, và trong quá trình làm điều đó, tôi đã sử dụng rất nhiều thủ thuật từ podcast - nối văn bản; điền APPLE, BANANA, CHERRY; Đi bộ ngẫu nhiên; F4 To Redo - và tôi nghĩ rằng trên đường đi, tôi đã khám phá ra một số điều thú vị về cách tăng tốc quá trình này.
Bây giờ, trước tiên, sẽ thật tuyệt nếu tôi có thể tạo ra một công thức mảng khổng lồ có thể tạo ra dữ liệu đó. Tôi có thể đã sao chép công thức đó xuống nhưng tôi không thể tiếp cận bản sao của CTRL + SHIFT + ENTER và tôi chỉ đang tìm kiếm thứ gì đó đơn giản hơn vào sáng hôm đó. Tôi là một fan hâm mộ lớn của RANDBETWEEN. Tôi sử dụng RANDBETWEEN mọi lúc. Vì vậy, nếu chúng tôi có danh sách 12 sản phẩm và sau đó, tại đây, tạo ra một loạt câu trả lời bằng cách sử dụng RANDBETWEEN, do đó, chỉ số của A1 đến A12, yêu cầu một số ngẫu nhiên từ 1 đến 12, mỗi khi tôi nhấn F9, tôi nhận được danh sách các sản phẩm khác nhau và sau đó tôi muốn có một số lượng sản phẩm khác nhau trong mỗi sản phẩm, vì vậy ở đây, GIỮA từ 2 đến 7 hoặc 2 đến 12 hoặc bất kỳ giới hạn trên hoặc dưới nào, và sau đó sử dụng TAXJOIN, chức năng mới tuyệt vời đó trong Office 365, được phân tách bằng;, bỏ qua các khoảng trống, và sau đó chúng tôi 'đang đi từ E2 ngay tại đó đến bất kỳ đâu trong E2 đến E12 - dựa trên giá trị thứ 6 trong trường hợp này - sẽ tạo ra danh sách đó, được chứ? Nhưng lý do tôi không thích điều này là tôi liên tục nhấn F9, thấy chưa, tôi bắt đầu bị lặp lại và bạn biết đấy, người ta cho rằng đây là đơn đặt hàng đến từ một trang web và tại sao ai đó lại đặt hàng ELDEBERRY? ELDERBERRY không có ý nghĩa gì, phải không? Vì vậy, tôi không thích cơ hội nhận được DATE DATE ở đó. Tôi muốn có một danh sách duy nhất. Vì vậy, đây là những gì tôi quyết định rằng tôi sẽ làm.bị cáo buộc đây là đơn đặt hàng đến từ một trang web và tại sao ai đó lại đặt hàng ELDEBERRY? ELDERBERRY không có ý nghĩa gì, phải không? Vì vậy, tôi không thích cơ hội nhận được DATE DATE. Tôi muốn có một danh sách duy nhất. Vì vậy, đây là những gì tôi quyết định rằng tôi sẽ làm.bị cáo buộc đây là đơn đặt hàng đến từ một trang web và tại sao ai đó lại đặt hàng ELDEBERRY? ELDERBERRY không có ý nghĩa gì, phải không? Vì vậy, tôi không thích cơ hội nhận được DATE DATE ở đó. Tôi muốn có một danh sách duy nhất. Vì vậy, đây là những gì tôi quyết định rằng tôi sẽ làm.
Trước tiên, tôi sẽ tạo một danh sách gồm 12 sản phẩm và tôi đã ghi nhớ điều này như một danh sách tùy chỉnh, vì vậy tôi có thể tạo một danh sách các mặt hàng theo bảng chữ cái đẹp mắt, và sau đó tôi muốn từ 2 đến khoảng 7. trong số này, và vì vậy những gì tôi đã làm ở đây là tôi nói = IF the RAND. RAND là một hàm tuyệt vời tạo ra một số thập phân từ 0 đến 1 là <.6. Vì vậy, nói cách khác trong khoảng 60% trường hợp, tôi muốn bạn đưa sản phẩm đó qua đây vào cột B, nếu không thì không cho tôi “”. Tôi sẽ sao chép nó xuống. Điều đó sẽ làm đối với tôi là tạo một danh sách các sản phẩm. Sẽ không bao giờ lặp lại. Không có cơ hội lặp lại nào, và mỗi khi tôi nhấn F9, tôi sẽ nhận được một danh sách các sản phẩm khác nhau, và, vâng, có vẻ như chúng tôi đang nhận được đúng số lượng sản phẩm mỗi lần. (= IF (RAND () <0,6, A1, “”))
Tiếp theo, Tiêu đề biểu đồ; họ cung cấp cho chúng tôi hai vị trí khác nhau cho một biểu đồ-- Biểu đồ phía trên a Được rồi. Vì vậy, bây giờ chúng ta đã có, hàm mới trong Excel trong Office 365 là TEXTJOIN. Tôi thích điều này. Dấu phân cách sẽ là một; và sau đó bỏ qua trống. Nó không quan trọng. Tôi thực sự… vâng, không, nó thực sự không quan trọng ở đây. Đó là điều quan trọng. Chúng tôi sẽ bỏ qua trống rỗng. ĐÚNG, và đây là danh sách các sản phẩm như vậy của chúng tôi. Được chứ. Vì vậy, ở đó chúng tôi có danh sách sản phẩm của mình cho hàng đầu tiên, nhưng tôi cần tạo ra một loạt các sản phẩm này và đây là nơi chúng tôi thực sự đi đến vấn đề, vấn đề mà tôi đang cố gắng giải quyết trong trường hợp cụ thể này. (= TEXTJOIN (“;”, TRUE, B1: B12))
Bây giờ, nếu tôi chỉ sao chép công thức đó xuống, được thôi, nếu tôi lấy công thức gốc đó và đến đây và chỉnh sửa nó - chọn :, nhấn F4 để đảm bảo rằng tôi có tham chiếu tuyệt đối và sao chép nó xuống - bạn Tôi sẽ thấy rằng tôi kết thúc với tất cả các mục giống hệt nhau. Nó không phải là dữ liệu giả mạo rất thú vị, phải không? Vì vậy, điều đó sẽ không hiệu quả. Điều tôi cần làm là lấy kết quả của công thức này và tạo ra một loạt chúng, được chứ? (= TEXTJOIN (“;”, TRUE, $ B $ 1: $ B $ 12))
Vì vậy, ban đầu tôi đã làm điều này. Tôi đã thực hiện CONTROL + C, sau đó tôi đến đây và tôi sẽ dán các giá trị đặc biệt - hoặc tôi đoán là chỉ dán - và QUÁ GIÁ TRỊ như vậy, được rồi và điều gì hấp dẫn đối với tôi - và tôi đã nói về điều này một lần trên podcast trước đây và mọi người trong các bình luận trên YouTube, tất nhiên là điều đó sẽ hiệu quả; không - điều hấp dẫn đối với tôi là tôi đã sao chép ô C14, và vì vậy bạn sẽ nghĩ rằng, khi tôi sao chép C14, văn bản từ C14 sẽ được sao chép vào khay nhớ tạm, nhưng không phải vậy. Nó chỉ đến C14, phải không? Vì vậy, lần đầu tiên tôi dán, tôi nhận được CHERRY, DATE, ELDERBERRY, nhưng bây giờ bạn thấy rằng C14, những con kiến diễu hành, đã chuyển thành APPLE, CHERRY, FIG, và vì vậy tôi sẽ xuống đây và tôi ' tôi lại chuyển sang GIÁ TRỊ QUÁ KHỨ, và tôi luôn bị sốc khi nó thay đổi sang giá trị mới.
Được rồi, vì vậy, nếu tôi chỉ có thể CHỈ ĐỊNH GIÁ TRỊ QUÁ KHỨ, GIÁ TRỊ QUÁ KHỨ, GIÁ TRỊ QUÁ KHỨ, GIÁ TRỊ QUÁ KHỨ, nó sẽ tạo ra một câu trả lời mới mỗi lần. Lần này khi tôi QUÁ TRỊ GIÁ TRỊ, APPLE, CHUỐI, NGÀY, HÌNH, ICEBERG, JACKFRUIT, nhưng, nhìn này, thật rắc rối khi lấy chuột và đến đây và chọn QUÁ KHỨ và chọn GIÁ TRỊ. Vì vậy, tôi sẽ sử dụng chức năng REDO tuyệt vời - không phải UNDO, REDO - là F4, vì vậy F4, dán giá trị mới. Khi tôi nhấn F4, tôi sẽ nhận được trừ BANANA, DATE, ELDERBERRY, LIME. Vì vậy, nó chỉ đơn giản. F4, MŨI TÊN XUỐNG, F4, MŨI TÊN XUỐNG, F4, MŨI TÊN XUỐNG, được rồi, và cuộc sống thật tuyệt. Ở đó, tôi có đủ dữ liệu giả cho bài báo, được rồi, nhưng ngay cả điều đó cũng phức tạp, được chứ?
Vì vậy, phương pháp mà tôi học được từ một người bạn tốt của tôi hiện đã qua đời - giáo sư Simon Benninga đã dạy tôi điều này - nếu chúng ta có một mô hình - và đây về cơ bản là một mô hình - đó là sử dụng RAND hoặc RANDBETWEEN và tạo ra một kết quả, những gì bạn có thể làm là bạn có thể có nhiều phiên bản của kết quả đó xảy ra, được rồi, và chúng tôi phải bắt đầu từ ô bên trái kết quả của mô hình của chúng tôi, chọn ô đó và ô chứa công thức của bạn, sau đó , tuy nhiên nhiều bạn muốn - giả sử tôi cần 100 trong số này hoặc 132 trong số này - chỉ cần sao chép hoặc chọn tất cả các cách và chúng ta sẽ đến đây với tab DỮ LIỆU, tab DỮ LIỆU, GÌ-NẾU PHÂN TÍCH, BẢNG DỮ LIỆU, được chứ?
Bây giờ, tôi sử dụng điều này mọi lúc để chỉ cách tạo nhiều kịch bản, nhưng trong trường hợp này, chúng tôi không thực sự có bất kỳ thứ gì cho ROW INPUT CELL. Đối với TẾ BÀO ĐẦU VÀO CỘT, chỉ cần chọn bất kỳ ô trống nào - không quan trọng ô đó là ô nào - và điều này sẽ chạy mô hình này 132 lần, mỗi lần về cơ bản nhấn F9 để tạo các giá trị ngẫu nhiên mới như vậy, hãy nhấp vào OK và, bam, và nó hoạt động. Tôi hoàn toàn thích điều đó.
Bây giờ, đây là trực tiếp. Mỗi khi tôi nhấn F9, tôi nhận được một bộ 132 mới trong số này. Vì vậy, chỉ cần sao chép - CONTROL + C - và xuất hiện ở đây, QUA CÁC GIÁ TRỊ ĐẶC BIỆT, và chúng tôi có danh sách sản phẩm giả mạo và chúng tôi đã sẵn sàng thực hiện những gì đã có trong tập 2097: chia một cột x; x; x; thành hàng. Thực sự khuyên bạn nên xem video đó, đó là một video tuyệt vời, hoặc số tháng 11, số tháng 11 năm 2017, của tạp chí Strategic Finance. Nó sẽ được phát hành trực tuyến vào đầu tháng 11.
Ổn thỏa. Vì vậy, tất cả các phương pháp này đều có trong cuốn sách Power Excel With, ấn bản năm 2017. Nhấp vào tôi ở góc trên cùng bên phải để kiểm tra.
Recap of what we talked about today: the goal is to create a sample data set with ; separated products; always two or more products up to a max of 12; so I stored a list of products in a custom list so you can easily generate a single column of products; using RANDBETWEEN, that would have worked but we have duplicates in the list so I'm using the RAND function to say is this product in or not; then the TEXTJOIN function to concatenate the non-blanks with ;s in between; but then the big question, now that you have one result, how do you make multiple results using the RAND function; well, that's surprising that one copy and then multiple paste values will give you different answers because it's always recalculating after each paste; and you could speed that using F4 to redo the paste values. Not a bad way to go, but the superfast way, thanks to Professor Benninga, use the WHAT-IF tools and a data table with an empty cell as the COLUMN INPUT CELLS and you'll be able to generate hundreds of random results very, very quickly. Alright, there you go.
Chà, này. Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2155.xlsm
