Tìm ngày - Mẹo Excel

Mục lục

Xem video

Một số câu hỏi đi kèm khá khó. Hôm nay, chúng ta có một cột ô. Mỗi ô có một số từ, sau đó là ngày tháng, sau đó là một số từ khác. Mục tiêu là kéo phần ngày của văn bản đó sang một cột mới. Đây là một tập phim đấu tay đôi lấy ý tưởng từ Bill và Mike.

Xem video

Cách tiếp cận siêu rộng của Bill:
Đặt tất cả 12 tháng vào các cột riêng biệt
Sử dụng chức năng TÌM để xem tháng này có ở văn bản gốc không
Để tìm vị trí bắt đầu tối thiểu, hãy sử dụng = AGGREGATE (5,6,…
Một vài công thức bổ sung để tìm kiếm vị trí số 2 hoặc 3 trước tháng
Cách tiếp cận của Mike:
Sử dụng SEARCH thay vì FIND. Tìm có phân biệt chữ hoa chữ thường, Tìm kiếm thì không.
Tạo hoạt động mảng đối số hàm bằng cách chỉ định B13: B24 là Find_Text.
Công thức trả về lỗi #VALUE! Lỗi, nhưng nếu bạn nhấn F2, F9, bạn sẽ thấy rằng nó đang trả về một mảng.
13 hàm đầu tiên trong AGGREGATE không thể xử lý một mảng, nhưng các hàm 14-19 có thể xử lý một mảng.
5 = MIN và 15 = SMALL (, 1) tương tự nhau, nhưng SMALL (, 1) sẽ hoạt động với một mảng.
LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX và AGGREGATE có thể xử lý các đối số mảng hàm mà không cần Ctrl + Shift + Enter
Mike thông minh hơn khi tìm xem 2 ký tự trước Bắt đầu có phải là một số không, rồi lấy 3 ký tự trước đó. Không gian thừa được loại bỏ bởi TRIM ()
Để lấy Tiêu đề, hãy sử dụng hàm SUBSTITUTE để loại bỏ văn bản Ngày trong cột C

Bản ghi video

Bill Jelen: Này, chào mừng trở lại. Đã đến lúc cho một Dueling Excel Podcast khác. Tôi là Bill Jelen đến từ. Tôi sẽ được tham gia bởi Mike Girvin từ Excel Is Fun.

Đây là Duel # 170: Tìm Ngày của chúng ta

Này, chào mừng mọi người trở lại. Tôi đã có một câu hỏi tuyệt vời ở đây và tôi không thể giải quyết nó. Ít nhất thì tôi không thể giải quyết nó một cách dễ dàng nên tôi đã đến gặp Mike Girvin và tôi nói, "Mike, này, bạn có cách nào để làm điều này không?" Anh ấy nói, “Vâng, tôi có một cách để làm điều đó. Hãy đấu tay đôi. "

Vì vậy, ai đó trên YouTube đã gửi dữ liệu này và mọi ô nói chung đều có một cái gì đó giống như tiêu đề tài liệu theo sau là ngày. Họ muốn chia dữ liệu này thành tiêu đề tài liệu: nó là gì, thứ là gì và sau đó là ngày tháng. Nhưng ngày tháng là hoàn toàn xấu xa. Như đây, đó là ngày 20 tháng Giêng; nhưng ở dưới đây, có những thứ mà ngày có thể là sau phòng giam, ngày 9 tháng 4. Được rồi, và dù theo cách nào thì chúng tôi cũng muốn tìm nó. Và đôi khi có hai cuộc hẹn hò và điều này hoàn toàn kinh khủng và đó chỉ là một tình huống hỗn hợp của các cuộc hẹn hò và nếu có thể, thậm chí không có ngày nào được hiển thị cả. Vì vậy, đây là nỗ lực của tôi. Ở phía bên tay phải, tôi sẽ đặt những thứ tôi đang tìm kiếm. Điều tôi thực sự thích ở đây là họ không bao giờ viết tắt tên tháng. Tôi thật sự,thực sự đánh giá cao điều đó. Vì vậy, hãy nhập vào tháng Giêng và tôi sẽ kéo ra đây đến tháng 12 như vậy, và đối với mỗi ô tôi muốn biết, chúng ta có thể tìm = TÌM tháng 1 đó không. Vì vậy, tôi sẽ nhấn F4 một, hai lần để khóa nó xuống chỉ một hàng, trong văn bản đằng kia ở Cột A, như vậy. Tôi sẽ nhấn F4 một, hai, ba lần để khóa nó vào cột, được rồi. Và đây, nó cho chúng ta biết rằng tháng Giêng được tìm thấy ở Vị trí 32 và trong 11 tháng còn lại, nó sẽ cho chúng ta biết rằng nó hoàn toàn không được tìm thấy. Nói cách khác, chúng tôi đang gặp lỗi Giá trị. Những gì tôi cần làm ở đó là tôi cần tìm, tôi cần tìm giá trị nhỏ nhất bỏ qua tất cả các lỗi giá trị. Vì vậy, hãy bỏ ẩn công thức nhỏ này ở đây = AGGREGATE và hãy xây dựng công thức này ngay từ đầu, = AGGREGATE, những gì chúng ta muốn là MIN nên đó là số 5,và sau đó Bỏ qua các giá trị lỗi số 6 dấu phẩy và sau đó là tất cả các ô này từ tháng Giêng đến tháng Mười Hai. Và những gì sẽ cho chúng ta biết là sẽ cho chúng ta biết tháng xảy ra ở đâu. Và trong trường hợp này, chúng ta sẽ nhận được 0, giả sử tháng không xảy ra chút nào.

Được rồi, hãy hiện phần còn lại của phần này. Vì vậy, để xử lý tình huống ở đây chúng ta có 20 tháng 1 hoặc 1 tháng 11, tôi đã nói điều đầu tiên tôi phải làm là tôi sẽ xem xét nơi bắt đầu của tháng đó và quay lại hai ô, hai ô, hai ký tự , hai ký tự. Và xem nếu đó là một con số, không phải như vậy. Đó là cột của tôi ở đây có tên là Adjust2. Điều chỉnh2. Và đây là những gì chúng ta sẽ làm. Tôi sẽ nói, lấy MID của A2 bắt đầu nó ở vị trí trong G2-2 với độ dài là 1, thêm 0 vào nó và hỏi, đó có phải là một số hay không? Được rồi, đó là một con số. Và sau đó, chúng tôi cũng sẽ tìm kiếm tình huống mà đó là ngày có 2 chữ số, tức là ngày 20 tháng 1. Vì vậy, đó được gọi là Điều chỉnh3, quay lại 3 ký tự từ nơi. Vì vậy, có Vị trí, quay lại ba ký tự với độ dài là 1, thêm 0 vào nó và xem liệu điều đó có 'sa số, được không? Sau đó, chúng tôi sẽ điều chỉnh và Nơi điều chỉnh cho biết IF. NẾU trường hợp kỳ lạ này là 0, chúng tôi sẽ đặt một giá trị thực sự lớn 999; nếu không, chúng ta sẽ đi từ G2 và quay lại 3, nếu Điều chỉnh 3 là Đúng hoặc quay lại 2 nếu Điều chỉnh2 là Đúng hoặc nếu không có điều nào trong số đó là Đúng, thì Vị trí sẽ là nơi bắt đầu của tháng. Được rồi, bây giờ chúng tôi biết rằng Vị trí được Điều chỉnh, chúng tôi sẽ nhấp đúp để sao chép điều đó xuống. Vâng, bây giờ, nó thực sự dễ dàng. Chúng ta sẽ chỉ - đối với Tiêu đề, chúng ta sẽ nói rằng hãy rời khỏi A2, chúng ta muốn có bao nhiêu ký tự. Chúng tôi muốn D2-1 vì đó là -1 để loại bỏ khoảng trống ở cuối. Mặc dù tôi đoán TRIM cũng đang loại bỏ không gian ở cuối.NẾU trường hợp kỳ lạ này là 0, chúng tôi sẽ đặt một giá trị thực sự lớn 999; nếu không, chúng ta sẽ đi từ G2 và quay lại 3, nếu Điều chỉnh 3 là Đúng hoặc quay lại 2 nếu Điều chỉnh2 là Đúng hoặc nếu không có điều nào trong số đó là Đúng, thì Vị trí sẽ là nơi bắt đầu của tháng. Được rồi, bây giờ chúng tôi biết rằng Vị trí được Điều chỉnh, chúng tôi sẽ nhấp đúp để sao chép nó xuống. Vâng, bây giờ, nó thực sự dễ dàng. Chúng ta sẽ chỉ - đối với Tiêu đề, chúng ta sẽ nói rằng hãy rời khỏi A2, chúng ta muốn có bao nhiêu ký tự. Chúng tôi muốn D2-1 vì đó là -1 để loại bỏ khoảng trống ở cuối. Mặc dù tôi đoán TRIM cũng đang loại bỏ không gian ở cuối.NẾU trường hợp kỳ lạ này là 0, chúng tôi sẽ đặt một giá trị thực sự lớn 999; nếu không, chúng ta sẽ đi từ G2 và quay lại 3, nếu Điều chỉnh 3 là Đúng hoặc quay lại 2 nếu Điều chỉnh2 là Đúng hoặc nếu không có điều nào trong số đó là Đúng, thì Vị trí sẽ là nơi bắt đầu của tháng. Được rồi, bây giờ chúng tôi biết rằng Vị trí được Điều chỉnh, chúng tôi sẽ nhấp đúp để sao chép điều đó xuống. Vâng, bây giờ, nó thực sự dễ dàng. Chúng ta sẽ chỉ - đối với Tiêu đề, chúng ta sẽ nói rằng hãy rời khỏi A2, chúng ta muốn có bao nhiêu ký tự. Chúng tôi muốn D2-1 vì đó là -1 để loại bỏ khoảng trống ở cuối. Mặc dù tôi đoán TRIM cũng đang loại bỏ không gian ở cuối.hoặc nếu không có câu nào là Đúng, thì Vị trí sẽ là nơi bắt đầu của tháng. Được rồi, bây giờ chúng tôi biết rằng Vị trí được Điều chỉnh, chúng tôi sẽ nhấp đúp để sao chép điều đó xuống. Vâng, bây giờ, nó thực sự dễ dàng. Chúng ta sẽ chỉ - đối với Tiêu đề, chúng ta sẽ nói rằng hãy rời khỏi A2, chúng ta muốn có bao nhiêu ký tự. Chúng tôi muốn D2-1 vì đó là -1 để loại bỏ khoảng trống ở cuối. Mặc dù tôi đoán TRIM cũng đang loại bỏ không gian ở cuối.hoặc nếu không có câu nào là Đúng, thì Vị trí sẽ là nơi bắt đầu của tháng. Được rồi, bây giờ chúng tôi biết rằng Vị trí được Điều chỉnh, chúng tôi sẽ nhấp đúp để sao chép điều đó xuống. Vâng, bây giờ, nó thực sự dễ dàng. Chúng ta sẽ chỉ - đối với Tiêu đề, chúng ta sẽ nói rằng hãy rời khỏi A2, chúng ta muốn có bao nhiêu ký tự. Chúng tôi muốn D2-1 vì đó là -1 để loại bỏ khoảng trống ở cuối. Mặc dù tôi đoán TRIM cũng đang loại bỏ khoảng trống ở cuối.s -1 là để loại bỏ khoảng trống ở cuối. Mặc dù tôi đoán TRIM cũng đang loại bỏ không gian ở cuối.s -1 là để loại bỏ khoảng trống ở cuối. Mặc dù tôi đoán TRIM cũng đang loại bỏ không gian ở cuối.

Và sau đó cho ngày, chúng tôi sẽ sử dụng MID. MID cho- MID của A2 bắt đầu tại Vị trí được Điều chỉnh trong D2 và đi ra ngoài 50 hoặc bất kỳ điều gì bạn nghĩ có thể có, sau đó là hàm TRIM, và chúng tôi sẽ nhấp đúp để sao chép điều đó xuống.

Được rồi, tôi đã nói lý do tôi liên hệ với Mike, tôi tự hỏi liệu có cách nào để tôi có thể thay thế 12 cột này bằng một biểu mẫu duy nhất hay không, thực ra là 13 cột này bằng một biểu mẫu duy nhất. Có cách nào tôi có thể thực hiện việc này bằng cách sử dụng công thức Mảng không? Và Mike, tất nhiên, đã viết cuốn sách tuyệt vời đó, Ctrl + Shift + Enter, về công thức Mảng. Và tôi đã thử một vài điều khác nhau và trong tâm trí của tôi, không có cách nào có thể làm được. Được rồi, nhưng bạn biết đấy, chúng ta hãy hỏi chuyên gia. Vì vậy, Mike, hãy xem những gì bạn có.

Mike Girvin: Cảm ơn ,. Này, và nói về chuyên gia, điều này được thực hiện khá chuyên nghiệp. Bạn đã sử dụng FIND, AGGREGATE, ISNUMBER (MID. Bây giờ, khi bạn gửi câu hỏi này cho tôi, tôi đã tiếp tục và giải quyết nó và thật ngạc nhiên là giải pháp của tôi tương tự như giải pháp của bạn.

Được rồi, tôi sẽ xem qua trang tính này ở đây. Tôi sẽ bắt đầu với việc tìm ra vị trí bắt đầu trong chuỗi văn bản này cho mỗi tháng cụ thể. Bây giờ cách tôi sẽ làm là tôi sẽ sử dụng chức năng TÌM KIẾM này. Bây giờ, bạn sử dụng FIND, tôi sử dụng SEARCH. Trên thực tế có lẽ FIND tốt hơn trong tình huống này vì FIND phân biệt chữ hoa chữ thường, SEARCH thì không. Bây giờ thông thường những gì chúng ta làm với TÌM hoặc TÌM KIẾM, tôi nói, này, hãy TÌM, tháng 1, dấu phẩy trong chuỗi văn bản lớn hơn này, đó là cách chúng ta thường sử dụng TÌM KIẾM Ctrl + Enter và nó đếm trên ngón tay: một, hai, ba , bốn năm. Nó cho biết ký tự thứ 32 là nơi nó tìm thấy tháng Giêng.

Now, instead of doing it in many cells across the columns, I'm going to hit F2, come up here and the FIND_TEXT. Notice we gave it 1 item, SEARCH gave us 1 answer. But if I highlight the entire column of month names, now instead of a single item I put many items in there. This is a Function Argument. We're putting an array of items in and so that means we're doing a Function Argument Array operation. Any time you do that, you tell the function, hey, give me 12 answers, 1 for each month. Now this will deliver an array so if I try to Enter this and copy down it's not going to work.

Well, let's go down to any particular cell, F2 and then F9 to look that yes, in fact, it is delivering an array, and look at that. It looks like I F2 up here, forgot to lock it. So I'm going to click on that and F4, Ctrl+Enter, double click and send it down, F2, F9. There we go, that's that array. There's exactly 12 answers and there's the 34 and the 55. Now, from this array, since we always want the actual first month, not the second month, we want whatever the MIN is because those are number of characters in from the left. So I'm going to click Escape, come up to the top F2. I'm going to use the AGGREGATE function. Hey, we would like to use AGGREGATE 5 but no matter how hard you try if you have an array operation and we do here, if you try to put any function 1 to 13, it just doesn't work. But no problem, we have SMALL, so number 15 comma. Any one of those functions 14 to 19, they understand array operations. And once you select 14 or above, this is the screen tip you're working off, not this bottom one with the references. Alright, comma.

The second options here we want to Ignore errors, comma. That number 6 will then tell AGGREGATE to look through here and ignore the errors. It will only see the numbers. And this is one of five functions in Excel: LOOKUP some product, CHI SQUARE TEST, AGGREGATE, and INDEX that actually have a special argument that can handle Array operations without doing any special key stroke. So there is the Array, comma, and then for K we simply put A1. That's our way of getting them in. Close parentheses, Ctrl+Enter, double click and send it down. And so that tells us the position where it found the first month name from this list.

Now, we'll deal with the NUM error at the very end in our final formula. Now, we are going to have to take these and notice that sometimes there's a number before the month and sometimes, like down here in December, there is not. So I'm going to do the same thing did. I'm going to go back two characters and check whether it is a letter or in this case a number =MID, there’s the text, comma, the starting position. Well, I want to start at 32 in this case and -2 to go back to and comma. I get exactly one character. Now, if I close parenthesis MID LEFT RIGHT they all deliver text, double click and send it down and we want to check if it's a number. So watch this, the whole column is highlighted. Active cell at the top, I'm going to hit F2. We could do any Math operation to convert text numbers back to number so I'm going to add 0, Ctrl+Enter to populate this edited formula down through the column. Ctrl+Enter. Now, we can ask the question: Is the returned item a number? F2. So now I say ISNUMBER, close parenthesis, Ctrl+Enter. So now we have a pattern of Trues and Falses. Now, remember we need to get the starting position and for 32 we're definitely going to have to subtract 3 and start at that 20 but notice down here, we don't want to subtract any. So our logical test if I hit F2, that will simply be put into the IF Logical Test Argument. If that comes out True comma then I want to jump back 3 comma. Otherwise I want to jump back 0, close parenthesis, Ctrl+Enter to populate that all the way down. Now we can take this number and subtract the number over here to give us our starting position. Active cell at the top F2, I'm putting this inside of MID. There's the text, comma. And can you believe it? All of this to get the start number. So I'm going to click on that B2 and subtract our IF, come to the end comma and I'm just going to put a big number in here, 100, some big number big enough to get all the way to the end, close parenthesis and Ctrl+Enter to populate that all the way down. It looks like we have some extra spaces and that makes sense because right here we went back three, so no problem. Active cell at the top, F2, I'm going to use the haircut function, the diet function. No, the TRIM function to remove extra spaces except for single spaces between words. Come to the end, close parenthesis, Ctrl+Enter to populate that all the way down.

Now, I have the date, oh, except for the NUM. Now, I could come to the top and use IF error but then it would run all of these plus that cell right there and for a small data set, it doesn't matter at all; but, with the goal of efficiency, I'm going to say IF(ISNUMBER and I'm going to click on that cell, that way close parenthesis, comma. The trigger for whether we run the formula is only based on that instead of the entire formula. If that comes out True, we want to run the formula, comma. Otherwise, double quote double quote. That zero link text string will show nothing. Ctrl+Enter, double click and send it down. And now, all we need to do is get the Title. Well, I already have the text that I don't want in here so I'm going to use the SUBSTITUTE function. SUBSTITUTE, there's the text, comma. The old text, it's that right there, comma, the new text. Hey, I want to take that and SUBSTITUTE in nothing. There's our zero link text string, Ctrl+Enter, double click and send it down.

Now, I'm going to come over here to column B, right click, Hide and there we go. Alright, throw it back to.

Bill Jelen: Hey, Mike, that is brilliant and I know exactly, exactly where I went wrong. Right here in row 12 when the formula returned the #VALUE error, you pressed F2, F9 to see that it's returning an array. When I got the #VALUE error, I just swore a little bit and said, why isn't this working? Never thought of pressing F2, F9, alright. Also, like that, of course, MIN and SMALL(,1) are the same but the difference is SMALL(,1) will work with an array in the AGGREGATE function. That was a beautiful, beautiful trick. And then, I went through that whole hassle to look at 2 characters before and 3 characters before. You were smart enough to say, “Hey, we're going to go 2 characters before and if so, go back 3 characters.” Worst case you get a space for that extra space and eliminated by the TRIM. And then the cherry on top, using SUBSTITUTE function to get rid of the Date text in column C. What a brilliant, brilliant way to go, alright.

Vì vậy, tôi muốn cảm ơn mọi người đã ghé qua. Chúng tôi sẽ gặp lại bạn vào lần tới với một Podcast Dueling Excel khác và Excel Is Fun.

Tải tập tin

Tải xuống tệp mẫu tại đây: Duel180.xlsm