Полезные функции excel

Полезные советы

Microsoft Excel, пожалуй, лучшая офисная программа из когда-либо созданных. На ней держатся целые отрасли экономики, так что, виртуозно овладев этим инструментом, вы сразу заметите, что дела у вашего бизнеса идут в гору.

1. 9 функций Excel, которые могут быть вам полезны
2. ВПР
3. Функция ИНДЕКС
4. ПОИСКПОЗ
5. 3D-сумма
6. $
7. &
8. Массивы
9. Подбор параметра

9 функций Excel, которые могут быть вам полезны

Кроме множества базовых приёмов работы с этой программой, которые вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают вас на голову выше всех остальных. Так что, если хотите впечатлить своих боссов и разгромить конкурентов, вам пригодятся эти 9 хитрых функций Excel.

Excel обладает массой функций

Эта функция позволяет быстро найти нужное вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth”,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.

Функция очень удобна, однако нужно знать некоторые особенности её использования. ВПР ищет только слева направо, так что, если вам понадобится искать в другом порядке, придётся менять параметры сортировки целого листа. И сли вы выберете слишком большую таблицу, поиск может занять много времени.

Функция ИНДЕКС

Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек. Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).

ПОИСКПОЗ

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне (Диапазон — две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными) ячеек и отражает относительную позицию этого элемента в диапазоне.

По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.

Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0).

С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это очент просто.

3D-сумма

Допустим, вы решили подсчитать, сколько денег вы тратите каждый день, и вести учёт в течение семи недель. Под каждую неделю вы создали отдельный лист, куда по дням заносите расходы на алкоголь, еду и прочие мелочи.

Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках. Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.

Одна из самых удобных функций в арсенале Excel, а также одна из самых простых – это знак $. Он указывает программе, что не нужно делать автоматической корректировки формулы при её копировании в новую ячейку, как Excel поступает обычно.

При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении. Очень удобный приём, когда приходится работать с большими базами данных.

Если вы хотите собрать все значения из разных ячеек в одну, вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&».

Массивы

Для создания массива или матрицы вам потребуется несколько операций, но они сложнее, чем в случае с обычными формулами, ведь для отображения результата требуется не одна, а несколько ячеек.

