EXCEL ДЛЯ ПРОДВИНУТЫХ: 9 полезных трюков
Microsoft Excel, пожалуй, лучшая офисная программа из когда-либо созданных. На ней держатся целые отрасли экономики, так что, виртуозно овладев этим инструментом, Вы сразу заметите, что дела у Вашего бизнеса идут в гору.
Кроме множества базовых приёмов работы с этой программой, которые Вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают Вас на голову выше всех остальных. Итак, 9 хитрых функций Excel.
1. Функция ВПР
Эта функция позволяет быстро найти нужное Вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth”,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.
Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если Вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если Вы выберете слишком большую таблицу, поиск может занять много времени.
2. Функция ИНДЕКС
Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек. Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).
3. Функция ПОИСКПОЗ
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне (Диапазон - две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными) ячеек и отражает относительную позицию этого элемента в диапазоне.
По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.
Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0).
С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью.
4. 3D-сумма
Допустим, Вы решили подсчитать, сколько денег Вы тратите каждый день, и вести учёт в течение семи недель. Под каждую неделю Вы создали отдельный лист, куда по дням заносите расходы на алкоголь, еду и прочие мелочи.
Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день Вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках. Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.
5. $
Одна из самых удобных функций в арсенале Excel, а также одна из самых простых – это знак $. Он указывает программе, что не нужно делать автоматической корректировки формулы при её копировании в новую ячейку, как Excel поступает обычно.
При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении. Очень удобный приём, когда приходится работать с большими базами данных.
6. &
Если Вы хотите собрать все значения из разных ячеек в одну, Вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&».
7. Массивы
Для создания массива или матрицы Вам потребуется несколько операций, но они сложнее, чем в случае с обычными формулами, ведь для отображения результата требуется не одна, а несколько ячеек.
Например, давайте перемножим две матрицы. Для этого используем функцию МУМНОЖ (Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами, – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.
8. Подбор параметра
Без этой функции Excel целым легионам аналитиков, консультантов и прогнозистов пришлось бы туго. Спросите кого угодно из сферы консалтинга или продаж, и Вам расскажут, насколько полезной бывает эта возможность Excel.
Например, Вы занимаетесь продажами новой видеоигры, и Вам нужно узнать, сколько экземпляров Ваши менеджеры должны продать в третьем месяце, чтобы заработать 100 миллионов долларов. Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов. В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный Вам результат.
9. Сводные таблицы
Если Вам нужно провести макроанализ и увидеть общую картину, а на руках у Вас только бесконечные колонки цифр, то сводная таблица станет отличным способом оптимизировать и ускорить работу. Для этого выберите в меню «Вставка» пункт «Сводная таблица». Введя нужные диапазоны, Вы получите данные в виде удобной для восприятия таблицы. Вы легко можете изменять параметры отображения данных с помощью конструктора сводных таблиц, сравнивать и анализировать полученные числа и т.д.
Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.
Примечание: название функций и операций на русском языке указано в соответствии с русифицированной версией Microsoft Excel 2010.
Microsoft Excel, пожалуй, лучшая офисная программа из когда-либо созданных. На ней держатся целые отрасли экономики, так что, виртуозно овладев этим инструментом, Вы сразу заметите, что дела у Вашего бизнеса идут в гору.
Кроме множества базовых приёмов работы с этой программой, которые Вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают Вас на голову выше всех остальных. Итак, 9 хитрых функций Excel.
1. Функция ВПР
Эта функция позволяет быстро найти нужное Вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth”,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.
Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если Вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если Вы выберете слишком большую таблицу, поиск может занять много времени.
2. Функция ИНДЕКС
Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек. Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).
3. Функция ПОИСКПОЗ
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне (Диапазон - две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными) ячеек и отражает относительную позицию этого элемента в диапазоне.
По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.
Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0).
С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью.
4. 3D-сумма
Допустим, Вы решили подсчитать, сколько денег Вы тратите каждый день, и вести учёт в течение семи недель. Под каждую неделю Вы создали отдельный лист, куда по дням заносите расходы на алкоголь, еду и прочие мелочи.
Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день Вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках. Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.
5. $
Одна из самых удобных функций в арсенале Excel, а также одна из самых простых – это знак $. Он указывает программе, что не нужно делать автоматической корректировки формулы при её копировании в новую ячейку, как Excel поступает обычно.
При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении. Очень удобный приём, когда приходится работать с большими базами данных.
6. &
Если Вы хотите собрать все значения из разных ячеек в одну, Вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&».
7. Массивы
Для создания массива или матрицы Вам потребуется несколько операций, но они сложнее, чем в случае с обычными формулами, ведь для отображения результата требуется не одна, а несколько ячеек.
Например, давайте перемножим две матрицы. Для этого используем функцию МУМНОЖ (Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами, – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.
8. Подбор параметра
Без этой функции Excel целым легионам аналитиков, консультантов и прогнозистов пришлось бы туго. Спросите кого угодно из сферы консалтинга или продаж, и Вам расскажут, насколько полезной бывает эта возможность Excel.
Например, Вы занимаетесь продажами новой видеоигры, и Вам нужно узнать, сколько экземпляров Ваши менеджеры должны продать в третьем месяце, чтобы заработать 100 миллионов долларов. Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов. В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный Вам результат.
9. Сводные таблицы
Если Вам нужно провести макроанализ и увидеть общую картину, а на руках у Вас только бесконечные колонки цифр, то сводная таблица станет отличным способом оптимизировать и ускорить работу. Для этого выберите в меню «Вставка» пункт «Сводная таблица». Введя нужные диапазоны, Вы получите данные в виде удобной для восприятия таблицы. Вы легко можете изменять параметры отображения данных с помощью конструктора сводных таблиц, сравнивать и анализировать полученные числа и т.д.
Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.
Примечание: название функций и операций на русском языке указано в соответствии с русифицированной версией Microsoft Excel 2010.
EXCEL FOR ADVANCED: 9 useful tricks
Microsoft Excel is perhaps the best office program ever created. Entire sectors of the economy rest on it, so masterfully mastering this tool, you will immediately notice that things are going uphill for your business.
In addition to the many basic tricks of working with this program that you probably already know, it is useful to learn some tricks "for advanced" that will make you head and shoulders above everyone else. So, 9 tricky Excel features.
1. VLOOKUP function
This function allows you to quickly find the value you need in the table. For example, we need to know the final Bette score, we write: = VLOOKUP (“Beth”, A2: E6,5,0), where Beth is the student’s name, A2: E6 is the range of the table, 5 is the column number, and 0 means that we are not looking for an exact match to the value.
The function is very convenient, but you need to know some features of its use. Firstly, VLOOKUP searches only from left to right, so if you need to search in a different order, you will have to change the sorting options for the whole sheet. Also, if you select a table that is too large, the search can take a long time.
2. INDEX function
Reflects a value or cell reference at the intersection of a specific row and column in a selected range of cells. For example, to see who became the fourth in the list of the highest paid Wall Street top managers, we type: = INDEX (A3: A11, 4).
3. SEARCH function
The SEARCH function searches for a specified element in a range (Range - two or more cells in a sheet. Range cells can be either adjacent or non-adjacent) cells and reflects the relative position of this element in the range.
Separately, INDEX and SEARCH are not particularly useful. But together they can replace the VLOOKUP function.
For example, to find who is the head of Wells Fargo in a large table, we write = INDEX (A3: A11, SEARCH (“Wells Fargo”, B3: B11.0).
Using the VLOOKUP function does not do this, because it searches only from left to right. A combination of the last two allows you to do this with ease.
4. 3D amount
Suppose you decide to calculate how much money you spend every day and keep records for seven weeks. For each week, you have created a separate sheet, where, by days, bring the costs of alcohol, food and other trifles.
Now on the TOTAL tab (TOTAL) we need to see how much and on what day you spent for this period. We type = SUM (‘Week1: Week7’! B2), and the formula sums all the values in cell B2 on all tabs. Now, having filled all the cells, we found out which day of the week we spent the most, and as a result we knocked out all our expenses for these 7 weeks.
5. $
One of the most convenient features in the arsenal of Excel, as well as one of the simplest, is the $ sign. He tells the program that there is no need to automatically adjust the formula when it is copied to a new cell, as Excel usually does.
In this case, the $ sign in front of “A” prevents the program from changing the formula horizontally, and before “1” - vertically. If you write "$ A $ 1", then the value of the copied cell will be the same in any direction. A very convenient technique when you have to work with large databases.
6. &
If you want to collect all the values from different cells into one, you can use the CONNECT function. But why type so many letters if you can replace them with the “&” sign.
7. Arrays
To create an array or matrix, you will need several operations, but they are more complicated than in the case with conventional formulas, because to display the result, you need not one, but several cells.
For example, let's multiply two matrices. To do this, use the function MUMNOZH (Array 1, Array 2). The main thing, do not forget to close the formula with a parenthesis. Now press the key combination Ctrl + Shift + Enter, and Excel will show the result of the multiplication in the form of a matrix. The same applies to other functions that work with arrays - instead of just pressing Enter to get the result, use Ctrl + Shift + Enter.
8. Parameter selection
Without this Excel function, entire legions of analysts, consultants and forecasters would have been tight. Ask anyone in consulting or sales, and they will tell you how useful this Excel feature is.
For example, you are selling a new video game, and you need to find out how many copies your managers must sell in the third month to earn $ 100 million. To do this, in the "Tools" menu, select the "Parameter Selection" function. We need to have a value of $ 100 million in the Total revenue cell. In the set cell field, specify the cell in which the total amount will be, in the to value field - the desired amount, and in by Changing cell (change the cell value) select the cell where the number of sold in the third month will be displayed goods. And - voila! - the program coped. The parameters and values in the cells can be easily manipulated to get the result you need.
9. Summary tables
If you need to conduct a macro analysis and see the big picture, and you only have endless columns of numbers in your hands, then the summary table will become
Microsoft Excel is perhaps the best office program ever created. Entire sectors of the economy rest on it, so masterfully mastering this tool, you will immediately notice that things are going uphill for your business.
In addition to the many basic tricks of working with this program that you probably already know, it is useful to learn some tricks "for advanced" that will make you head and shoulders above everyone else. So, 9 tricky Excel features.
1. VLOOKUP function
This function allows you to quickly find the value you need in the table. For example, we need to know the final Bette score, we write: = VLOOKUP (“Beth”, A2: E6,5,0), where Beth is the student’s name, A2: E6 is the range of the table, 5 is the column number, and 0 means that we are not looking for an exact match to the value.
The function is very convenient, but you need to know some features of its use. Firstly, VLOOKUP searches only from left to right, so if you need to search in a different order, you will have to change the sorting options for the whole sheet. Also, if you select a table that is too large, the search can take a long time.
2. INDEX function
Reflects a value or cell reference at the intersection of a specific row and column in a selected range of cells. For example, to see who became the fourth in the list of the highest paid Wall Street top managers, we type: = INDEX (A3: A11, 4).
3. SEARCH function
The SEARCH function searches for a specified element in a range (Range - two or more cells in a sheet. Range cells can be either adjacent or non-adjacent) cells and reflects the relative position of this element in the range.
Separately, INDEX and SEARCH are not particularly useful. But together they can replace the VLOOKUP function.
For example, to find who is the head of Wells Fargo in a large table, we write = INDEX (A3: A11, SEARCH (“Wells Fargo”, B3: B11.0).
Using the VLOOKUP function does not do this, because it searches only from left to right. A combination of the last two allows you to do this with ease.
4. 3D amount
Suppose you decide to calculate how much money you spend every day and keep records for seven weeks. For each week, you have created a separate sheet, where, by days, bring the costs of alcohol, food and other trifles.
Now on the TOTAL tab (TOTAL) we need to see how much and on what day you spent for this period. We type = SUM (‘Week1: Week7’! B2), and the formula sums all the values in cell B2 on all tabs. Now, having filled all the cells, we found out which day of the week we spent the most, and as a result we knocked out all our expenses for these 7 weeks.
5. $
One of the most convenient features in the arsenal of Excel, as well as one of the simplest, is the $ sign. He tells the program that there is no need to automatically adjust the formula when it is copied to a new cell, as Excel usually does.
In this case, the $ sign in front of “A” prevents the program from changing the formula horizontally, and before “1” - vertically. If you write "$ A $ 1", then the value of the copied cell will be the same in any direction. A very convenient technique when you have to work with large databases.
6. &
If you want to collect all the values from different cells into one, you can use the CONNECT function. But why type so many letters if you can replace them with the “&” sign.
7. Arrays
To create an array or matrix, you will need several operations, but they are more complicated than in the case with conventional formulas, because to display the result, you need not one, but several cells.
For example, let's multiply two matrices. To do this, use the function MUMNOZH (Array 1, Array 2). The main thing, do not forget to close the formula with a parenthesis. Now press the key combination Ctrl + Shift + Enter, and Excel will show the result of the multiplication in the form of a matrix. The same applies to other functions that work with arrays - instead of just pressing Enter to get the result, use Ctrl + Shift + Enter.
8. Parameter selection
Without this Excel function, entire legions of analysts, consultants and forecasters would have been tight. Ask anyone in consulting or sales, and they will tell you how useful this Excel feature is.
For example, you are selling a new video game, and you need to find out how many copies your managers must sell in the third month to earn $ 100 million. To do this, in the "Tools" menu, select the "Parameter Selection" function. We need to have a value of $ 100 million in the Total revenue cell. In the set cell field, specify the cell in which the total amount will be, in the to value field - the desired amount, and in by Changing cell (change the cell value) select the cell where the number of sold in the third month will be displayed goods. And - voila! - the program coped. The parameters and values in the cells can be easily manipulated to get the result you need.
9. Summary tables
If you need to conduct a macro analysis and see the big picture, and you only have endless columns of numbers in your hands, then the summary table will become
У записи 3 лайков,
0 репостов.
0 репостов.
Эту запись оставил(а) на своей стене Екатерина Федорова
![Олег Яковлев [ведущий]](https://sun9-51.userapi.com/s/v1/if1/i_TN-GSjPx4SzNEI_301NGcg2TZn7asg39fJfny0nV96uL7A6yC7L8QkvKDv0i48m6tSniOW.jpg?quality=96&crop=64,90,463,463&as=32x32,48x48,72x72,108x108,160x160,240x240,360x360&ava=1&cs=200x200 "Олег Яковлев [ведущий]")
