Sebilangan soalan yang timbul cukup sukar. Hari ini, kita mempunyai lajur sel. Setiap sel mempunyai beberapa perkataan, kemudian tarikh, dan beberapa perkataan lagi. Tujuannya adalah untuk menarik bahagian tarikh teks itu ke lajur baru. Ini adalah episod duel dengan idea dari Bill dan Mike.
Tonton Video
- Pendekatan Bill yang sangat luas:
- Masukkan semua 12 bulan dalam ruangan yang berasingan
- Gunakan fungsi FIND untuk melihat apakah bulan ini ada dalam teks asal
- Untuk mencari kedudukan permulaan minimum, gunakan = AGGREGATE (5,6,…
- Beberapa formula tambahan untuk mencari kedudukan nombor 2 atau 3 sebelum bulan tersebut
- Pendekatan Mike:
- Gunakan CARIAN dan bukannya CARI. Cari peka huruf besar-kecil, Carian tidak.
- Buat operasi array argumen fungsi dengan menentukan B13: B24 sebagai Find_Text.
- Formula mengembalikan # NILAI! Ralat, tetapi jika anda menekan F2, F9, anda akan melihat bahawa ia mengembalikan array.
- 13 fungsi pertama dalam AGGREGATE tidak dapat menangani array, tetapi fungsi 14-19 dapat menangani array.
- 5 = MIN dan 15 = KECIL (, 1) serupa, tetapi KECIL (, 1) akan berfungsi dengan tatasusunan.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX, dan AGGREGATE dapat menangani argumen array fungsi tanpa Ctrl + Shift + Enter
- Mike lebih pintar dengan melihat apakah 2 watak sebelum Mula adalah nombor, dan kemudian mengambil 3 watak sebelumnya. Ruang tambahan dihapuskan oleh TRIM ()
- Untuk mendapatkan Judul, gunakan fungsi SUBSTITUTE untuk menyingkirkan teks Tarikh di lajur C
Transkrip Video
Bill Jelen: Hei, selamat datang kembali. Sudah tiba masanya untuk Dueling Excel Podcast yang lain. Saya Bill Jelen dari. Saya akan disertai oleh Mike Girvin dari Excel Is Fun.
Ini adalah Duel # 170: Mencari Tarikh
Hei, selamat datang kembali semua orang. Saya mempunyai soalan hebat di sini dan saya tidak dapat menyelesaikannya. Sekurang-kurangnya saya tidak dapat menyelesaikannya dengan mudah jadi saya pergi ke Mike Girvin dan saya berkata, "Mike, hei, adakah anda mempunyai cara untuk melakukan ini?" Dia berkata, “Ya, saya ada cara untuk melakukannya. Mari kita lakukan Duel. "
Oleh itu, seseorang di YouTube menghantar data ini dan setiap sel secara amnya mempunyai sesuatu seperti tajuk dokumen diikuti dengan tarikh. Mereka ingin membahagikan data ini ke dalam tajuk dokumen: apa itu, apa masalahnya dan kemudian tarikhnya. Tetapi tarikhnya benar-benar jahat. Seperti di sini, 20 Januari; tetapi di sini, ada perkara di mana tarikh mungkin selepas sel, 9 April. Baiklah, dan tidak kira dengan cara mana sekalipun kita mahu mencarinya. Dan kadang-kadang ada dua tarikh dan ini benar-benar mengerikan dan ini adalah keadaan tarikh yang bercampur-campur dan mungkin, bahkan tidak ada tarikh yang muncul, baiklah. Jadi, inilah percubaan saya. Di sebelah kanan, saya akan meletakkan perkara yang saya cari. Yang sangat saya gemari di sini adalah mereka tidak pernah menyingkat nama bulan. Saya betul-betul,sangat menghargai itu. Oleh itu, taipkan pada bulan Januari dan saya akan menyeret ke sini hingga Disember seperti itu, dan untuk setiap sel yang saya ingin tahu boleh kita temui = MENCARI Januari itu. Jadi saya akan menekan F4 satu, dua kali untuk menguncinya hanya berturut-turut, dalam teks di sana di Lajur A, seperti itu. Saya akan tekan F4 satu, dua, tiga kali untuk menguncinya ke lajur, baiklah. Dan di sini, ia memberitahu bahawa Januari dijumpai di Kedudukan 32 dan selama 11 bulan lagi, ia akan memberitahu kita bahawa ia tidak dijumpai sama sekali. Dengan kata lain, kita mendapat ralat Nilai sekarang. Apa yang perlu saya lakukan di sana adalah saya perlu mencari, saya perlu mencari nilai minimum mengabaikan semua kesalahan nilai. Oleh itu, tutup formula kecil ini di sini = AGGREGATE dan mari kita buat ini dari awal lagi, = AGGREGATE, apa yang kita mahukan adalah MIN jadi nombor 5,dan kemudian Abaikan nilai ralat nombor 6 koma dan kemudian semua sel ini dari Januari hingga Disember. Dan apa yang akan diberitahu kepada kita adalah yang akan memberitahu kita di mana bulan itu berlaku. Dan dalam kes ini, kita akan mendapat 0, katakan bulan itu sama sekali tidak berlaku.
Baiklah sekarang, mari kita batalkan semua ini. Oleh itu, untuk menangani keadaan di mana kita ada 20 Januari atau 1 November, saya mengatakan perkara pertama yang akan saya lakukan ialah saya akan melihat dari mana bulan itu bermula dan mengembalikan dua sel, dua sel, dua watak , dua watak. Dan lihat apakah itu nombor, tidak begitu. Itulah ruangan saya di sini disebut, Adjust2. Laraskan2. Dan inilah yang akan kami lakukan. Saya akan katakan, ambil MID A2 mulai dari mana di G2-2 untuk panjang 1, tambahkan 0 padanya dan tanyakan, adakah itu nombor atau tidak? Baiklah, begitu juga nombor. Dan kemudian, kami juga akan melihat keadaan di mana ia adalah tarikh 2 digit, sehingga 20 Januari. Jadi yang disebut Adjust3, kembali 3 watak dari mana. Jadi ada di mana, kembali tiga watak untuk panjang 1, tambahkan 0 padanya, dan lihat apakah itu 'nombor nombor, baiklah? Kemudian kita akan menyesuaikan diri dan di mana Diselaraskan mengatakan JIKA. JIKA kes ini pelik adalah 0, kita akan meletakkan nilai yang sangat besar 999; jika tidak, kita akan pergi dari G2 dan akan kembali ke 3, jika Adjust3 adalah Benar atau kembali 2 jika Adjust2 adalah Benar, atau jika tidak ada yang benar, Tempat akan menjadi tempat bulan bermula. Baiklah, setelah kita mengetahui bahawa Tempat Diselaraskan, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, ia sangat mudah. Kami hanya akan - untuk Tajuk, kita akan mengatakan di sebelah kiri A2, berapa banyak watak yang kita mahukan. Kami mahukan D2-1 kerana itulah -1 adalah menyingkirkan ruang pada akhirnya. Walaupun saya rasa TRIM juga menyingkirkan ruang di hujungnya.JIKA kes ini pelik adalah 0, kita akan meletakkan nilai yang sangat besar 999; jika tidak, kita akan pergi dari G2 dan akan kembali ke 3, jika Adjust3 adalah Benar atau kembali 2 jika Adjust2 adalah Benar, atau jika tidak ada yang benar, Tempat akan menjadi tempat bulan bermula. Baiklah, setelah kita mengetahui bahawa Tempat Diselaraskan, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, ia sangat mudah. Kami hanya akan - untuk Tajuk, kita akan mengatakan di sebelah kiri A2, berapa banyak watak yang kita mahukan. Kami mahukan D2-1 kerana itulah -1 adalah menyingkirkan ruang pada akhirnya. Walaupun saya rasa TRIM juga menyingkirkan ruang di hujungnya.JIKA kes ini pelik adalah 0, kita akan meletakkan nilai yang sangat besar 999; jika tidak, kita akan pergi dari G2 dan akan kembali ke 3, jika Adjust3 adalah Benar atau kembali 2 jika Adjust2 adalah Benar, atau jika tidak ada yang benar, Tempat akan menjadi tempat bulan bermula. Baiklah, setelah kita mengetahui bahawa Tempat Diselaraskan, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, ia sangat mudah. Kami hanya akan - untuk Tajuk, kita akan mengatakan di sebelah kiri A2, berapa banyak watak yang kita mahukan. Kami mahukan D2-1 kerana itulah -1 adalah menyingkirkan ruang pada akhirnya. Walaupun saya rasa TRIM juga menyingkirkan ruang di hujungnya.atau jika tidak ada yang benar, Tempat akan menjadi tempat bulan bermula. Baiklah, setelah kita mengetahui bahawa Tempat Diselaraskan, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, ia sangat mudah. Kami hanya akan - untuk Tajuk, kita akan mengatakan di sebelah kiri A2, berapa banyak watak yang kita mahukan. Kami mahukan D2-1 kerana itulah -1 adalah menyingkirkan ruang pada akhirnya. Walaupun saya rasa TRIM juga menyingkirkan ruang di hujungnya.atau jika tidak ada yang benar, Tempat akan menjadi tempat bulan bermula. Baiklah, setelah kita mengetahui bahawa Tempat Diselaraskan, kita akan klik dua kali untuk menyalinnya. Nah, hei sekarang, ia sangat mudah. Kami hanya akan - untuk Tajuk, kita akan mengatakan di sebelah kiri A2, berapa banyak watak yang kita mahukan. Kami mahukan D2-1 kerana itulah -1 adalah menyingkirkan ruang pada akhirnya. Walaupun saya rasa TRIM juga menyingkirkan ruang di hujungnya.s -1 adalah untuk membuang ruang di hujungnya. Walaupun saya rasa TRIM juga menyingkirkan ruang di hujungnya.s -1 adalah untuk membuang ruang di hujungnya. Walaupun saya rasa TRIM juga menyingkirkan ruang di hujungnya.
Dan untuk tarikh tersebut, kita akan menggunakan MID. MID untuk- MID A2 bermula di Tempat yang Diselaraskan di D2 dan keluar 50 atau apa sahaja yang anda fikirkan mungkin, dan kemudian fungsi TRIM, dan kami akan klik dua kali untuk menyalinnya.
Baiklah sekarang, alasan saya menghubungi Mike adalah saya berkata, saya tertanya-tanya apakah ada cara untuk mengganti 12 lajur ini dengan satu bentuk, sebenarnya 13 lajur ini dengan satu bentuk. Adakah ada cara yang boleh saya lakukan dengan menggunakan formula Array? Dan tentu saja Mike menulis buku hebat itu, Ctrl + Shift + Enter, pada formula Array. Dan saya mencuba beberapa perkara yang berbeza dan dalam fikiran saya, tidak mungkin ia dapat dilakukan. Baiklah, tetapi anda tahu, mari bertanya kepada pakar. Jadi Mike, mari kita lihat apa yang anda ada.
Mike Girvin: Thanks,. Hey, and speaking of expert, this was pretty expertly done. You used FIND, AGGREGATE, ISNUMBER(MID. Now, when you sent this question over to me, I went ahead and solved it and it is amazing how similar my solution is to yours.
Alright, I'm going to go over to this sheet here. I'm going to start with figuring out where the start position in this text string is for each particular month. Now the way I'm going to do it is I'm going to, hey, use this SEARCH function. Now, you used FIND, I use SEARCH. Actually probably FIND is better in this situation because FIND is case-sensitive, SEARCH is not. Now normally what we do with either FIND or SEARCH, I say, hey, go FIND, January, comma within this larger text string, that's how we normally use SEARCH Ctrl+Enter, and it counts on its finger: one, two, three, four, five. It says the 32nd character is where it found January.
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.
Oleh itu, saya ingin mengucapkan terima kasih kepada semua orang kerana berhenti. Kami akan berjumpa anda di lain masa untuk Dueling Excel Podcast lain dan Excel Is Fun.
Muat turun fail
Muat turun fail contoh di sini: Duel180.xlsm
