EXCEL - Créer une barre de recherche avec une formule simple (Sans VBA) — Transcript

Full transcript

1. 0:00Hello everyone, I'm Axel. Welcome to
2. 0:03this new video on using Excel. In this
3. 0:06video, I'm going to show you how to
4. 0:09create a search bar in Excel. But first
5. 0:12, if you'd like to receive this file in
6. 0:14Excel so you can practice at the same
7. 0:15time as me, there's a link in the
8. 0:17description. Just click, enter your
9. 0:20email address and you'll receive this
10. 0:22file directly by email. Here we go.
11. 0:24Here, I have a list of countries in an
12. 0:28Excel table and we're going to create a
13. 0:31search bar that will filter the list in
14. 0:34real time as we type text. No VBA, no
15. 0:38macros, just a single formula. We're
16. 0:41going to do two examples. The first
17. 0:43example involves creating a search bar
18. 0:45on a single column and the second
19. 0:47example involves creating a search bar
20. 0:50on multiple columns. So, let's start by
21. 0:53creating the search box. Here, let's
22. 0:56say it's cell C1. And every time we
23. 1:00type something in it, Excel should only
24. 1:03return the countries that contain this
25. 1:06text, even partially. So here, the data
26. 1:10has already been formatted as a table.
27. 1:13And for this first table, the table
28. 1:16name is PI table and the column is PI
29. 1:19list. It's important because we'll use
30. 1:21these names in the formula. Now, we
31. 1:24select the cell in which we are going
32. 1:26to display the first search result, to
33. 1:30enter the formula that generates the
34. 1:32dynamic search. Since we are going to
35. 1:35use the FILTER function, which is a
36. 1:38dynamic function, meaning that it will
37. 1:42expand in width and height depending on
38. 1:45the number of results. We start by
39. 1:48selecting the table that we want to
40. 1:49filter. To do this, we select it. If we
41. 1:53do it with the mouse, we just need to
42. 1:55place the cursor at the top left of the
43. 1:58table. And when it takes the form of a
44. 2:01diagonal arrow pointing downwards, we
45. 2:04click and it selects all the data in
46. 2:06the table, with the name of the table
47. 2:08first and the name of the column in
48. 2:11brackets. So this is the table that we
49. 2:14want to filter. And to keep only the
50. 2:17countries that contain the text that we
51. 2:20are going to enter in the search cell,
52. 2:22we are going to use the inum function
53. 2:25with the search function. We select
54. 2:28cell C1 for the search text and for the
55. 2:32text we select the data from the table
56. 2:36again. Don't forget to close the
57. 2:39parenthesis of the search function, the
58. 2:41en function, and the filter function.
59. 2:44And now we are going to dissect this
60. 2:46formula together. Here, the first
61. 2:49argument of the filter function is the
62. 2:51list of ps since this is the list that
63. 2:54we want to filter. And the second part
64. 2:57is the condition. Search checks if the
65. 3:01text entered in C1 appears in each
66. 3:04country, i.e. in the countries in the
67. 3:07list, and Enum checks if a numeric
68. 3:11result exists. If it does, it means
69. 3:14that the function is trying to find one
70. 3:16of the letters entered in the search
71. 3:19cell, i.e. in the PI table. Here, the
72. 3:22enumeration function returns true;
73. 3:24otherwise, it returns false. We can
74. 3:27validate the formula and we will
75. 3:28perform the test. For now, we haven't
76. 3:31entered anything in the search cell.
77. 3:34The filter function displays all
78. 3:36countries from the source table. And if
79. 3:39we now type letters, this time it only
80. 3:42displays the countries that contain
81. 3:45these letters. For example, here FR, we
82. 3:47only have the countries that contain
83. 3:50eff in the name. And of course, we can
84. 3:53type a single letter. And if we type
85. 3:55several letters, all the letters must
86. 3:59be contained in the name. For example,
87. 4:02here, we have Germany and we don't have
88. 4:05France because even if there is an A in
89. 4:07it, there is no a followed by two Ls.
90. 4:10So it really works like a search bar as
91. 4:13we would normally find. And what's
92. 4:16interesting here is that the search
93. 4:18function is case-insensitive. So if we
94. 4:22type FR in lowercase or FR in uppercase
95. 4:26, we get the same results. And as we
96. 4:29saw, if the search box is empty, filter
97. 4:32automatically returns the entire list,
98. 4:35which is perfect for an intuitive
99. 4:38search bar. Now, let's move on to the
100. 4:41case where we perform a search, but
101. 4:43this time on multiple columns. So here
102. 4:46we have the search bar and we're going
103. 4:49to enter the formula in the first cell
104. 4:52at the top left and it will then expand
105. 4:54by itself according to the number of
106. 4:57columns in the source table. In the
107. 5:00example, on three columns on the right
108. 5:02starting from the first cell. This time
109. 5:05, we're using the table called table 2.
110. 5:08We're going to rename it, we're going
111. 5:11to call it sales table. So we do
112. 5:13exactly the same thing with the filter
113. 5:17function. We select the table. Then we
114. 5:21use the enum function. We nest the
115. 5:25search function. For the search text,
116. 5:28we select the search bar, so in which
117. 5:30we're going to enter the search text.
118. 5:33And this time, for the text, we're not
119. 5:36going to select the entire table, but
120. 5:39we're going to select all the columns
121. 5:42one after the other by connecting them
122. 5:45with the ampersand. So we select the
123. 5:48first column, we use the ampersand or
124. 5:51the commercial e. We select the second
125. 5:54column, type the ampersands again, and
126. 5:57select the third column. We can close
127. 5:59the parentheses of the search by number
128. 6:01and filter functions. We validate. And
129. 6:04as before, if we do not enter anything
130. 6:06in the search bar, the filter function
131. 6:09automatically returns the entire list.
132. 6:11In the example, all the data in the
133. 6:14table for all three columns. But if we
134. 6:17perform a search, for example, we type
135. 6:20DR, and we get all the values with DR
136. 6:23in the name. It doesn't matter which
137. 6:26column these letters are in. For
138. 6:29example, we can filter on the customer
139. 6:31column to search for a customer. For
140. 6:34example, I will search for the customer
141. 6:36Allro but also on the name of the
142. 6:38product, for example vacuum cleaner and
143. 6:41everything that contains ASP, high
144. 6:43suction vacuum cleaner. But here it
145. 6:46filters on all the columns. If we had a
146. 6:49customer with the name ASP in it, for
147. 6:52example here, if I add a new customer,
148. 6:55I will call it ASPI easy. No matter
149. 6:58what is in the other two columns, this
150. 7:01result also appears in the search, not
151. 7:05for the product name but for the
152. 7:07customer name since "faire un repas"
153. 7:10does not contain the letters ASP. And
154. 7:13we can do this for the amount column as
155. 7:16well. So that's it for this short video
156. 7:19on how to create a search bar in Excel
157. 7:22without VBA, without a macro, just with
158. 7:24a very simple formula. I repeat, if you
159. 7:27would like to receive the Excel file
160. 7:29used in this video, there is a link in
161. 7:32the description. Just click, enter your
162. 7:35email and you will receive this file
163. 7:37directly by email. In any case, I hope
164. 7:40that this video will be useful to you
165. 7:41and as for us, we'll see you next time.