Например, давайте перемножим две матрицы. Для этого используем функцию МУМНОЖ (Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами, – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.

Подбор параметра

Без этой функции Excel целым легионам аналитиков, консультантов и прогнозистов пришлось бы туго. Спросите кого угодно из сферы консалтинга или продаж, и вам расскажут, насколько полезной бывает эта возможность Excel.

Знание этих приёмом поможет вам сэкономить кучу времени при работе с электронными таблицами.

Полезные функции Excel: мелочи, которые помогут упростить работу

В Excel есть полезные функции, о которых мало кто знает. Они достаточно простые, но при этом могут существенно упростить работу и сэкономить время в десятки и сотни раз.
Это статья о секретах Excel при вводе и форматировании информации, которые будут полезны независимо от сферы деятельности и способах его использования.

При этом, обычно мы используем только 5% от возможностей этой программы. Но изучив основные полезные функции Excel можно работать гораздо проще и быстрее, а разработав форму расчетов можно выполнять рутинные задачи одним кликом вместо десяти и сотни.

Кнопка «Формат по образцу»

Она применяется для копирования формата ячейки. Например, Вы сделали формат ячейки со множеством параметров: размер шрифта, заливка ячейки, границы ячейки и прочее. Чтобы эти действия повторно не выполнять, можно воспользоваться кнопкой «Формат по образцу».
Для этого нужно выделить ячейку с нужным форматом, нажать на кнопку и выделить ячейку, к которой нужно применить этот формат.

Преобразование строк в столбцы и обратно

Иногда требуется поменять местами строки и столбцы (например, начальнику нужен отчет в другом виде).
Для этого нужно:
1. Копировать нужную таблицу
2. Поставить курсор на пустую ячейку, в которой будет начинаться будущая таблица
3. Нажать правой кнопкой мыши и выбрать команду «Транспонировать».

Автоподбор ширины столбца

При необходимости задания ширины столбца согласно вписанного и всего лишь дважды щелкнуть по линии между столбцами в самом верху. Это приведет к автоматическому расширению ячейки под заданный размер ее содержимого. Эту операцию можно применить сразу к нескольким столбцам.

Преобразование текста в число

Для данных, записанных в текстовом формате невозможно применить математические формулы (сложение, вычитание и др.). Чтобы быстро заменить текстовый формат на числовой можно воспользоваться одним из способов:
— умножением на единицу. То есть в ячейке вписать формулу: =ячейка*1
— подстановкой двух минусов. То есть в ячейке вписать формулу: =—ячейка
— воспользоваться командой «Найти и заменить» для данного диапазона.
Если в качестве разделителей целых и дробных чисел выступают точки, то можно воспользоваться командой «Найти и заменить» для данного диапазона.

Изменение регистра букв

Изменить регистр всей текстовой информации можно с помощью функций
«ПРОПИСН» — делает все буква прописными,
«СТРОЧН» — делает все буква строчными,
«ПРОПНАЧ» — делает прописными только первую букву в каждом слове.

Печать нулей вначале числа

При необходимости внесения числа с нулями вначале Excel автоматически уберет нули. Для их сохранения нужно перед числом поставить знак апострофа.

Перенести текст внутри ячейки

Для этого используется клавиша Alt на клавиатуре вместе с клавишей Enter.

Объединение ячеек и отмена объединения ячеек

Чтобы объединить ячейки, нужно выделить нудный диапазон ячеек и нажать кнопку «Объединить и выровнять по центру».Для отмены объединения, нужно выделить объединенную ячейку и нажать на ту же кнопку.

Объединяется информации из нескольких ячеек в одну

Для этого используется знак амперсанда «&», например, =ячейка1&яччейка2. Если нужно сделать пробел между ними, то формула будет следующей =ячейка1& » «&ячейка2.

Печать даты

• Для задания формата даты или изменения формата даты нужно воспользоваться командой «Число» —> «Дата» и выбрать нужный формат.
• Чтобы быстро вставить текущую дату, используется сочетание клавиш или ,
• Для вставки текущего времени — или .
• Чтобы отобразить в ячейке текущую дату, которая будет обновляться ежедневно и показывать актуальное значение, используется функция СЕГОДНЯ. Для этого достаточно набрать в ячейке формулу: =СЕГОДНЯ().

Расширенный поиск

При нажатии кнопки «Найти и заменить» (или сочетание клавиш Ctrl + F) можно найти любые данные. Но иногда требуется найти не точную информацию. В этом случае в форме поиска нужно воспользоваться символами вопрос (?) и звездочка(*). Знак вопроса заменяет один неизвестный символ, звездочка – несколько неизвестных символов. Если нужно найти непосредственно знаки вопроса или звездочки, то в поисковой форме поставить перед ними знак тильды (

«Волшебная» клавиша F4

Нажатие этой клавиши позволяет повторить любое последнее действие: выравнивание ячеек, вставку строк и столбцов, изменение размеров и др. Она может значительно сократить время обработки данных, заменяя сразу несколько нажатий мышкой.

10 самых полезных функций MS Excel

Наши преподаватели много лет ведут курсы Excel для персонала компаний. За это время накопилась статистика сотен фирм относительно востребованности на практике и полезности тех или иных функций. Этим рейтингом, в порядке убывания популярности, мы и спешим с Вами поделиться.

Кому будет полезна данная подборка?

• Заказчикам, планирующим курсы Excel для сотрудников, и думающим над расстановкой приоритетов в учебной программе.
• Соискателям вакансий, для понимания каких знаний от них скорее всего потребует работодатель.
• Специалистам, уже использующим электронные таблицы, и не догадывающимся, что работу можно сделать гораздо эффективней.

Функции перечислены в порядке убывания их значимости.

ВПР (VLOOKUP)

ВПР – в расшифровке это вертикальный просмотр. Функция незаменима для хозяйственников, бухгалтеров, начальников складов, менеджеров, даже маркетологов.

Что делает ВПР? — переносит искомые данные, согласно параметрам поиска, из исходной таблицы в сводную. Инструмент для понимания и освоения не самый простой, но действительно полезный.

Примеры использования функции ВПР в разных отделах компании.

1. Реклама. Нужно свести показы (или прокрутки в подкастах) рекламных роликов по площадкам для каждого из продуктов. Формируем сетку сводной таблицы и за несколько минут переносим в неё все данные из отчётов по каждой рекламной площадке. Если где-то показов не было, можно скомбинировать ВПР с функцией ЕСЛИОШИБКА, тогда в ячейке по продукту, который не рекламировался, будет стоять прочерк.
2. Бухгалтерия. Здесь удобно считать зарплату по категориям сотрудников. Берём сводную таблицу Excel и исходную со ставками по категориям, переносим значение ставок в новый отчёт за 1 секунду.
3. Складские запасы. С помощью функции здесь очень удобно формировать таблицы с общими остатками. Для этого опять же надо сформировать итоговый отчёт, в котором первый столбец – это наименование товара, а строкой идут магазины. Дальше применяем функцию ВПР по остаткам для каждого магазина. Получаем заполненную таблицу по всей сети за минуты. И сразу можно формировать заказ на поставки.

СЧЕТЕСЛИ (COUNT)

Ещё одна полезная функция для менеджера, IT-специалиста, маркетолога. СЧЕТЕСЛИ считает количество непустых ячеек (заполненных данных), которые соответствуют заданному условию.

С помощью функции можно подсчитать количество сотрудников, выполнивших план, узнать, сколько сделок заключил новый работник за 6 месяцев. Или увидеть число товаров, которые были проданы в отчётном месяце больше 100 раз. Численность персонала старше 60 лет или количество семейных сотрудников, отделений площадью свыше 100 квадратов и т. д. Условие можно задать любое.

СЧЕТЕСЛИМН (COUNTS)

Эта функция, по сути, является модернизированной версией предыдущего инструмента. Только в отличие от СЧЕТЕСЛИ СЧЕТЕСЛИМН позволяет задавать одновременно несколько условий, уточняя выборку.

Удобно для бухгалтеров, руководителей, интернет-маркетологов. Позволяет быстро узнать точное число, например, сотрудников, которые приняты на работу больше 4 лет назад и совершившие в месяц больше 10 продаж. Или узнать, сколько зачислений на счёт компании было совершено в отчётном месяце в сумме от 50 тыс. рублей в национальной валюте.

СУММ (SUM)

С этой функции многие открывают для себя «магию» Excel. Потому что она незаменима при составлении отчетов. Как несложно догадаться из названия, СУММ считает сумму значений в ячейках выборки. Умеет по столбцам, строкам, всей таблице.

Она рассчитывает значения пресловутых «Итого» и «Всего». По зарплате, складским остаткам, расходам и т. д.

СУММЕСЛИ (SUMIF)

Расширенная функция СУММЕСЛИ считает сумму значений в ячейках, которые соответствуют установленному условию. Очень удобна, когда надо узнать, сколько транзакций совершено по конкретному терминалу из списка. Или рассчитать из сводной таблицы затраты на 1 отделение.

СУММЕСЛИМН (SUMIFS)

По аналогии со СЧЕТСЛИМН функция СУММЕСЛИМН помогает получить сумму данных, согласно нескольким условиям выборки. Продолжая предыдущие примеры, она считает не просто общие расходы на отделение, а конкретно постоянные или переменные затраты. Или издержки на содержание его IT-отдела.

ЕСЛИ (IF)

Функция ЕСЛИ заполняет ячейку в зависимости от выполнения или невыполнения указанного условия. Очень удобный инструмент, когда ячейка заполняется в привязке к некоему результату или требованию. Например:

• премия начисляется только при выполнении сотрудником плана (функция заполнит ячейку с суммой премии или оставит её пустой в зависимости от того, что указано в ячейке его продаж);
• расчёт за поставку производится через определённое количество дней или после распродажи (когда известен процент возврата);
• дальнейшее финансирование проекта осуществляется, если он начинает приносить прибыль (или выделяемая сумма привязана к этой прибыли).

СЕГОДНЯ (TODAY)

Функция СЕГОДНЯ полезна при работе с вопросами, где важно соотносить даты (товары с ограниченным сроком действия, дебиторская задолженность со сроками возврата, кредиты и т. д.). Она даёт дату на текущий момент (каждый день она автоматически меняется), от которой отнимаются:

• дата платежа (расчет дней просрочки);
• дата выпуска товара (количество дней негодности) и т. д.

СЦЕПИТЬ (CONCATENATE)

Функцию используют не так часто, как стоило бы. Хотя фактическая потребность в ней есть у всех. СЦЕПИТЬ позволяет соединить разбросанные при копировании слова, которые должны быть в одной ячейке, а оказались в разных.

Это может быть наименование товара (детские подгузники Libero), полное ф. и. о. (Иванов Иван Иванович) и т. д. Очень удобный инструмент, экономит часы на форматирование.

СЖПРОБЕЛЫ (TRIM)

Ещё одна функция для оформления таблиц, которая поможет исключить ошибки при использовании текстовых данных ячейки в формулах. СЖПРОБЕЛЫ удаляет лишние пробелы, оставляя только по 1 пробелу между словами.

Больше функций Excel, которые экономят время

Перечисленные 10 функций – это лишь самые полезные из наиболее часто используемых инструментов Excel. Список можно продолжать долго. Есть масса функций для анализа, построения прогнозов, формирования отчетов. В зависимости от профиля фирмы и под каждый её отдел нужно сформировать свой набор действенных инструментов модуля.

Этим и занимаются в учебном центре «Альянс». Мы формируем программу корпоративного обучения Excel под конкретные задачи организации и учим сотрудников пользоваться теми функциями, которые сэкономят время и упростят работу именно в их условиях.

Функции Excel 2016: 10 самых важных формул

Опубликовано Михаил Непомнящий в 24.10.2016 24.10.2016

Общее количество функций для работы с электронными таблицами великое множество. Однако среди них есть наиболее полезные для повседневного использования. Мы составили десять самых важных формул Excel 2016 на каждый день.

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;” “;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

Раскрывающиеся списки Excel

Раскрывающиеся списки Excel
Добавление раскрывающегося списка в вашу Excel-таблицу может значительно повысить удобство работы, а значит и эффективность.
Подробнее

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

В качестве результата может также выводиться значение указанной ячейки, как текстовое, так цифирное. В таком случае в дальнейшем достаточно будет поменять значение одной ячейки, без необходимости править формулу во всех местах использования.

Формула ранжирования

Для значения чисел можно использовать формулу РАНГ, которая выдаст величину каждого числа относительно других в заданном списке. При этом ранжирование может быть как от меньшего значения в сторону увеличения, так и обратно.

Для данной функции используется три параметра – непосредственно число, массив или ссылка на список чисел и порядок. При этом если порядок не указан или стоит значение 0, то ранг определяется в порядке убывание. Любое другое значение для порядка будет отсортировывать значения по возрастанию.

Применение: Для таблицы с доходами по месяцам можно добавить столбец с ранжированием, а в дальнейшем по этому столбцу сделать сортировку.

Максимум из выбранных значений

Простая, но очень полезная формула МАКС выдает наибольшее значение из списка значений. Сам список может состоять как из ячеек и/или их диапазона, так и вручную введенных чисел. Всего максимальное значение можно искать среди списка из 255 чисел.

Применение: Возвращаясь к примеру с ранжированием, вместо ранга можно выводить значение лучшего показателя за выбранный период.

Минимум из выбранных значений

Аналогичным образом действует формула поиска минимальных значений. Идентичный синтаксис, обратный результат на выходе.

Среднее из выбранных значений

Для получения среднего арифметического из выбранного списка значений также есть своя формула. Однако написание ее в русском языке не столь очевидно. Звучит она как СРЗНАЧ, после чего в скобках указываются либо конкретные значения, либо ссылки на ячейки.

Сумма выбранных значений

Напоследок, самая ходовая функция, которую знает каждый, когда-либо использовавший электронные таблицы Excel. Сложение производится по формуле СУММ, а в скобках задается интервал или интервалы ячеек, значения которых требуется суммировать.

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

Примечание: Подробную информацию об использовании каждой функции Excel можно найти на официальном сайте Microsoft Office.

Разное — всего понемногу

В этой статье опубликованы различные вспомогательные функции на VBA, которые порой помогают в работе.

1. Функция формирования инициалов из имени и отчества

2. Шифрование строк на VBA

3. Сортировка двумерного массива по нулевому столбцу

4. Преобразование строки в набор ASC кодов

5. Функции для определения нажатой клавиши

6. Макрос для создания копии файла программы
(подразумевается наличие в программе глобальной константы или функции PROJECT_NAME)

7. Изменяем форматирование цифр в ячейке (выделяем все цифры полужирным шрифтом)

8. Поиск артикула (последовательности цифр заданной длины) в текстовой строке
Ссылка на примеры использования Regexp: script-coding.com/WSH/RegExp.html

9. Добавление значений сразу во весь столбец двумерного массива

10. Использование Application.OnTime с задержкой меньше секунды

11. Преобразование коллекции в массив

12. Разрешаем Excel доступ в Интернет путем отключения брандмауэра:

• 20590 просмотров

Комментарии

А чем событие Change листа не устраивает?

«Скажите, что вы пытаетесь сделать — наверняка найдётся более простой способ».

В Ворде2007 в статусбаре есть кнопка WordCount(Число слов)Мне кажется она отслеживает нажатие )))
Очень хочется понять ее принцип работы.

возможно ли основываясь на вашем коде сделать следующее:
при нажатии любой клавиши (находясь в редактировании или листа, ячейки или скажем документа в ворде) иметь запуск маленького макроса.
То есть макрос должен включаться при нажатии или отжатии клавиши.

А зачем запускать макрос каждый раз при вводе очередного символа?
Скажите, что вы пытаетесь сделать — наверняка найдётся более простой способ.

В общем случае, в Excel такого не сделать — когда вы редактируете текст в ячейке, никакие макросы не работают (они все приостанавливаются).
Что означает «редактирование листа» — не понял.
При изменении ячеек, переходе по листам и ячейкам макросы запускать можно — но для этого не требуется назначать макросы на нажатия клавиш — для этого в Excel существуют события листа и книги.
Могу посоветовать назначить макросы на нажатие комбинаций клавиш (например, Ctrl + буква) — но и в этом случае функция GetKeyState вам не нужна.

дико извиняюсь за первый вопрос. Понял. В период выполнения макроса он следит какая клавиша была нажата.
Всегда так сначала спрашиваю, потом думаю.

но второй вопрос остается.

позвольте узнать
5. Функции для определения нажатой клавиши
какую задачу выполняет? показывает какую клавишу нажали?

возможно ли основываясь на вашем коде сделать следующее: при нажатии любой клавиши (находясь в редактировании или листа, ячейки или скажем документа в ворде) иметь запуск маленького макроса. То есть макрос должен включаться при нажатии или отжатии клавиши.