Excel интересные возможности: Самые интересные функции для работы в Excel

6 малоизвестных, но очень полезных функций Excel

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

И поэтому сегодня AdMe.ru расскажет, какими способами можно эффективно систематизировать информацию и разложить все по полочкам.

Сводные таблицы

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

Как применять:

1. Выберите Вставка > Рекомендуемые сводные таблицы.
2. В диалоговом окне Рекомендуемые сводные таблицы щелкните любой макет сводной таблицы, чтобы увидеть его в режиме предварительного просмотра, а затем выберите тот из них, в котором данные отображаются нужным вам образом.  Нажмите кнопку ОК.
3. Excel добавит сводную таблицу на новый лист и отобразит список полей, с помощью которого можно упорядочить данные в таблице.

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

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

Как применять:

1. Выберите Данные > Работа с данными > Анализ «что если» > Подбор параметра. 
2. В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. 
3. В поле Значение введите нужный результат формулы. 
4. В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение, и нажмите кнопку ОК.

Условное форматирование

Условное форматирование позволяет быстро выделить на листе важные сведения.

Как применять:

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите формулу, которая вам понадобится.

Например, если вам нужно выделить все значения меньше 100, выберите Правила выделения ячеек > Меньше, а затем наберите 100. Перед тем как нажать ОК, можно выбрать формат, который будет применяться для подходящих значений.

ИНДЕКС и ПОИСКПОЗ

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

Как применять:

1. Убедитесь, что ячейки с данными образуют сетку, где есть заголовки и названия строк.
2. Используйте функцию ПОИСКПОЗ: сначала, чтобы найти столбец, в котором расположен искомый элемент, и затем еще раз, чтобы перейти к строке с ответом. 
3. Вставьте ответы в ИНДЕКС, и Excel сможет указать на ячейку, где эти значения пересекаются.

Например: ИНДЕКС (array, ПОИСКПОЗ (lookup_value, lookup_array, 0), ПОИСКПОЗ (lookup_value, lookup_array, 0)).

Диаграмма «водопад»

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

Как применять:

В версии Excel 2016 необходимо выделить нужные данные и выбрать Вставка > Водопад или Диаграмма > Водопад.

Прогноз

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

Как применять:

1. Укажите не менее двух ячеек, содержащих начальные значения.
2. Выберите Данные > Прогноз > Лист прогноза. 
3. В окне Создание листа прогноза выберите график или гистограмму для визуального представления прогноза.
4. В поле Завершение прогноза выберите дату окончания, а затем нажмите кнопку Создать.

По материалам lifehacker.com, Перевод и адаптация: , AdMe.ru

Самые нужные функции в excel

10 полезных функций программы Microsoft Excel

​Смотрите также​ и выберите инструмент:​ будем рассматривать практическое​Применение функции СМЕЩ​ Excel.​МОДА.ОДН​ аргументу проверяем условие,​ диапазонов ячеек в​ информационные сообщения пользователям​ данных в Excel,​ столбцам, Верхний и​ приведенных примеров вы​

​ каждая рабочая книга​Каждый раздел содержит несколько​

Функция ВПР

​ введете в ячейки,​ условиям, или одному​ различного типа диаграмм.​Программа Microsoft Excel способна​ «Формулы»-«Логические»-«ЕСЛИ».​ применение функций.​ для динамического получения​Примеры использования функции​. Форма записи​ по третьему –​ Excel. С помощью​ Вашей программы.​ чтобы видеть только​ нижний регистр, Удалить​ узнаете, как предоставить​ Excel содержит три​ глав.

В каждой​ расположенные у границ​ из нескольких условий.​Чтобы создать диаграмму, нужно​ в значительной мере​Заполняем аргументы функции в​Как найти среднее арифметическое​ ссылки на ячейку​ ЗАМЕНИТЬ при различных​МОДА​

​ находим среднее.​ данных функций Вы​Объекты Workbook и Worksheet:​ записи, удовлетворяющие определённому​ ненужные символы, Сравнение​

​ общий доступ к​ листа (в Excel​ главе вы найдете​ таблицы, какие-то данные,​Урок: Применение функции «ЕСЛИ»​ выделить набор ячеек​

​ облегчить пользователю работу​ диалоговом окне как​

Сводные таблицы

​ число в Excel?​ в диапазоне. Создание​ поставленных задачах для​также поддерживается в​Допустим, в таблице ниже​ можете вычислить наибольшее,​ Узнайте больше об​ критерию.Примеры: Числовые и​ текста, Функции НАЙТИ​ книге Excel.​ 2010 и более​ ознакомительный урок, раскрывающий​ то они будут​ в Microsoft Excel​ с данными, которые​ с таблицами и​ показано на рисунке​

​ Создайте таблицу, так​ счетчика элемента управления​ работы с текстом​ Excel для совместимости. ​ собрана статистика по​

​ наименьшее или среднее​ объектах Workbook и​

Создание диаграмм

​ текстовые фильтры, Фильтры​ и ПОИСК, Функции​Примеры: Лист Excel в​ ранних версиях).​ основные понятия и​ автоматически включены в​С помощью макросов, в​ вы хотите визуально​ числовыми выражениями, автоматизировав​ и жмем ОК​ как показано на​ интерфейсом формы. Автоматическое​

​ в ячейках таблиц.​Как известно, категория​ стоимости лекарств в​ значение, подсчитать количество​ Worksheet в VBA.​ по дате, Расширенный​ ПОДСТАВИТЬ и ЗАМЕНИТЬ.​ Word, Сохранить в​Примеры: Масштаб, Разделяем лист,​ положения, и несколько​ эту таблицу. Кроме​

​ программе Microsoft Excel​ отобразить. Затем, находясь​ её. Этого удается​ (обратите внимание вторая​ рисунке:​ обновление итоговых данных​

​ Как изменить несколько​Статистические​ городе. В одной​ ячеек, содержащих заданную​Объект Range: Объект Range​ фильтр, Форма данных,​

​Функции поиска и ссылок:​ PDF, Общий доступ​

Формулы в EXCEL

​ Закрепление областей, Группировка​ простых и понятных​ того, при прокрутке​ можно записывать выполнение​ во вкладке «Вставка»,​ достичь с помощью​ ссылка $A$4 -​В ячейках D5 и​ при заполнении таблицы. ​ символов в текстовой​в Excel содержит​ аптеке лекарство стоит​

​ информацию, и т.д.​ – это ячейка​ Удаляем дубликаты, Структурирование​ Узнайте всё о​ к книге, SkyDrive,​ листов, Консолидация, Просмотр​ примеров. Хотите узнать​ вниз, шапка таблицы​ определенных действий, а​ выбрать на ленте​ инструментария данного приложения,​ абсолютная):​ E5 введем функции,​Примеры работы функции EXP​ строке?​ более 100 самых​ дороже, в другой​Данная категория содержит более​ (или ячейки) Вашего​

​ данных.​ функциях Excel для​ Excel Online, Импорт​ нескольких листов, Проверка​ об Excel намного​ будет постоянно в​ потом воспроизводить их​ тот тип диаграммы,​ и различных его​

​Функцию из D7 копируем​ которые помогут получить​

Функция «ЕСЛИ»

​ для возведения числа​Примеры использования функции СТРОКА​ разноплановых функций. Но,​ дешевле. Чтобы посчитать​ 100 самых различных​ листа. Это самый​Условное форматирование: Условное форматирование​ работы со ссылками​ данных из Access,​ правописания.

​ больше? Немедленно приступайте​

​ пределах области зрения.​ автоматически. Это существенно​ который считаете наиболее​ функций. Давайте рассмотрим​ в E7.​

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

​ важный объект VBA.​ в Excel позволяет​

Макросы

​ и массивами, таких​ Microsoft Query, Импорт​Форматирование ячеек: При форматировании​ к изучению Microsoft​Урок: Создание таблицы в​ экономит время на​ подходящим для достижения​ наиболее полезные функции​Описание аргументов функции: =ЕСЛИ().​

​ успеваемости по урокам​Пример применения функции​ Excel.​ львиная доля этих​ среднем по городу,​

​ часть из которых​Переменные: В этом разделе​ выделить ячейки определённым​ как ВПР, ГПР,​

​ и экспорт текстовых​ ячеек в Excel,​

Условное форматирование

​ Excel вместе с​ Microsoft Excel​ выполнении большого количества​ поставленных целей. ​ программы Microsoft Excel.​ В ячейке A4​ Английского и Математики.​ EXP в лабораторных​Полезные формулы с​ функций практически не​ воспользуемся следующей формулой:​

​ предназначена исключительно для​ Вы научитесь объявлять,​ цветом в зависимости​ ПОИСКПОЗ, ИНДЕКС и​ файлов, XML.​ мы изменяем лишь​ Офис-Гуру! С нами​С помощью функции подбора​ однотипной работы.​Более точная настройка диаграмм,​Скачать последнюю версию​ у нас количество​ Ячейка E4 не​ исследованиях и анализах.​ использованием функции СТРОКА​

​ применяется, а особенно​

​Если требуется соблюсти несколько​ статистических расчетов и​

«Умная» таблица

​ инициализировать и отображать​ от содержащихся в​ ВЫБОР.​Защита: Зашифруйте файл Excel​ внешний вид содержимого,​ вам будет гораздо​ параметров, можно подобрать​Макросы можно записывать, просто​ включая установку её​ Excel​ всех студентов, а​

​ имеет значения, поэтому​ Применение функции EXP​ на готовых примерах:​ начинающими пользователями. В​ условий, то всегда​ обычному рядовому пользователю​ переменную в VBA.​ них значений.Примеры: Управление​Примеры: Налоговые ставки, Функция​ при помощи пароля.​ не изменяя самого​ проще!​

​ исходные данные, исходя​ включив запись своих​ наименования и наименования​Одной из самых востребованных​ в ячейке D6​ результат будет вычислен​ для финансовых расчетов​

​ удаление повторяющихся значений,​ этом уроке мы​ можно применить статистическую​ покажется темным лесом.​Выражение «If Then»: Используйте​ правилами, Гистограммы, Цветовые​ СМЕЩ, «Левый” поиск,​ Только зная пароль,​ значения.​Данный курс по Excel​ из конечного нужного​ действий в программе,​ осей, производится в​ функций в программе​ и E6 –​

​ из 2 оценок.​ при анализе вложений​

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

​ нумерация ячеек в​ постарались познакомить Вас​ функцию​ Мы же в​ выражение «If Then»​

​ шкалы, Наборы значков,​ Двумерный поиск, Поиск​ можно будет открыть​Примеры: Десятичные знаки, Денежный​ – это перевод​ для вас результата. ​ через соответствующую кнопку​ группе вкладок «Работа​ Microsoft Excel является​ количество оценок. Функция​Перейдите в ячейку D5.​ инвестиций на депозитные​

​ таблице, выбор значения​ только с самыми​СРЗНАЧЕСЛИМН​ рамках этого урока​ в VBA, чтобы​ Новое правило, Поиск​ максимального значения, Функция​ этот файл.​ против финансового, Форматы​ очень популярного англоязычного​Для того, чтобы использовать​ на ленте.​ с диаграммами».​ ВПР (VLOOKUP). С​

​ ЕСЛИ() проверяет, совпадают​Выберите инструмент из выпадающего​

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

​ счета в банк.​ по условию.​ популярными статистическими функциями​, которая позволяет считать​ рассмотрим самые полезные​ выполнять строки кода,​ дубликатов, Закрашивание чередующихся​ ДВССЫЛ.​Примеры: Защита книги, Защита​

​ даты и времени,​ сайта-самоучителя – excel-easy.com,​

​ эту функцию, нужно​Также, запись макросов можно​Одним из видов диаграмм​ помощью данной функции,​ ли показатели D6​ списка: «Главная»-«Сумма»-«Среднее». В​Примеры формул с функциями​Функция ВЕРОЯТНОСТЬ для расчета​

​ Excel, которые Вы​

lumpics.ru>

300 примеров по Excel

​ среднее арифметическое ячеек,​ и распространенные функции​ если встречается определённое​ строк, Сравнение двух​Финансовые функции: Этот раздел​ листа, Блокировка ячеек,​ Дроби, Текст в​ который посещают десятки​​ находиться во вкладке​ производить, используя язык​ являются графики. Принцип​​ можно значения одной​ и E6 со​ данном выпадающем списке​ ИНДЕКС и ПОИСКПОЗ​ вероятности событий в​ рано или поздно​ удовлетворяющих двум и​

• ​ данной категории.​
• ​ условие.​
• ​ списков, Конфликт правил,​
• ​ рассказывает о наиболее​
• ​ Книга только для​

​ число, Число в​ тысяч человек ежедневно!​ «Данные». Затем, требуется​ разметки Visual Basic,​ построения их тот​ или нескольких таблиц,​ значением A4. Если​ находятся часто используемые​ СУММПРОИЗВ в Excel. ​ Excel.​ сможете применить на​ более критериям.​В рамках данной статьи​Цикл: Циклы – это​ Чеклист.​

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

Введение

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

​ практике. Надеюсь, что​Статистическая функция​ мы не будем​ мощнейшая из техник​Диаграммы: Простейшая диаграмма в​ Excel.​ окончательную.​ форматы, Формат по​ вас он не​ «Анализ «что если»»,​Урок: Применение макросов в​

1. ​ у остальных типов​ При этом, поиск​ ответ ДА, а​Диапазон определяется автоматически, остается​ функций ИНДЕКС и​ вероятности возникновения события​ данный урок был​МАКС​

   ​ затрагивать такие популярные​ программирования. Цикл в​ Excel может быть​Примеры: Кредиты различной длительности,​Узнайте, как функции в​ образцу, Стили ячеек,​

2. ​ оставит равнодушными!​ которая располагается в​ Microsoft Excel​ диаграмм. ​ производится только в​ если нет –​

   ​ только нажать Enter.​ ПОИСКПОЗ, СУММПРОИЗВ как​ путем простых вычислений​ для Вас полезен.​возвращает наибольшее значение​

Основы

​ статистические функции Excel,​ VBA позволяет Вам​ более красноречивой, чем​

1. ​ Инвестиции и аннуитет,​ Excel помогают сэкономить​ Темы.​Этот раздел предназначен для​ блоке инструментов «Работа​Для того, чтобы выделить​

   ​Урок: Применение диаграмм в​ первом столбце таблицы.​

2. ​ ответ НЕТ.​Функцию, которую теперь содержит​ альтернатива формулам массива.​ с использованием статистической​ Удачи Вам и​

   ​ в диапазоне ячеек:​ как​ обрабатывать диапазон ячеек​ таблица полная чисел.​

3. ​ Расчёт сложных процентов,​ время. Если Вы​Найти и выделить: В​ пользователей, не знакомых​ с данными». Потом,​ определенные данные в​ Microsoft Excel​ Тем самым, при​В логическом выражении для​ ячейка D5, скопируйте​

   ​ Формулы для поиска​ функции ВЕРОЯТНОСТЬ. ​ успехов в изучении​Статистическая функция​СЧЕТ​

4. ​ снова и снова,​ Вы увидите: диаграммы​ График погашения кредита,​ только начинаете знакомство​ этой главе Вы​ с Excel.​

   ​ выбрать в появившемся​ таблице применяется функция​Для работы с числовыми​ изменении данных в​ удобства применяется 2​ в ячейку E5.​ значений по столбцам​Примеры функции ПОДСТАВИТЬ для​ Excel.​

5. ​МИН​и​ написав для этого​ – это не​ Амортизация.​ с функциями в​

   ​ научитесь использовать два​Microsoft Excel – одно​ списке пункт «Подбор​ условного форматирования. С​ данными в программе​

6. ​ таблице-источнике, автоматически формируются​ типа ссылок: относительная​Функция среднее значение в​ таблицы.​ замены текста в​Автор: Антон Андронов​возвращает наименьшее значение​СЧЕТЕСЛИ​ всего лишь несколько​ сложно.Примеры: Гистограмма, График,​

   ​Статистические функции: Здесь Вы​ Excel, то рекомендуем​ очень полезных инструмента​ из самых широко​

7. ​ параметра…». ​ помощью этого инструмента,​ Microsoft Excel удобно​ данные и в​ и абсолютная. Это​ Excel: =СРЗНАЧ() в​

   ​Функция РИМСКОЕ в Excel​ ячейке Excel.​Примеры использования функций​ в диапазоне ячеек:​, для них подготовлен​

8. ​ строк кода.​ Круговая диаграмма, Линейчатая​ найдёте обзор самых​ Вам сначала познакомиться​

   ​ Excel: Найти и​ используемых приложений в​

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

   ​ для перевода арабских​Функция ПОДСТАВИТЬ предназначена​ в формулах. Описание​Возвращает n-ое по величине​ отдельный урок.​Ошибки макросов: Этот раздел​ диаграмма, Диаграмма с​ полезных статистических функций​

10. ​ с главой Введение​ заменить и Перейти.​ истории. Сотни миллионов​ В поле «Установить​ выделения ячеек. Само​ С их помощью​ которой могут выполняться​ формулу без ошибок​ текст. Так же​ чисел в римские. ​ для работы с​

    ​ предназначения и назначения​ значение из массива​Статистическая функция​ научит справляться с​ областями, Точечная диаграмма,​ Excel.​ в формулы и​Примеры: Особенности инструмента «Найти»,​ людей во всём​

11. ​ в ячейке» вы​ условное форматирование можно​ можно производить различные​ отдельные расчеты. Например,​ в результатах ее​

    ​ она проигнорирует пустую​Примеры использования функции​ текстовыми данными. Часто​ атрибутов в популярных​ числовых данных. Например,​

Функции

​СРЗНАЧ​ ошибками макросов в​ Ряды данных, Оси,​Примеры: Отрицательные числа на​ функции.​ Удаляем пустые строки,​ мире пользуются Microsoft​ должны указать ссылку​ выполнить в виде​ арифметические действия с​

1. ​ данные из таблицы,​ вычисления.​ ячейку. Но если​ РИМСКОЕ в переводе​ используется, когда необходимо​ функциях.​ на рисунке ниже​возвращает среднее арифметическое​ Excel.​

   ​ Лист диаграммы, Линия​ нули, Случайные числа,​СЧЁТ и СУММ: Чаще​ Отличия по строкам,​ Excel. Excel умеет​

2. ​ на ячейку, которая​ гистограммы, цветовой шкалы​ данными в таблицах:​ в которой находятся​

   ​Примечание. Закладка «Формулы» предоставляет​ в ячейке будет​

3. ​ арабских чисел в​ подставлять значения в​Функция ГРАДУСЫ для выполнения​ мы нашли пятое​ своих аргументов.​Операции со строками: В​ тренда, Предел погрешностей,​ Функция РАНГ, ПЕРСЕНТИЛЬ​

   ​ всего в Excel​ Копируем только видимые​ работать с данными​

4. ​ содержит нужную формулу.​ или набора значков.​ сложение, вычитание, умножение,​ прейскуранты цен на​ доступ только к​ значение 0, то​ римские цифры. Как​ строку из другой​ геометрических расчетов в​ по величине значение​

   ​Данная функция может принимать​ этом разделе Вы​ Спарклайны, Комбинированная диаграмма,​ и КВАРТИЛЬ, ПРЕДСКАЗ​ используются функции, которые​ ячейки, Строка поиска.​

5. ​ любого типа и​ В поле «Значение»​Для того, чтобы перейти​ деление, возведение в​ товары, могут использоваться​

   ​ наиболее часто используемым​ результат естественно измениться. ​ переводить, преобразовать, конвертировать​ ячейки.​ Excel.​ из списка.​ до 255 аргументов​ найдёте сведения о​ Диаграмма-спидометр, Диаграмма-термометр, Диаграмма​

6. ​ и ТЕНДЕНЦИЯ.​ подсчитывают количество и​Шаблоны: Вместо того, чтобы​ выполнять финансовые, математические​ должен быть указан​ к условному форматированию,​ степень извлечение корня,​ для расчета показателей​

   ​ функциям. Больше можно​В Excel еще существует​ и заменить арабские​Примеры функции ТЕНДЕНЦИЯ в​Полезные примеры как​

7. ​Чтобы убедиться в этом,​ и находить среднее​ самых важных функциях​ Ганта, Диаграмма Парето.​

   ​Округление: В этом разделе​ сумму. Вы можете​ создавать рабочую книгу​ и статистические вычисления.​ конечный результат, который​

8. ​ нужно, находясь во​ и т.д.​ в таблице, об​ получить, вызвав окно​

   ​ функция =СРЗНАЧА() –​ цифры римскими?​ Excel для прогнозирования​ делать геометрические расчеты​ можно отсортировать числа​

9. ​ сразу в нескольких​ для работы со​Сводные таблицы: Сводные таблицы​ описаны три самых​ посчитать количество и​ Excel с чистого​Диапазон: Диапазон в Excel​

   ​ вы хотите получить. ​ вкладке «Главная», выделить​Для того, чтобы применить​

10. ​ объёме закупок в​ «Мастер функций» нажав​ среднее значение арифметическое​Функция КПЕР для расчета​ данных.​

    ​ используя в формулах​ в порядке возрастания:​ несмежных диапазонах и​

11. ​ строковыми элементами в​ – один из​ популярных функции для​ сумму значений, выбранных​ листа, Вы можете​ – это набор​ В поле «Изменяя​ диапазон ячеек, который​

    ​ формулу, нужно в​ денежном выражении.​ на кнопку «Вставить​ число. Она отличается​ количества периодов погашений​Примеры составления простого,​ функцию ГРАДУСЫ: расчет​Возвращает n-ое наименьшее значение​ ячейках:​ VBA.​ самых мощных инструментов​ округления чисел в​

Анализ данных

​ по одному или​ создать рабочую книгу​ из двух или​ значения ячейки» нужно​

1. ​ вы собираетесь отформатировать.​ ячейке, куда планируется​ВПР запускается путем вставки​ функцию» вначале строки​ от предыдущей тем,​ в Excel. ​ но эффективного прогнозирования​ траектории движения, вычисление​
2. ​ из массива числовых​Если в рассчитываемом диапазоне​Дата и время: Научитесь​ Excel. Сводная таблица​ Excel. Это функции​ по нескольким критериям.​ на базе шаблона.​ более ячеек. В​ указать координаты ячейки​ Далее, в группе​
3. ​ выводить результат, поставить​ оператора «ВПР» из​ формул. Или нажать​ что:​Примеры использования функции​ тенденции по собранным​ оборотов электродвигателя и​ данных. Например, на​ встречаются пустые или​ работать с датой​ позволит выделить суть​ ОКРУГЛ, ОКРУГЛВВЕРХ и​Примеры: Подсчёт вхождений текста,​
4. ​ Существует множество бесплатных​ этой главе Вы​ с корректируемым значением.​ инструментов «Стили» нажать​ знак «=». После​ Мастера функций в​ комбинацию клавиш SHIFT+F3.​=СРЗНАЧ() – пропускает ячейки​ КПЕР для расчетов​ данным статистики за​ др.​ рисунке ниже мы​ содержащие текст ячейки,​ и временем в​ из огромного набора​ ОКРУГЛВНИЗ.​
5. ​ Подсчёт логических значений,​ шаблонов, которые так​ найдёте обзор некоторых​Урок: Применение подбора параметров​ на кнопку, которая​ этого, вводится сама​ ту ячейку, где​
6. ​Функция =ОКРУГЛ() более​ которые не содержат​ сроков погашений кредита​ определенный период с​
7. ​Примеры работы функций РУБЛЬ​ нашли четвертое наименьшее​ то они игнорируются. ​ VBA.​
8. ​ данных.​Примеры: Отбрасываем десятичные знаки,​ Подсчёт пустых и​ и просятся, чтобы​ очень важных операций​ в Microsoft Excel​ так и называется​ формула, которая может​ данные должны отображаться.​
9. ​ точна и полезнее​ чисел;​ и вычисления реальной​ помощью использования функции​ РУБЛЬ.ДЕС и РУБЛЬ.ДРОБЬ​ значение из списка.​

VBA

​ В примере ниже​События: События – это​Таблицы: Таблицы нужны для​ Ближайшее кратное, Четные​ непустых ячеек, Функция​ их использовали.​ с диапазонами.​Возможности, которые предоставляет функция​

1. ​ «Условное форматирование». После​ состоять из математических​В появившемся, после запуска​ чем округление с​=СРЗНАЧА() – пропускает только​ суммы долга с​ ТЕНДЕНЦИЯ. 1 2​ в Excel.​
2. ​Если отсортировать числа в​ среднее ищется по​ действия, совершаемые пользователями,​ того, чтобы выполнять​ и нечетные.​ СУММПРОИЗВ.​
3. ​Примеры: Календарь, Бюджет, Планировщик​Примеры: Последовательность Фибоначчи, Пользовательские​ «ИНДЕКС», в чем-то​ этого, нужно выбрать​
4. ​ знаков, чисел, и​ этой функции окне,​ помощью формата ячеек. ​ пустые ячейки, а​ учетом переплаты и​
5. ​ 3 4 5​Функции РУБЛЬ, РУБЛЬ.ДЕС​ порядке возрастания, то​ четырем ячейкам, т.е.​
6. ​ которые запускают выполнение​ анализ имеющихся данных​Ошибки в формулах: Эта​Логические функции: Узнайте, как​ питания, Счет-фактура, Автоматизированный​ списки, Примечания, Скрытие​
7. ​ близки к возможностям​ тот вариант форматирования,​ адресов ячеек. Для​ нужно указать адрес​ В этом легко​ текстовые значения воспринимает​ процентной ставки.​ 6 7 8​ и РУБЛЬ.ДРОБЬ предназначены​
8. ​ все станет гораздо​(4+15+11+22)/4 = 13​ кода VBA.​ быстро и легко.​
9. ​ глава научит Вас​ пользоваться логическими функциями​ счет-фактура, Шаблоны по​ строк и столбцов,​ функции ВПР. Она​ который считаете наиболее​ того, чтобы указать​
10. ​ ячейки или диапазона​ убедиться на практике.​ как 0.​Примеры работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ​
11. ​ 9 10 11​ для финансовых расчетов.​ очевидней:​Если необходимо вычислить среднее,​
12. ​Массив: Массив – это​Анализ «Что-если»: Анализ «Что-если»​ справляться с некоторыми​ Excel, такими как​ умолчанию. ​ Пропускать пустые ячейки,​ также позволяет искать​ подходящим.​
13. ​ адрес ячейки, из​ ячеек, откуда данные​Создайте исходную таблицу так​​ в Excel.​ 12 13 14​ Вторые две активно​
14. ​Статистическая функция​ учитывая все ячейки​ группа переменных. В​ в Excel позволяет​ распространёнными ошибками в​ ЕСЛИ, И, ИЛИ.​Проверка данных: Используйте проверку​ Транспонирование, Объединение и​ данные в массиве​
15. ​Форматирование будет выполнено.​ которой берутся данные​ будут подтягиваться.​ как показано на​В ячейки D6 и​Примеры использования функции​
16. ​ 15 16 17​ применяются для финансового​МЕДИАНА​ диапазона, то можно​

​ VBA Вы можете​

office-guru.ru>

Статистические функции Excel, которые необходимо знать

​ подставлять различные значения​​ формулах Excel.​​Примеры: Вложенные функции ЕСЛИ,​ данных в Excel​ пересечение.​ значений, и возвращать​Урок: Применение условного форматирования​ для расчета, достаточно​Урок: Применение функции ВПР​ рисунке:​ E6 нашей таблицы​ ПОЛУЧИТЬ. ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для выборки​

​Сборник интересных функций​ анализа ценных бумаг.​возвращает медиану из​ воспользоваться статистической функцией​ сослаться на определённую​ (сценарии) в формулы.​Примеры: ЕСЛИОШИБКА, ЕОШИБКА, Циклическая​ Игра «Бросить кости».​ и будьте уверены,​Формулы и функции: Формула​ их в указанную​ в Microsoft Excel​ кликнуть по ней​

​ в Microsoft Excel​Ячейку B2 отформатируйте так,​ введите функцию подсчета​ отдельных значений из​ с практическими примерами,​​Функция ФИКСИРОВАННЫЙ для округления​​ заданного массива числовых​​СРЗНАЧА​​ переменную (элемент) массива,​Поиск Решения: В арсенале​

СРЗНАЧ()

​ ссылка, Зависимости формул,​​Ссылки на ячейки: Ссылка​​ что пользователи введут​ – это выражение,​

​ ячейку.​Не все пользователи знают,​ мышкой, и её​Ещё одной важной возможностью​ чтобы были отображены​ количества числовых значений.​

​ полей сводной таблицы. ​ картинками, подробным описанием​ котировок курсов в​ данных. Медианой называют​. В следующем примере​ используя для этого​ Excel есть инструмент,​​ Ошибка плавающей запятой.​

​ на ячейку –​ в ячейку только​ которое вычисляет значение​Синтаксис данной функции выглядит​​ что таблицу, просто​​ координаты появится в​ программы Excel является​ только 2 знака​ Функция позволит нам​​ Формулы для работы​​ синтаксиса и параметров.​

​ Excel.​​ число, которое является​​ среднее ищется уже​ имя массива и​ который называется «Поиск​Формулы массива: Изучив эту​

СРЗНАЧЕСЛИ()

​ это очень важный​ подходящее значение.​ ячейки. Функции –​ следующим образом: «ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца)».​ начерченную карандашом, или​​ ячейке для вывода​​ создание сводных таблиц.​ после запятой. Это​ узнать количество поставленных​

​ со сводными таблицами. ​Примеры формул с использованием​Функция Фиксированный идеально​ серединой числового множества.​ по 6 ячейкам,​ числовой индекс элемента.​ Решения». Он использует​ главу, Вы будете​​ элемент Excel. Поймите​​Примеры: Отбросить недопустимые даты,​ это предопределённые формулы,​Это далеко не полный​ при помощи границы,​ результата.​ С помощью данной​ можно сделать с​

​ оценок.​Функция ЭФФЕКТ для расчета​ функций ИЛИ И​ подходит для трейдеров,​ Если в списке​ т.е.​Function и Sub: В​ различные методы анализа​ понимать, что такое​ разницу между относительной,​

​ Ограничение бюджета, Предотвращение​ доступные в Excel.​ перечень всех функций,​ программа Microsoft Excel​​Также, программу Microsoft Excel​​ функции, можно группировать​ помощью диалогового окна​Перейдите в ячейку D6​ годовой процентной ставки​

МАКС()

​ ЕСЛИ в Excel.​​ брокеров и финансовых​​ нечетное количество значений,​(4+15+11+22)/6 = 8,6(6)​

МИН()

​ Visual Basic for​​ вычислений для поиска​​ формулы массива в​ абсолютной и смешанной​

НАИБОЛЬШИЙ()

​ дублирования записей, Коды​Примеры: Процентное изменение, Имена​ которые доступны в​ воспринимает, как простую​ можно использовать и​ данные из других​ «Формат ячеек» или​

​ и выберите инструмент​ в Excel . ​Примеры логических функций​

НАИМЕНЬШИЙ()

​ аналитиков, которым нужно​ то функция возвращает​.​ Applications функция (Function)​ наилучших решений для​ Excel. Одноячеечные формулы​

​ ссылками, и Вы​ продуктов, Выпадающий список,​ в формулах, Динамический​ программе Microsoft Excel.​

МЕДИАНА()

​ область ячеек. Для​​ в качестве обычного​​ таблиц по различным​ инструментом расположенном на​ из выпадающего списка:​Примеры работы функции​ ЕСЛИ, И, ИЛИ​ быстро и качественно​ то, что находится​Статистическая функция​ может возвращать значение,​ задач оптимизации всех​ массива позволяют выполнять​ наверняка добьётесь успеха!​ Зависимые выпадающие списки.​ именованный диапазон, Параметры​

​ Мы остановили внимание​ того, чтобы этот​ калькулятора. Для этого,​ критериям, а также​

​ закладке «Главная»-«Уменьшить разрядность»​ «Главная»-«Сумма»-«Число».​ ЭФФЕКТ при расчете​ в формулах для​

МОДА()

​ округлять котировки цен​ ровно по середине. ​СРЗНАЧ​

​ в то время​ видов.​ сразу серию вычислений​Примеры: Копируем точную формулу,​

​Сочетания клавиш: Сочетания клавиш​​ вставки, Строка состояния,​​ только на самых​ набор данных воспринимался​ в строке формул​ производить различные расчеты​В столбце C проставьте​​На этот рас нам​​ эффективных годовых процентных​​ выборки значений при​​ по ценным бумагам​ Если же количество​

​может использовать в​​ как процедура (Sub)​​Пакет анализа: Пакет анализа​ в одной ячейке.​ 3D ссылка, Создание​ позволяют увеличивать скорость​ Быстрые операции.​ популярных, и наиболее​ именно как таблица,​ или в любой​ с ними (суммировать,​ формулу вычитания 1,25​ не подходит автоматическое​ ставок по разным​ условии. Как использовать​ и курсам валют.​ значений четное, то​ качестве своих аргументов​ – не может.​ – это надстройка​Примеры: Подсчёт ошибок, Подсчёт​ внешней ссылки, Гиперссылки.​ работы, используя клавиатуру​

​Этот раздел даёт базовые​

office-guru. ru>

Обзоры использования самых популярных функций в Excel

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

Интересные примеры часто используемых функций

​ определение диапазона ячеек,​ банковским вкладам и​ функции И ИЛИ​
​ ​ функция возвращает среднее​ математические операторы и​Объект Application: Главным в​ Excel, которая предоставляет​ уникальных значений, Подсчёт​Дата и время: Чтобы​​ вместо мыши.​ понятия по работе​Автор: Максим Тютюшев​
​Делается это просто. Для​ математические выражения после​ т.д.), а результаты​ B: =B-1,25.​ поэтому его нужно​ депозитным счетам с​​ ЕСЛИ в формуле?​Функции СИМВОЛ ЗНАК ТИП​ для двух чисел.​
​ различные функции Excel:​ иерархии объектов является​ инструменты для анализа​ с критерием «ИЛИ»,​ ввести дату в​Примеры: Функциональные клавиши, Подсказки​ в Excel.​Ищите примеры работы в​ начала, выделяем нужный​​ знака «=».​ выводить в отдельную​Теперь вы знаете, как​ исправить на D2:D4. ​
​ простыми или сложными​Примеры расчетов функций КОВАРИАЦИЯ.В​ в Excel и​Например, на рисунке ниже​Если необходимо вернуть среднее​ сам Excel. Мы​​ финансовых, статистических и​ Суммирование каждой n-ой​ Excel, используйте символы-разделители:​
​ клавиш.​Лента меню: Когда Вы​ Excel? Хотите найти​ диапазон с данными,​Урок: Применение формул в​ таблицу. При этом,​ пользоваться функцией ОКРУГЛ​ После чего жмем​​ процентами. 1 2​ и КОВАРИАЦИЯ.Г в​ примеры работы их​
​ формула возвращает медиану​ арифметическое значений, которые​ называем его объект​ технических данных.​ строки, Суммирование наибольших​ слеш (/), тире​Печать: Эта глава научит​ запускаете Excel, на​​ понятную инструкцию, которая​ а затем, находясь​ Microsoft Excel​
​ существуют очень широкие​ в Excel.​ Enter.​ 3 4 5​ Excel .​ формул.​ для списка, состоящего​​ удовлетворяют определенному условию,​ Application. Объект Application​VBA (Visual Basic for​
​ чисел, Суммирование диапазона​ (-) или точку​ вас отправлять листы​ Ленте меню открывается​ позволит в совершенстве​​ во вкладке «Главная»,​Одной из самых популярных​ возможности по настройке​
​Описание аргументов функции =ОКРУГЛ():​Из D6 в ячейку​ 6 7 8​Примеры использования функций​Примеры формул для​ из 14 чисел. ​ то можно воспользоваться​​ открывает доступ ко​ Applications) – это​ с ошибками, Суммирование​
​ (.). Чтобы ввести​ Excel на печать​ вкладка Главная. Узнайте,​ овладеть великим множеством​ кликаем по кнопке​ функций, которые используются​ полей сводной таблицы.​Первый аргумент – это​ E6 скопируйте функцию​ 9 10 11​ КОВАРИАЦИЯ.В, КОВАРИАЦИЯ.Г и​ использования вспомогательных, но​

exceltable.com>

Самые интересные функции для работы в Excel

​Если отсортировать значения в​ статистической функцией​ многим параметрам, связанным​ название языка программирования​

Синтаксис и параметры функций

​ с критерием «ИЛИ»,​ время, используйте двоеточие​ и задавать некоторые​
​ как сворачивать и​ инструментов Excel, причем​ «Форматировать как таблицу».​ в Excel, является​Сводную таблицу можно создать​ ссылка на ячейку​ =СЧЕТ() – это​​ 12 13 14​ КОВАР для определения​ очень полезных функций​
​ порядке возрастания, то​СРЗНАЧЕСЛИ​ с самим приложением​ для Microsoft Office. ​ Поиск в двух​ (:). Дату и​ важные настройки при​​ настраивать Ленту.​ быстро и легко?​ После этого, появляется​
​ функция «ЕСЛИ». С​ во вкладке «Вставка»,​ значение, которой нужно​ функция Excel для​ 15 16 17​ взаимосвязей между разными​​ ТИП, ЗНАК и​ все становится на​. Следующая формула вычисляет​
​ Excel.​ В данном разделе​ столбцах, Наиболее часто​ время можно вводить​ печати.​Примеры: Панель быстрого доступа,​ Предлагаем Вашему вниманию​ список с различными​​ её помощью можно​ нажав на кнопку»​ округлить.​
​ подсчета количества не​Функции в Excel –​ наборами данных. Расчет​ СИМВОЛ для быстрого​ много понятней:​ среднее чисел, которые​Элементы управления ActiveX: Научитесь​ описаны возможности и​​ встречающееся слово, Система​ в одну ячейку.​Примеры: Режимы просмотра, Разрывы​
​ Вкладка Разработчик.​самоучитель по работе в​ вариантами стилей оформления​ задать в ячейке​ которая так и​Второй аргумент – это​ пустых ячеек.​​ это вычислительные инструменты​ и вычисление ковариации​ решения сложных задач. ​
​Возвращает наиболее часто встречающееся​ больше нуля:​ создавать элементы управления​ примеры использования VBA​ линейных уравнений.​Примеры: Функция РАЗНДАТ, Будние​ страниц, Верхние и​​Рабочая книга: Книгой называют​ Excel на простых​ таблицы. Выбираем наиболее​
​ вывод одного результата​ называется «Сводная таблица».​ количество знаков после​На данном примере хорошо​ пригодны для самых​ разных показателей.​Примеры формул где используется​​ значение в массиве​В данном примере для​
​ ActiveX, такие как​ применительно к Excel.​Эта глава рассказывает о​ и рабочие дни,​ нижние колонтитулы, Номера​ файл Excel. Когда​​ примерах​ подходящий из них.​ при выполнении конкретного​
​Урок: Применение сводных таблиц​ запятой, которое нужно​ видно, что функция​ разных отраслей деятельности:​Функция РЯД.СУММ для расчета​ функция СТОЛБЕЦ в​ числовых данных.​ подсчета среднего и​ кнопки, текстовые поля,​Создание макроса: При помощи​ мощных инструментах, которые​ Дней до дня​ страниц, Печать заголовков,​

exceltable. com>

Работа с функциями в Excel на примерах

​ Вы запускаете Excel,​. Здесь Вы найдёте​Также, таблицу можно создать,​ условия, и другого​ в Microsoft Excel​ оставить после округления.​ =СЧЕТ() игнорирует ячейки,​ финансы, статистика, банковское​ суммы степенных рядов​

​ Excel.​Если отсортировать числа в​ проверки условия используется​ поля со списком​ VBA Вы можете​ предлагает Excel для​ рождения, Табель, Последний​ Центрирование на странице,​

Функция вычисления среднего числа

​ автоматически создаётся пустая​ ответы на самые​ нажав на кнопку​ результата, в случае​Для визуального отображения данных,​

​Внимание! Форматирование ячеек только​ которые не содержат​ дело, бухгалтерский учет,​ в Excel.​Сложные и простые​ порядке возрастания, то​ один и тот​ и так далее.​ автоматизировать задачи в​ анализа данных.​

1. ​ день месяца, Праздники,​
2. ​ Печать сетки и​ рабочая книга.​ различные вопросы и​ «Таблица», которая расположена​ его невыполнения. ​
3. ​ размещенных в таблице,​ отображает округление но​
4. ​ числа или пусты.​ инженерство и проектирование,​Примеры работы с​

​ формулы с примерами​ все становится гораздо​ же диапазон, что​Userform: В этой главе​ Excel, записывая так​Сортировка: В Excel Вы​ Квартал, День года.​ заголовков строк/столбцов, Область​Примеры: Сохраняем в формате​

​ научитесь применять Excel​ во вкладке «Вставка»,​Синтаксис данной функции выглядит​ можно использовать диаграммы.​ не изменяет значение,​В ячейки D7 и​

• ​ анализ исследовательских данных​ функцией РЯД.СУММ при​ использования функции СТОЛБЕЦ.​
• ​ понятней:​ не всегда удобно.​ Вы узнаете, как​ называемые макросы. В​

​ можете сортировать по​

Функция подсчета количества значений в Excel

1. ​Текстовые функции: Excel предлагает​ печати.​ Excel 97-2003, Просмотр​ на практике. Самоучитель​ предварительно выделив определенную​ следующим образом «ЕСЛИ(логическое​ Их можно применять​
2. ​ а =ОКРУГЛ() –​ E7 введем логическую​ и т. д.​ вычислении сложных процентов,​
3. ​ А также применение​Статистическая функция​ На этот случай​ создавать пользовательские формы​ этом разделе Вы​ одному или нескольким​ огромное количество функций​
4. ​Обмен данными и общий​ нескольких рабочих книг,​ состоит из пяти​ область листа с​ выражение; [результат если​ в целях создания​

​ округляет значение. Поэтому​ функцию, которая позволит​Лишь мизерная часть возможностей​ расчета экспоненциального роста,​ функции СТОЛБЕЦ для​

Функция ЕСЛИ в Excel

​МОДА​ у функции​ в VBA.​ научитесь создавать простой​ столбцам. Расположите данные​ для самых различных​ доступ: Узнайте, как​

1. ​ Рабочая область, Автовосстановление.​ разделов:​ данными.​
2. ​ истина]; [результат если​ презентаций, написания научных​ для вычислений и​ нам проверить, все​ вычислительных функций включено​ прогноз траектории с​ нестандартных решений в​
3. ​на данный момент​СРЗНАЧЕСЛИ​

​Автор: Антон Андронов​ макрос. ​ по убыванию или​ операций с текстовыми​ можно отправить данные​Рабочий лист: Лист –​Введение​После этого, выделенный набор​ ложь])».​ работ, в исследовательских​ расчетов нужно использовать​ ли студенты имеют​ в данный учебник​ учетом последовательности. ​ формулах при комбинации​

​ устарела, точнее, устарела​существует третий необязательный​Функции категории​MsgBox: MsgBox – это​ по возрастанию.Примеры: Сортировка​ строками.​ Excel в документ​ это набор ячеек,​

​Основы​ ячеек Microsoft Excel,​С помощью операторов «И»,​ целях, и т.д.​ функцию =ОКРУГЛ(), так​ оценки. Пример использования​ с уроками по​Примеры функции СМЕЩ для​ с другими функциями.​ ее форма записи.​

Как округлять числа в Excel

​ аргумент, по которому​Статистические​ диалоговое окно в​ по цвету, Обратный​Примеры: Разделение строки, Количество​ Word или в​

1. ​ в которых Вы​Функции​ будет воспринимать как​
2. ​ «ИЛИ» и вложенной​ Программа Microsoft Excel​ как форматирование ячеек​ функции ЕСЛИ:​ Excel. На простых​ прохода по диапазону​Примеры работы с текстовой​ Вместо нее теперь​ можно вычислять среднее.​
3. ​предназначены в первую​ VBA, при помощи​ список, Случайный список.​ экземпляров текста, Количество​

​ другие файлы. Кроме​ храните и обрабатываете​Анализ данных​

​ таблицу. Вследствие этого,​

1. ​ функции «ЕСЛИ», можно​ предоставляет широкий набор​ приведет к ошибочным​Перейдите в ячейку D7​
2. ​ примерах и далее​ ячеек в Excel.​ функцией ЗАМЕНИТЬ в​ используется функция​

​ Т.е. по первому​ очередь для анализа​ которого можно показывать​Фильтрация: Настройте фильтр для​ слов, Текст по​ этого, из множества​ данные. По умолчанию​VBA​ например, если вы​ задать соответствие нескольким​ инструментов для создания​

exceltable.com>

​ значениям в результатах.​

7 неизвестных функций Excel | EDUGET

Кто думает, что знает о программе Excel все — тот утверждает, что объял необъятное. Обычно мы используем только 5% от возможностей этой самой распространенной программы Microsoft Office и даже не интересуемся ее скрытыми функциями. А ведь Excel позволяет сравнивать прайс-листы, скрывать информацию, составлять самые сложные аналитические отчеты.

Продвинутые пользователи Excel – особая каста, которая не очень любит делиться секретами. А мы вам раскроем 7 функций Excel для учебы и бизнеса.

1. Скрытый лист
Довольно известен классический способ скрыть лист Excel – достаточно кликнуть правой кнопкой по названию листа и выбрать пункт «Скрыть», и другие пользователи не смогут увидеть содержимое. Однако имя скрытого листа и сам факт скрытия все равно будут видны. Сделать информацию абсолютно недосягаемой для других пользователей можно так:
— Нажать ALT+F11.
— В появившемся слева окне в верхней части выбрать номер листа.
— В нижней части окна в самом конце списка выбрать свойство Visible и далее выбрать опцию xlSheetVeryHidden. Теперь лист будет абсолютно невидимым для других пользователей.

2. Блокирование изменений задним числом
Мощный инструмент контроля за действиями сотрудников – запрет на изменение данных. Допустим, внесены данные о количестве проданного товара и вырученной сумме. Чтобы эти данные нельзя было изменить в пользу того или иного сотрудника, стоит использовать функцию запрета изменения данных задним числом. Порядок действий такой:
— Навести курсор на ячейку с датой в графе «Дата» и выбрать в меню пункт «Данные».
— Нажмите на кнопку «Проверка данных» и вызовите таблицу.
— В выпадающем списке «Тип данных» выберите «Другой».
— В графе «Формула» напишите =А2=СЕГОДНЯ().
— Уберите галочку с «Игнорировать пустые ячейки».
— Нажмите кнопку «ОК». Теперь, если кто-то захочет ввести другую дату, появится предупреждающая надпись.
— По такому же алгоритму можно запретить изменение данных в графе «Количество» и любой другой.

3. Запрет на ввод дублей
Очень полезная функция в работе с большими списками однотипных товаров или других наименований. Чтобы товары в прайс-листе не повторялись, можно установить запрет на ввод дублей.
— Выделите нужное количество ячеек, на которые будет распространяться запрет, например, А1:А10.
— Во вкладке «Данные» нажмите кнопку «Проверка данных».
— Во вкладке «Параметры» из выпадающего списка «Тип данных» выберите вариант «Другой».
— В графе «Формула» нужно вбить =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1. Не закрывайте окно.
— В этом же окне перейдите на вкладку «Сообщение об ошибке» и там введите предупреждающий текст.
— «ОК» — подтвердите завершение операции.

4. Выборочное суммирование
Для работы с постоянными клиентами пригодится функция, собирающая данные по одному заказчику из разных категорий товаров и разных транзакций. Чтобы узнать общую сумму, на которую Ivan купил, например, итальянской колбасы (Salame di Milano).
— В ячейку G4 введите имя заказчика Ivan.
— В ячейку G5 — название продукта Salame di Milano.
— Встаньте на ячейку G7, где и будет подсчитана сумма, и напишите для нее формулу {=СУММ((С3:С21=G4)*( B3:B21=G5)*D3:D21)}. Смысл формулы мы объясняем ниже:
— Сначала введите {=СУММ и откройте скобки, в которых будет три множителя.
— Первый множитель (С3:С21=G4) ищет в указанном списке клиентов упоминания Ivan.
— Второй множитель (B3:B21=G5) делает то же самое с Salame di Milano.
— Третий множитель D3:D21 отвечает за столбец стоимости, после него нужно закрыть скобки.
— Вместо Enter при написании формул в Excel вводите Ctrl + Shift + Enter.

5. Сводная таблица
Когда собирается много информации в одной таблице: кто, какому заказчику, какой товар и на какую сумму продал, в массиве данных сложно что-то найти. Чтобы получать из таблицы выборочные данные, например, у какого менеджера было больше продаж или на какую сумму продали справочников, можно использовать сводные таблицы Excel:
— Во вкладке «Вставка» нажмите кнопку «Сводная таблица».
— В появившемся окне подтвердите действие («ОК»).
— В появившемся окошке сформируйте новую таблицу, используя только интересующие данные.

6. Товарный чек
Посчитать общую сумму заказа можно двумя способами: традиционным и продвинутым. Традиционно: добавить столбец, где перемножить цену и количество, затем посчитать сумму этого столбца. Знаток Excel выберет новаторский путь:
— Выделить ячейку C7.
— Ввести =СУММ(.
— Выделить диапазон B2:B5.
— Ввести знак умножения Excel­ — звездочку.
— Выделить диапазон C2:C5 и закрыть скобку.
— Вместо Enter при написании формул в Excel вводите Ctrl + Shift + Enter.

7. Сравнение прайсов
Если нужно сравнить наличие товаров и цены в прайсах разного времени или разных компаний, можно воспользоваться специальными функциями Excel. Например:
— Создать в книге еще один лист и скопировать в него списки товаров и из первого, и из второго прайса.
— Чтобы избавиться от дублей товаров, выделяем весь список товаров, включая его название.
— В меню выбрать «Данные» — «Фильтр» — «Расширенный фильтр».
— В появившемся окне отметить: а) скопировать результат в другое место; б) поместить результат в диапазон — выбираем имя ячейки, куда нужно записать результат; в) поставить галочку на «Только уникальные записи».
— Нажать кнопку «ОК» и, начиная с ячейки D4, получить список без дублей.
— Удалить первоначальный список товаров.
— Добавить колонки для загрузки значений прайса за дату-1 и дату-2 и колонку сравнения.
— Ввести в колонку сравнения формулу =D5-C5, отвечающую за вычисление разницы.
— Чтобы автоматически загрузить в колонки «дата-1» и «дата-2» значения из прайсов, использовать функцию =ВПР(искомое_значение; таблица; номер_столбца; интервальный _просмотр).
— «Искомое_значение» — это строчка, которую мы будем искать в таблице прайса. Легче всего искать товары по их наименованию.
— «Таблица» — это массив данных, в котором мы будем искать нужное нам значение. Он должен ссылаться на таблицу, содержащую прайс от даты-2.
— «Номер_столбца» — порядковый номер столбца в диапазоне, заданном для поиска данных. Для поиска определена таблица из двух столбцов, во втором из которых – цена.
— Интервальный_просмотр. Если таблица, в которой нужно найти значение, отсортирована по возрастанию или по убыванию, ставьте значение ИСТИНА, если не отсортирована — пишете ЛОЖЬ.
— Протянуть формулу вниз, не забыв закрепить диапазоны. Для этого поставить перед буквой столбца и перед номером строки значок доллара (чтобы это сделать, выделите нужный диапазон и нажмите клавишу F4).
— В итоговом столбце отразится разница в ценах по тем позициям, которые есть и в том, и в другом прайсе. Если в итоговом столбце отражается #Н/Д, это значит, что указанный товар есть только в одном из прайсов, а следовательно, разницу вычислить невозможно.
 

При цитировании материалов раздела «Блог» на www.eduget.com активная ссылка на сам материал или на страницу www.eduget.com – обязательна. Любое использование материалов раздела «Статьи» на www.eduget.com (материала целиком) возможно исключительно по предварительному письменному разрешению правообладателя. Благодарим за сотрудничество!

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

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

Работа с функциями в Excel

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

Функция «ВПР»

Одной из самых востребованных функций в Microsoft Excel является «ВПР» («VLOOKUP)». Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. При этом поиск производится только в первом столбце таблицы, тем самым при изменении данных в таблице-источнике автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, сведения из таблицы, в которой находятся прейскуранты на товары, могут использоваться для расчета показателей в таблице об объеме закупок в денежном выражении.

ВПР запускается путем вставки оператора «ВПР» из «Мастера функций» в ту ячейку, где данные должны отображаться.

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

Урок: Применение функции «ВПР» в Microsoft Excel

Сводные таблицы

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

Создается она на вкладке «Вставка» нажатием на кнопку, которая так и называется — «Сводная таблица».

Урок: Применение сводных таблиц в Microsoft Excel

Создание диаграмм

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

Чтобы создать диаграмму, нужно выделить набор ячеек с данными, которые вы хотите визуально отобразить. Затем, находясь на вкладке «Вставка», выбрать на ленте тот тип диаграммы, который считаете наиболее подходящим для достижения поставленных целей.

Более точная настройка диаграмм, включая установку ее наименования и наименования осей, производится в группе вкладок «Работа с диаграммами».

Одним из видов диаграмм являются графики. Принцип построения их тот же, что и у остальных типов диаграмм.

Урок:
Применение диаграмм в Microsoft Excel
Построение графика в Microsoft Excel

Формулы в Excel

Для работы с числовыми данными в программе позволяется использовать специальные формулы. С их помощью можно производить различные арифметические действия с данными в таблицах: сложение, вычитание, умножение, деление, возведение в степень извлечение корня и т. д. Чтобы применить формулу, нужно в ячейке, куда планируется выводить результат, поставить знак «=». После этого вводится сама формула, которая может состоять из математических знаков, чисел и адресов ячеек. Для указания адреса ячейки, из которой берутся данные для расчета, достаточно кликнуть по ней мышкой, и ее координаты появится в ячейке для вывода результата.

Excel также удобно использовать и в качестве обычного калькулятора. Для этого в строке формул или в любой ячейке просто вводятся математические выражения после знака «=».

Урок: Применение формул в Microsoft Excel

Функция «ЕСЛИ»

Одной из самых популярных функций, которые используются в Excel, является «ЕСЛИ». Она дает возможность задать в ячейке вывод одного результата при выполнении конкретного условия и другого результата в случае его невыполнения. Ее синтаксис выглядит следующим образом: ЕСЛИ(логическое выражение; [результат если истина]; [результат если ложь]).

Операторами «И», «ИЛИ» и вложенной функцией «ЕСЛИ» задается соответствие нескольким условиям или одному из нескольких условий.

Урок: Применение функции «ЕСЛИ» в Microsoft Excel

Макросы

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

Запись макросов также можно производить, используя язык разметки Visual Basic в специальном редакторе.

Урок: Применение макросов в Microsoft Excel

Условное форматирование

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

Форматирование будет выполнено.

Урок: Применение условного форматирования в Microsoft Excel

«Умная» таблица

Не все пользователи знают, что таблицу, просто начерченную карандашом или границами, Excel воспринимает как простую область ячеек. Заставить программу видеть этот набор данных как таблицу можно через переформатирование. Делается это просто: для начала выделяем нужный диапазон с данными, а затем, находясь на вкладке «Главная», кликаем по кнопке «Форматировать как таблицу». Появится список с различными вариантами стилей оформления, где укажите подходящий.

Таблица также создается нажатием на кнопку «Таблица», которая расположена на вкладке «Вставка», предварительно выделив определенную область листа с данными.

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

Урок: Создание таблицы в Microsoft Excel

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

С помощью функции подбора параметров можно подобрать исходные данные, руководствуясь желаемым для вас результатом. Перейдите на вкладку «Данные» и нажмите кнопку «Анализ «что если»», расположенную в блоке инструментов «Работа с данными». В появившемся списке укажите пункт «Подбор параметра…».

Откроется окно подбора параметра. В поле «Установить в ячейке» вы должны указать ссылку на ячейку, которая содержит нужную формулу. В поле «Значение» должен быть указан конечный результат, который вы хотите получить. В поле «Изменяя значения ячейки» вставьте координаты ячейки с корректируемым значением.

Урок: Применение подбора параметров в Microsoft Excel

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

Возможности, которые предоставляет функция «ИНДЕКС», в чем-то близки к возможностям функции «ВПР». Она также позволяет искать данные в массиве значений и возвращать их в указанную ячейку. Синтаксис выглядит следующим образом: ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца).

Урок: Применение функции «ИНДЕКС» в Microsoft Excel

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

300 примеров по Excel — Информационные технологии

Уроки MS Excel

Работая с таблицами Excel, иногда возникает необходимость в распределении информации из одного столбца по

Уроки MS Excel

Тем людям, которые регулярно работают с таблицами Excel, нужно часто выполнять одни и те

Уроки MS Excel

Нередко пользователям приходится перенести часть информации с документа Microsoft Word в Excel формат, чтобы

Уроки MS Excel

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

Уроки MS Excel

Пользователю Excel нередко приходится сталкиваться с тем, чтобы определять, сколько строк содержит таблица. Чтобы

Уроки MS Excel

Excel – одна из лучших программ для аналитика данных. А почти каждому человеку на

Уроки MS Excel

Время от времени при работе с электронными таблицами появляется необходимость изменить положение нескольких рядов

Уроки MS Excel

Excel – удивительная программа, дающая возможность не только числовые данные обрабатывать. С ее помощью

Уроки MS Excel

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

Уроки MS Excel

Определение процента от числа – довольно частая задача, с которой приходится сталкиваться пользователю Ecxel,

Уроки MS Excel

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

Уроки MS Excel

Excel – невероятно функциональная программа, позволяющая не просто записывать данные в табличном виде, но

Уроки MS Excel

Стандартное обозначение строк в Excel – цифровое. Если же речь идет о столбцах, то

Уроки MS Excel

Набор функций у программы Excel, конечно, поистине огромный. В том числе, можно в определенной

Уроки MS Excel

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

Уроки MS Excel

Важно понимать, что Excel – это не только программа для создания баз данных, но

Умные Таблицы Excel – секреты эффективной работы

В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы». 

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

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

Как правило, ничего не меняем. После нажатия Ок исходный диапазон превратится в Таблицу Excel.

Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

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

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет[Продажи]

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи. 

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

1. Каждая Таблица имеет заголовки, которые обычно берутся из первой строки исходного диапазона.

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

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

Помимо указанных свойств есть возможность сделать дополнительные настройки.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

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

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

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

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Поделиться в социальных сетях:

Приемы :: Планета Excel

Поиск ключевых слов в тексте

Как быстро найти в исходном тексте все ключевые слова из справочника и вывести их рядом с каждой ячейкой данных через заданный символ-разделитель. Попутно разбираемся с буферизацией запросов в Power Query с помощью функции Table.Buffer для ускорения обработки.

Подробнее… Разделение таблицы по листам

Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию. Разбираем два способа — с обновлением (через Power Query) и без (только VBA).

Поиск последнего вхождения (инвертированный ВПР)

Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?

Распределяем список по наборам

Как разделить список по наборам неодинакового размера — разбор 3 способов: вручную через сводную, запросом в Power Query и функциями динамических массивов.

Вафельная диаграмма в Excel

2 способа построить в Excel вафельную диаграмму (waffle-chart) для наглядной визуализации прогресса по проекту или любых других KPI.

Массовая замена текста формулами

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

Регулярные выражения (RegExp) в Power Query

Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.

Производственный календарь в Excel

Как при помощи Power Query сделать в Excel «вечный» производственный календарь — автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.

ВПР и числа-как-текст

Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются «числа-как-текст», что приводит к ошибкам #Н/Д.

Ад Условного Форматирования

Что такое «Ад Условного Форматирования», когда и почему он возникает? Как с ним бороться вручную и при помощи специального макроса?

Самый быстрый ВПР

Тест скорости 7 разных вариантов реализации поиска и подстановки данных из одной таблицы в другую: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРХ, СУММЕСЛИ и т. д. Кто будет самым быстрым?

Выпадающий список с быстрым поиском

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

Динамические массивы в Excel

Подробный разбор революционно нового инструмента Excel — динамических массивов (Dynamic Arrays). Логика их работы, нюансы, плюсы и минусы, совместимость со старыми версиями.

Как открыть новый Excel в отдельном окне

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

Дубликаты внутри ячейки

Как бороться с повторами в тексте внутри ячейки: обнаруживать их, выделять цветом или удалять. С помощью формул, макросов или запросов Power Query.

Суперсила Мгновенного заполнения (Flash Fill)

Подробный разбор вариантов применения одного из самых удивительных, но малоизвестных инструментов — Мгновенного заполнения (Flash Fill). Поможет для обработки текста: нарезки, склейки, исправления регистра, добавления или удаления слов, извлечения чисел из текста и многого другого.

Генератор фраз из заданных фрагментов

Как быстро сгенерировать все возможные фразы, состоящие из заданных наборов слов в любом порядке, используя Декартово произведение множеств с помощью формул или Power Query.

Параметризация путей к данным в Power Query

При пересылке файлов с запросами Power Query они очень часто перестают работать, т.к. путь к исходным данным на другом ПК меняется. Как решить эту проблему и превратить путь в параметр (переменную), чтобы наш запрос работал на любом компьютере?

Запуск макроса по времени

Как запускать нужные вам макросы в определенное время, с определенной частотой или по расписанию? Например, автоматически обновлять тяжелый и медленный отчет каждое утро в 5:00?

Новые типы данных в Excel 2016

Подробный разбор особенностей использования двух новых типов данных География (Geography) и Акции (Stocks), появившихся в Microsoft Excel 2016.

Нечеткий текстовый поиск с Fuzzy Lookup в Excel

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

Последнее слово

Как извлечь из текста последнее слово или фрагмент по заданному символу-разделителю. Разбор нескольких способов (формулы, макросы, Power Query).

Как использовать Личную Книгу Макросов

Личная Книга Макросов (Personal Macro Workbook) — что это такое, как ее создать и использовать в качестве личной библиотеки для хранения и запуска самых нужных вам в повседневной работе макросов и функций.

Диаграмма Ганта в Power Query

Как с помощью Power Query быстро «размазать» бюджет по дням в списке проектов, построив упрощенный вариант диаграммы Ганта в Excel.

Анализ текста регулярными выражениями (RegExp) в Excel

Как с помощью регулярных выражений (Regular Expression = RegExp) находить и извлекать из текста фрагменты по заданным сложным шаблонам. Например, вытащить номер счета из описания платежа, город из адреса, телефон из контактов клиента и т.п.

10 лучших функций Excel | Бесплатная загрузка

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

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

Это 10 лучших функций Excel согласно нашему рейтингу.

Навыки ранжируются по степени полезности и получают оценку из 100. У каждого навыка также есть рейтинг сложности (из 5), основанный на сложности и сложности функции. Наконец, мы приводим среднее время в минутах, которое требуется среднему учащемуся, чтобы перейти от незнания к уровню владения языком.

1. Условное форматирование

Полезность: 100 | Сложность: 3 | Учиться за 180 минут

Разобраться в нашем шумном и богатом данными мире сложно, но жизненно важно.Вот почему так важна первая из 10 наших лучших функций Excel. При правильном использовании условное форматирование позволяет выявить закономерности вселенной, зафиксированные в вашей электронной таблице. Вот почему эксперты Excel и пользователи Excel одинаково считают эту функцию №1 самой важной. Это может быть сложно. Но даже самые простые изменения цвета могут принести огромную пользу. Предположим, у вас есть объемы, продаваемые торговым персоналом каждый месяц. Всего за три клика можно выявить 10% наиболее эффективных продавцов и начать содержательный деловой разговор.

2. Сводные таблицы

Коммунальные услуги: 95 | Сложность: 3 | Учиться за 240 минут

За четыре часа до овладения знаниями вам, возможно, придется отложить изучение сводных таблиц, но не стоит этого делать. Используйте их для сортировки, подсчета, суммирования или усреднения данных, хранящихся в одной большой электронной таблице, и отображения их в новой таблице, вырезая как хотите. Это критический момент. Если вы хотите смотреть только на показатели продаж для определенных стран, продуктовых линеек или каналов сбыта, это тривиально.Предупреждение: сначала убедитесь, что ваши данные чистые!

3. Специальная паста

Коммунальные услуги: 88 | Сложность: 3 | Выучить за 10 минут

Захват (т. е. копирование) некоторых данных из одной ячейки и вставка их в другую ячейку — одно из наиболее распространенных действий в Excel. Но вы можете многое скопировать (форматирование, значение, формула, комментарии и т. Д.), И иногда вам не захочется копировать все это. Наиболее распространенный пример этого — когда вы хотите потерять форматирование — эти данные размещаются в вашей собственной электронной таблице с вашим собственным стилем.

4. Добавить несколько строк

Коммунальные услуги: 87 | Сложность: 0 | Выучить за 10 минут

Вероятно, одно из наиболее часто выполняемых действий в электронных таблицах. Ctrl-Shift + — это ярлык, но на самом деле он занимает больше времени, поэтому мы рекомендуем щелкнуть правой кнопкой мыши. Если вы хотите добавить более одной, выберите столько строк или столбцов, сколько хотите добавить, а затем щелкните правой кнопкой мыши и добавьте.

5. Абсолютные ссылки

Коммунальные услуги: 85 | Сложность: 2 | Выучить за 15 минут

Незаменим! Доллар перед буквой фиксирует столбец, знак доллара перед числом фиксирует, строка F4 переключает четыре возможных комбинации.

6. Оптимизация печати

Коммунальные услуги: 84 | Сложность: 3 | Учиться за 120 минут

У всех проблемы с печатью из Excel. Но только представьте, если бы то, что вы печатали, всегда было именно тем, что вы собирались напечатать. Это действительно возможно. Однако есть несколько компонентов: предварительный просмотр печати, размещение на одной странице, настройка полей, выбор печати, печать заголовков, портретная или альбомная ориентация и дизайн электронной таблицы. Потратьте время, чтобы освоиться с этим.Вы будете выполнять эту задачу много-много раз в своей трудовой жизни.

7. Растянуть формулу поперек / вниз

Коммунальные услуги: 84 | Сложность: 1 | Выучить за 5 минут

Прелесть Excel в его легкой масштабируемости. Получите формулу один раз, и Excel произведет правильный расчет миллион раз. Прицел + удобен. Двойной щелчок по нему полностью опустит, если у вас есть непрерывные данные. Иногда копирование и вставка (обычная вставка или формулы вставки) будет для вас быстрее.

8. Заливка

Коммунальные услуги: 84 | Сложность: 2 | Выучить за 30 минут

Excel разработал собственный ум в 2013 году. Допустим, у вас есть два столбца имен, и вам нужно построить адреса электронной почты из них всех. Просто сделайте это для первой строки, и Excel поймет, что вы имеете в виду, и сделает то же самое для всего остального. До 2013 года это было возможно, но полагалось на комбинацию функций (НАЙТИ, ВЛЕВО, & и т. Д.). Это намного быстрее и БУДЕТ впечатлять людей.

9.ИНДЕКС-МАТЧ

Коммунальные услуги: 82 | Сложность: 4 | Выучить за 45 минут

Это одна из самых мощных комбинаций функций Excel. Вы можете использовать его для поиска значения в большой таблице данных и возврата соответствующего значения в этой таблице. Предположим, в вашей компании 10 000 сотрудников, и в ней есть таблица со всеми ними, в которой содержится много информации о них, например, зарплата, дата начала работы, линейный руководитель и т. Д. Но у вас есть команда из 20 человек, и вы действительно заинтересованы только в них. . INDEX-MATCH найдет значение членов вашей команды (они должны быть уникальными, например, адрес электронной почты или номер сотрудника) в этой таблице и вернет желаемую информацию для вашей команды.Об этом стоит подумать, так как он более гибкий и, следовательно, более мощный, чем ВПР.

10. Фильтры

Коммунальные услуги: 81 | Сложность: 2 | Учиться за 60 минут

Быстрый просмотр данных в таблице. Фильтрация эффективно скрывает данные, которые не представляют интереса. Обычно это значение, например, «Синие машины», которые вы ищете, и Фильтры покажут их и скроют все остальное. Но в более современных версиях Excel теперь вы также можете фильтровать по числовым значениям (например, по числовым значениям).грамм. больше чем, верхние 10% и т. д.) и цвет ячейки. Фильтрация становится более мощной, когда вам нужно отфильтровать несколько столбцов в комбинации, например. как цвета, так и автомобили, чтобы найти свою синюю машину.

Эта статья изначально была опубликована на Business Insider.

5 простых и мощных функций Excel, о которых вы, возможно, не знали

Сегодняшний пост в Excel написал Пурна Дуггирала, президент chandoo.орг.

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

Спарклайны

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

Чтобы создать свои собственные спарклайны, выберите диапазон данных и на ленте щелкните Вставить , а затем выберите тип спарклайнов — линия, столбец или выигрыш / проигрыш.Затем введите целевой диапазон, в котором вы хотите отобразить спарклайн. Вот и все.

Условное форматирование

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

Ниже приведена тепловая карта, на которой параметр цветовой шкалы был применен буквально за 10 секунд из тех же данных о продажах, которые использовались в приведенном выше примере «Спарклайны», чтобы проиллюстрировать высокие (темно-зеленые) и низкие (белые) данные о продажах по продуктам и времени.

Чтобы создать тепловую карту с использованием условного форматирования, выберите необработанные данные, а затем на ленте под заголовком Home щелкните Conditional Formatting > Color Scales , а затем выберите цветовую шкалу. Вы также можете настроить параметры цветовой шкалы, отредактировав правило форматирования.

МАЛЕНЬКИЕ и БОЛЬШИЕ функции

Хотя все знают функции MAX и MIN, очень немногие пользуются преимуществами LARGE и SMALL — функций, которые помогают найти первое, второе или n-е наибольшее (или наименьшее) значение в ваших данных.

Используя те же данные о продажах, что и в приведенных выше примерах, функция НАИБОЛЬШИЙ позволяет быстро определить два самых популярных продукта для каждого месяца наших данных. См. Пример ниже, сделанный с помощью ИНДЕКС и ПОИСКПОЗ, двух других очень полезных функций для анализа.

Чтобы получить дополнительную информацию об этих формулах, загрузите эту книгу: Мощные функции Excel.

Удалить дубликаты

В корпоративной жизни много постоянных, и очистка и систематизация данных — одна из них.Функция удаления дубликатов, представленная в Excel 2007, остается одной из моих любимых. Он очень прост в использовании и решает важную проблему, с которой мы все сталкиваемся, — дублирование данных.

Просто выберите данные, а затем на ленте под заголовком Data нажмите кнопку Remove Duplicates и наблюдайте, как Excel очищает ваши данные. Это так просто.

Ломтерезки

Думайте о слайсерах как о визуальных фильтрах. Они помогают быстро сузить набор данных и визуализировать его (либо в формате необработанных данных, либо с помощью подключенной диаграммы).Срезы были представлены в Excel 2010 и стали еще лучше в Excel 2013.

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

Чтобы добавить срез к диаграммам в Excel 2013, выберите диапазон данных и на ленте щелкните «Вставить»> «Слайсер », затем выберите часть данных, которую вы хотите использовать в качестве фильтра.В приведенном выше примере мы выбрали столбец Продукт. Тогда все готово!

Чтобы поиграть с интерактивной диаграммой и узнать больше об этой технике, загрузите эту книгу: Мощные функции Excel.

Присоединяйтесь ко мне на конференции PASS BA 2015

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

Приходите и присоединяйтесь ко мне в течение трех дней на конференции PASS Business Analytics в Санта-Кларе, Калифорния, 20-22 апреля, и проведите три дня потрясающих идей, интерактивных лабораторных работ, мощных демонстраций и полнодневного обучения различным расширенным аспектам Excel.Существует множество сеансов по всем аспектам Excel, от формул до тонкостей Power BI и других инструментов аналитики.

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

Зарегистрируйтесь сегодня на конференцию PASS Business Analytics!

—Пурна Дуггирала

20 уловок Excel, которые могут сделать любого экспертом по Excel

После того, как Microsoft разработала Excel до версии 2010 года, она преподнесла больше сюрпризов, чем когда-либо.Чтобы иметь дело с огромным количеством больших данных, нельзя игнорировать важную роль Excel в повседневной работе. Однако как для новичков, так и для опытных пользователей есть еще много полезных советов и приемов, которые неизбежно упускаются из виду. Вот 20 полезных секретов электронных таблиц Excel, о которых вы, возможно, не знали. Обратите внимание, что все эти функции основаны на Microsoft Excel 2010.

1. Выделить все одним щелчком мыши

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

2. Массовое открытие файлов Excel

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

3. Переход между разными файлами Excel

Когда у вас открыты разные электронные таблицы, переключение между разными файлами действительно раздражает, потому что иногда работа с неправильным листом может испортить весь проект.Используя Ctrl + Tab, вы можете свободно переключаться между разными файлами. Эта функция также применима к другим файлам, например, к различным вкладкам Windows в Firefox при открытии в Windows 7.

4. Создание нового контекстного меню

Обычно в верхнем меню есть три ярлыка: «Сохранить», «Отменить ввод» и «Повторный ввод». . Однако, если вы хотите использовать больше ярлыков, таких как «Копировать» и «Вырезать», вы можете настроить их следующим образом:

Файл-> Параметры-> Панель быстрого доступа, добавьте «Вырезать» и «Копировать» из левого столбца вправо, сохраните его.Вы увидите еще два ярлыка, добавленные в верхнее меню.

5. Добавьте диагональную линию к ячейке

Например, при создании списка адресов одноклассников вам может потребоваться диагональная ссылка в первой ячейке для разделения различных атрибутов строк и столбцов. Как это сделать? Всем известно, что Home-> Font-> Borders может изменять разные границы для ячейки и даже добавлять разные цвета. Однако, если вы нажмете «Больше границ», вы получите больше сюрпризов, например, диагональную линию. Щелкните его и сохраните — теперь вы можете сделать это немедленно.

6. Добавить более одной новой строки или столбца

Возможно, вы знаете, как добавить одну новую строку или столбец, но это действительно отнимает у вас много времени, если вам нужно вставить более одного из них, повторяя это действие X количество раз. Лучший способ — перетащить и выбрать X строк или столбцов (X — два или более), если вы хотите добавить X строк или столбцов сверху или слева. Щелкните правой кнопкой мыши выделенные строки или столбцы и выберите «Вставить» в раскрывающемся меню. Новые строки будут вставлены над строкой или слева от столбца, который вы выбрали первым.

7. Быстрое перемещение и копирование данных в ячейках

Если вы хотите переместить один столбец данных в электронной таблице, самый быстрый способ — выбрать его и переместить указатель к границе, после того как он превратится в значок перечеркнутой стрелки, перетащите, чтобы свободно перемещать столбец. Что делать, если вы хотите скопировать данные? Вы можете нажать кнопку Ctrl перед перетаскиванием для перемещения; новый столбец скопирует все выбранные данные.

8. Быстрое удаление пустых ячеек

Некоторые данные по умолчанию будут пустыми по разным причинам.Если вам нужно удалить их для поддержания точности, особенно при вычислении среднего значения, самый быстрый способ — отфильтровать все пустые ячейки и удалить их одним щелчком мыши. Выберите столбец, который вы хотите отфильтровать, перейдите в «Данные-> Фильтр», после того, как появится кнопка «вниз», отмените действие «Выбрать все», а затем выберите последний вариант «Пробелы». Все пустые ячейки отобразятся сразу. Вернитесь на главную и нажмите Удалить напрямую, все они будут удалены.

9. Расплывчатый поиск с подстановочными знаками

Возможно, вы знаете, как активировать быстрый поиск с помощью сочетания клавиш Ctrl + F, но есть два основных подстановочных знака — вопросительный знак и звездочка, которые используются в таблицах Excel для активации неопределенного поиска.Это используется, когда вы не уверены в целевом результате. Вопросительный знак обозначает один символ, а звездочка обозначает один или несколько символов. Что делать, если вам нужно искать вопросительный знак и звездочку в качестве целевого результата? Не забудьте добавить волновую линию впереди.

10. Создание уникального значения в столбце

Вы знаете о ключевой функции фильтра, но мало кто использует расширенный фильтр, который будет многократно применяться, когда вам нужно отфильтровать уникальное значение из данных в столбце.Щелкните, чтобы выбрать столбец, и перейдите к Data-> Advanced. Появится всплывающее окно. Как показано на скриншоте, нажмите Копировать в другое место, которое должно соответствовать второй красной прямоугольной области. Затем укажите целевое местоположение, введя значение или нажав кнопку выбора области. В этом примере уникальный возраст может быть сгенерирован из столбца C и отображен в столбце E. Не забудьте выбрать только уникальные записи, а затем нажмите OK. Уникальное значение, отображаемое в столбце E, может быть контрастом исходных данных в C, поэтому рекомендуется скопировать в другое место.

11. Ограничение ввода с помощью функции проверки данных

Чтобы сохранить достоверность данных, иногда необходимо ограничить вводимое значение и предложить несколько советов по дальнейшим действиям. Например, возраст в этой таблице должен быть целым числом, и все люди, участвующие в этом опросе, должны быть в возрасте от 18 до 60 лет. Чтобы убедиться, что данные за пределами этого возрастного диапазона не вводятся, перейдите в «Данные-> Проверка данных-> Настройка», введите условия и перейдите к «Входное сообщение», чтобы отобразить такие запросы, как: «Пожалуйста, введите свой возраст целым числом, с 18 до 60.»Пользователи получат это приглашение, когда повесят указатель в этой области, и получат предупреждающее сообщение, если введенная информация неверна.

12. Быстрая навигация с помощью Ctrl + кнопка со стрелкой

При нажатии Ctrl + любая кнопка со стрелкой на клавиатуре можно переходить к краю листа в разных направлениях. Если вы хотите перейти к нижней строке данных, просто попробуйте нажать Ctrl + кнопка вниз.

13. Транспонирование данных из строки в столбец

Эту функцию можно использовать, если вы хотите транспонировать данные для лучшего отображения; однако повторный ввод всех данных — последнее, что вам нужно сделать, если вы знаете, как использовать функцию Transpose в Paste.Вот как: скопируйте область, которую хотите транспонировать, переместите указатель в другое пустое место. Перейдите на главную-> Вставить-> Транспонировать, обратите внимание, что эта функция не активируется, пока вы сначала не скопируете данные.

14. Тщательно скрыть данные

Почти все пользователи знают, как скрыть данные, щелкнув правой кнопкой мыши для выбора функции «Скрыть», но это можно легко заметить, если данных совсем немного. Лучший и самый простой способ полностью скрыть данные — использовать функцию «Форматировать ячейки».Выберите область и перейдите в Главная-> Шрифт-> Ячейки открытого формата-> Числовая вкладка-> Пользовательский-> Тип ;;; -> Нажмите OK, тогда все значения в этой области станут невидимыми, и их можно будет найти только в области предварительного просмотра рядом с функциональной кнопкой.

15. Составьте текст с помощью &

В сложных формулировках нет необходимости, если вы знаете, как использовать &. С помощью этого символа вы можете свободно составлять любой текст. Ниже у меня четыре столбца с разными текстами, но что, если я хочу объединить их в одно значение в одной ячейке? Сначала найдите ячейку, которая должна отображать составленный результат, используйте формулировку с &, как показано на снимке экрана ниже.Нажмите Enter: все тексты в A2, B2, C2 и D2 будут объединены в LizaUSA25 @ в F2.

16. Преобразование текста

Используя все описанные здесь приемы, я изо всех сил старался избегать сложных формулировок. Но есть еще несколько простых и удобных формулировок, которые можно показать вам, например UPPER, LOWER и PROPER, которые могут преобразовывать тексты для различных целей. UPPER будет использовать все символы с большой буквы, LOWER может заменить текст на все строчные буквы, а PROPER будет использовать заглавную букву только для первого символа слова.

17. Входные значения, начинающиеся с 0

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

18. Ускорение ввода сложных терминов с помощью автозамены

Если вам нужно повторить одно и то же значение, и его сложно ввести, лучший способ — использовать функцию автозамены, которая заменит ваш текст правильным текстом.Возьмем, к примеру, мое имя, Лиза Браун, которое можно заменить на LZ. Следовательно, каждый раз, когда я ввожу LZ, он может автоматически заменяться на Liza Brown. Перейдите в Файл-> Параметры-> Правописание-> Параметры автозамены и введите «Заменить текст» правильным текстом в красной прямоугольной области, как показано ниже.

19. Один щелчок, чтобы получить больше статуса

Большинство пользователей знают, как проверить статус данных в нижней части листа Excel, например, Среднее и Суммарное значение. Однако знаете ли вы, что можно переместить указатель на нижнюю вкладку и щелкнуть правой кнопкой мыши, чтобы получить дополнительный статус, как показано ниже?

20.Переименование листа с помощью двойного щелчка

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

Хотите еще больше повысить свой уровень мастерства? Не пропустите эту статью:

Отличные ярлыки Excel, о которых мало кто знает

10 функций Excel, которые должен знать каждый

За последние 10 лет мы увидели волну инновационного программного обеспечения для работы с большими данными, предназначенного для анализа, обработки и визуализации данных.Тем не менее, для обычного специалиста Microsoft Excel, 30 лет спустя, остается продуктом, к которому обращаются люди, стремящиеся разобраться в данных. Сатья Наделла, генеральный директор Microsoft, утверждает, что Excel по-прежнему остается единственным продуктом Microsoft, превосходящим все остальные, и 750 миллионов специалистов по всему миру поддерживают это утверждение каждый день.

Мы обучаем и тестируем Microsoft Excel в течение десяти лет, и проведенный нами опрос нескольких сотен офисных сотрудников показал, что мы тратим более 10% своей рабочей жизни на электронные таблицы, а для тех, кто занимается исследованиями и разработками или финансами, это больше. вроде 30% или 2.5 часов в день.

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

Insight Center

В прошлом году мы составили «100 самых полезных советов по Excel», по поводу которых мы консультировались с экспертами по Excel и изучили десятки тысяч результатов тестов и данные об использовании курсов. Хотя у каждой функции Excel есть вариант использования, ни один человек не использует все функции Excel самостоятельно.Пройдите через 500+ функций, и вы получите около 100 действительно полезных функций и возможностей для большинства современных работников умственного труда. Мы выбрали десять из этого списка, которые особенно просты в освоении (всего около 2 часов) и которые могут существенно повлиять на продуктивность. Эти десять перечислены в порядке убывания полезности из нашего списка 100 лучших. Если, как и многие, вы не знаете, что изучать дальше в Excel, вы можете взглянуть на эту матрицу 2 × 2, которая учитывает полезность и время, необходимое для изучения навыка.

Специальная вставка (обучение 10 минут)

Копирование и вставка — одна из самых простых и часто используемых функций в Excel. Но мы часто переносим формат, который нам не нужен, или копируем формулу, когда вместо этого нам просто нужно значение.

Эти небольшие разочарования могут потребовать времени, чтобы исправить, поэтому Paste Special настолько… особенный. Специальная вставка позволяет вам выбрать, какие элементы скопированной ячейки вы переносите. После того, как вы скопировали ячейку (Ctrl + C), нажмите Ctrl + Alt + V (или перейдите в раздел «Буфер обмена» на главной ленте или «Правка»> «Специальная вставка»), чтобы вызвать Специальную вставку и сделать свой выбор. Alt + E + S + V — это ярлык для простой вставки значений — вероятно, наиболее распространенное использование Специальной вставки.

Добавить несколько строк (обучение 2 минуты)

Нам часто нужно добавлять новые строки между существующими строками. Ярлык (Ctrl, shift, +) очень удобен, тем более что вы можете переключать +, чтобы добавить несколько строк. Часто, просто выделив количество строк, которые вы хотите добавить (скажем, 5), и щелкнув правой кнопкой мыши , вставка выполняется быстрее при массовом добавлении, так как добавляется количество выделенных вами строк.

Flash Fill (обучение 30 минут)

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

Предположим, у вас есть список номеров продуктов в первых десяти ячейках столбца A, например От «ABC-00001» до «ABC-00010», и вам нужны только цифры после «-». Вы можете легко отказаться от «ABC», используя Flash Fill. До 2013 года это было возможно, но полагалось на комбинацию функций (НАЙТИ, ВЛЕВО, & и т. Д.).Теперь это намного быстрее, и произведет впечатление на людей.

Установите шаблон, введя «00001» в первую пустую ячейку. Если функция Flash Fill включена (Параметры файла, Дополнительно), просто начните вводить следующий номер продукта в ячейку ниже, и Flash Fill распознает шаблон и заполнит оставшиеся номера продуктов за вас. Просто нажмите клавишу Enter, чтобы принять. Или запустите его вручную, щелкнув Data> Flash Fill или Ctrl + E.

Flash Fill похож на волшебство и может использоваться во многих различных сценариях.Это фантастическая экономия времени, когда вам нужно быстро и точно ввести или изменить большой объем данных. Flash Fill — потрясающая функция.

ИНДЕКС-МАТЧ (обучение 45 минут)

Помимо ВПР (которая ищет значение в одном столбце и возвращает соответствующее значение из другого столбца), ИНДЕКС и ПОИСКПОЗ являются наиболее широко используемыми и наиболее мощными инструментами в Excel для выполнения поиска. При использовании по отдельности эти функции бесценны, но их истинная сила раскрывается только тогда, когда вы объединяете их.Использование INDEX и MATCH в комбинации помогает эффективно и точно извлекать нужные данные из большого набора данных. Освоение этих функций не только заставит вас выглядеть в глазах коллег и менеджера гением Excel, но и сделает утомительную, рутинную задачу быстрой и простой. Вот как работают эти функции:

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

Допустим, у вас есть таблица со списком продуктов. У вас есть столбцы с заголовками «Номер продукта», «Прибыль», «Название продукта» и «Доход». В другой таблице у вас есть список названий продуктов, и вы хотите узнать, сколько прибыли принесло каждый продукт. В этом сценарии мы используем название продукта (наше поисковое значение) для поиска прибыли. Название продукта находится справа от прибыли, поэтому функция ВПР не работает. Это идеальный сценарий для INDEX и MATCH.

Синтаксис будет:

= ИНДЕКС (столбец прибыли, ПОИСКПОЗ (значение поиска , столбец названия продукта, 0))

Вот хороший способ запомнить, как это работает:

= ИНДЕКС (столбец, из которого требуется возвращаемое значение , ПОИСКПОЗ (мое значение поиска, столбец, по которому я хочу выполнить поиск, введите «0»)) (ноль дает точное совпадение, вы можете сопоставить меньше, чем ( -1) или больше (1).)

На первый и даже второй взгляд INDEX и MATCH выглядят сложными.Это, безусловно, требует некоторой практики, но стоит задуматься, поскольку он более гибкий и мощный, чем ВПР. Это одно из самых важных применений Excel, точка.

SUM (2 минуты на обучение)

Это одна из первых функций, которые вы, вероятно, изучите в Excel — как суммировать строку или столбец. Но знаете ли вы, что можно выбрать ячейку в конце строки или столбца и нажать Alt + , чтобы выполнить эти функции за секунды?

CTRL Z / CTRL Y (обучение 1 минута)

Это ваша карточка Excel, позволяющая избежать освобождения из тюрьмы.Если вы не используете Ctrl Z для исправления ошибок в Excel, то вам следует это сделать. Многие люди не знают, что Ctrl Y делает обратное — повторяет. Они прекрасно работают в тандеме, и вы циклически выполняете итерации своей работы, пока не найдете подходящую.

Удаление дубликатов (обучение 10 минут)

Это так просто и быстро в использовании. Функция Remove Duplicates делает именно то, что вы ожидаете, — удаляет дубликаты в любом заданном диапазоне данных.Наш совет — удалить значения, которые вы хотите вывести, и поместить их на другой лист. Он находится на вкладке «Данные » в разделе «Инструменты данных » на ленте.

Если вы просто хотите выделить дубликаты, вы можете сделать это с помощью условного форматирования. Ярлык, чтобы попасть туда: Alt H L . (Или найдите его на ленте «Главная» в разделе «Стили»).

Freeze Panes (15 минут на изучение)

Когда-нибудь прокручивали вниз большую таблицу данных только для того, чтобы забыть, какие столбцы какие? Freeze Panes — вот ответ.Вы можете закрепить только верхнюю строку, первый столбец или любое их количество. Определите столбцы и строки области, которую вы хотите зафиксировать. Затем выберите ячейку справа от этих столбцов и под этими строками. Перейдите на вкладку «Просмотр» и закрепите области в разделе «Окно». Alt W F — это ярлык.

F4 (10 минут на обучение)

Есть два особенно приятных способа использования F4 в Excel. Первый — при создании абсолютной ссылки: F4 переключает вас между различными опциями.Второй вариант, о котором мало кто знает, но он может серьезно повысить продуктивность работы с Excel. F4 повторяет ваше последнее действие, если возможно. Например, если вы только что применили границу к одной ячейке, используйте F4, чтобы применить ее к другим.

CTRL + стрелки (обучение 5 минут)

Если вы обнаружили, что прокручиваете набор данных, чтобы добраться до его нижней части, остановитесь прямо сейчас и начните использовать Ctrl + клавиши со стрелками ! Этот простой ярлык приведет вас прямо к концу данных в столбце или строке, которые вы используете.Комбинируйте его с Ctrl, Shift, чтобы выделить / выделить большие области данных за секунды.

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

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

12 хитростей Excel, которые нужно знать

Несмотря на широкую доступность инструментов для работы с электронными таблицами, наборов для анализа данных и других мощных программ, которые делают многое из того, что делает Microsoft Excel (и, возможно, лучше), мы по-прежнему довольно часто возвращаемся к самому Excel. Одна из причин, по которой мы это делаем, — огромное количество встроенных инструментов и функций, которые значительно упрощают нашу работу. Конечно, есть время и место для специализированного программного обеспечения, но скромный Excel по-прежнему отлично подходит для составления бюджета, постановки общих целей, анализа данных высокого уровня и многого другого.Вот некоторые из наших любимых приемов Excel, которые, на наш взгляд, могут вам пригодиться.

Найдите то, что ищете

Удалить дубликаты

Excel упрощает очистку и систематизацию данных. Функция удаления дубликатов проста в использовании и, как следует из названия, удаляет повторяющиеся значения из набора данных. Чтобы использовать «Удалить дубликаты», просто выберите данные, которые нужно очистить, и перейдите в « Данные»> «Удалить дубликаты» .

Поиск с использованием подстановочных знаков Excel

Вы, наверное, знакомы с запуском функции поиска с помощью CTRL + F или ⌘ + F .Функциональность поиска можно расширить с помощью подстановочных знаков. Это полезно при поиске неточных совпадений, отвечающих определенным критериям.

Вы можете использовать две подстановочные карты:

• ? обозначает один символ
• * обозначает один или несколько символов

Если результат, который вы ищете, включает в себя ? или * , используйте оператор тильды ~ впереди (например, ~? или ~ * ).

Использование индекса и соответствия для поиска в электронной таблице

Функция Index возвращает значение, основанное на пересечении строки и столбца.

В следующем примере показана функция индекса, которая включает:

• Массив, в котором будет выполняться поиск. [A1: B2 представляет собой раздел таблицы, который мы хотим запросить, а именно область между ячейкой A1 (вверху слева) и ячейкой B2 (внизу справа). Эта область охватывает первые 2 строки и первые 2 столбца в таблице.]
• Значения, которые мы хотим вернуть.В этом примере мы запрашиваем значения в первом столбце, первой строке (1,1) в массиве.

Функция Match ищет значение в массиве и возвращает его относительное положение. Например, предположим, что нам нужно относительное положение «синего» в массиве, состоящем из ячеек A1 и A2:

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

= ИНДЕКС (ваши значения, ПОИСКПОЗ (значение для поиска, столбец поиска, идентификатор сортировки))

Правильный

Правильная функция полезна, если ваш набор данных содержит текстовые значения странного формата.Например, предположим, что в ячейке A1 написано: «Я люблю сИНГ». Используя PROPER (A1) , вы увидите I Love To Sing.

Блокировка ссылок с использованием знака доллара

Как вы знаете, Excel представляет ячейки с помощью пар строка / столбец, например A1 . При ссылке на конкретную ячейку вы можете заблокировать значение столбца или строки, чтобы ссылка не изменилась неожиданно.

• Если вы хотите заблокировать ссылку на строку, введите A $ 1 .
• Если вы хотите заблокировать ссылку на столбец, введите $ A1 .
• Если вы хотите заблокировать ссылки на столбцы строки и , введите $ A $ 1 .

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

Конкатенат

Используя Concatenate, вы можете объединить значения нескольких ячеек в одну ячейку:

= СЦЕПИТЬ (F3, G3, h4, I3, J3)

Вот результат:

В качестве альтернативы вы можете объединить, используя только амперсанды: = F3 & G3 & h4 & I3 & J3 .

Если операторы

Операторы «If» позволяют вывести выбранное вами значение как допустимое (или, наоборот, ложное). Например, предположим, вы ввели следующее:

= ЕСЛИ (B2> C2, «прошел», «не прошел»)

Это похоже на высказывание: «Если значение ячейки B2 больше, чем значение ячейки C3, заполните эту ячейку с помощью« Пропустить ». Если нет, заполните эту ячейку «Неудачно».

Вы можете использовать простые операторы «If», используя SUMIF , COUNTIF и AVERAGEIF .Например, если у вас есть набор результатов опроса, вы можете использовать COUNTIF для подсчета количества строк, в которых респондент — женщина, используя:

= СЧЁТЕСЛИ (B17: B19, «Женский»)

Условное форматирование

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

Наведите указатель мыши на Набор значков и щелкните Дополнительные правила .

Создайте правила, определяющие, какой значок и когда используется.

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

Трехмерная сумма

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

Допустим, у вас есть таблица бюджета, в которой можно отслеживать еженедельные расходы.Для каждой недели у вас есть отдельная вкладка в электронной таблице (вкладка для первой недели — , неделя 1 , вкладка для второй недели — , неделя 2, и т. Д.). Используя трехмерную сумму, вы можете сложить свои общие расходы — например, вы можете узнать общую сумму, которую вы потратили на бензин в течение нескольких недель. Для этого введите:

= СУММ ('Неделя 1: Неделя 7'! B10)

Цель

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

• Учитывая мои продажи с января по март, что мне нужно продать в апреле, чтобы достичь определенного порога?
• Если я копил и получал проценты на своем счете в течение последних десяти лет, сколько еще мне нужно откладывать, чтобы иметь 2 миллиона долларов в банке, когда я выйду на пенсию в 67 лет?

После того, как вы заполните данные в Excel, вы можете запустить функцию «Поиск цели», выбрав «Инструменты »> «Поиск цели» .

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

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

Хотя этот пример был простым (и несколько тривиальным), поиск цели часто используется консультантами, специалистами по продажам и всеми, кто заинтересован в оптимизации неизвестного значения.

Сводные таблицы

Если у вас большой объем данных и вы хотите получить хорошее представление о них «в целом», вы можете использовать сводные таблицы.

Чтобы создать сводную таблицу, выберите Данные> Суммировать со сводными таблицами .Вам будет предложено выбрать данные, которые вы хотите проанализировать, и место, где вы хотите разместить сводную таблицу.

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

Спарклайны

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

Вы можете создать свою собственную диаграмму спарклайнов, выбрав данные, которые вы хотите отобразить, и затем перейдя на Вставка> Спарклайны> [Тип спарклайна] .

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

Excel Tricks Takeaways

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

10 самых полезных советов и приемов Excel

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

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

Это 10 лучших советов по Excel, согласно нашему рейтингу.

Навыки ранжируются по степени полезности и получают оценку из 100. У каждого навыка также есть рейтинг сложности (из 5) в зависимости от сложности и сложности функции. Наконец, мы приводим среднее время в минутах, которое требуется среднему учащемуся, чтобы перейти от незнания к уровню владения языком.

Видео и гифки, добавленные в этот список, были созданы Business Insider.

1.Условное форматирование

Утилита: 100
Сложность: 3
Обучение за 180 минут

Разобраться в нашем шумном и богатом данными мире сложно, но важно. При правильном использовании условное форматирование позволяет выявить закономерности вселенной, зафиксированные в вашей электронной таблице. Вот почему как эксперты Excel, так и пользователи Excel считают, что это самая важная функция №1. Это может быть сложно. Но даже простейшая смена цвета может принести огромную пользу. Предположим, у вас есть объемы, продаваемые торговым персоналом каждый месяц.Всего за три клика можно выявить 10% самых эффективных продавцов и начать важный деловой разговор.

2. Сводные таблицы

Утилита: 95
Сложность: 3
Обучение за 240 минут

За 4 часа, чтобы перейти к профессиональному уровню, вы можете отложить изучение сводных таблиц, но не быть. Используйте их для сортировки, подсчета, суммирования или усреднения данных, хранящихся в одной большой электронной таблице, и отображения их в новой таблице, вырезая как хотите.Это ключевой момент. Если вы хотите смотреть только на показатели продаж для определенных стран, продуктовых линеек или каналов сбыта, это тривиально. Предупреждение: сначала убедитесь, что ваши данные чистые!

3. Специальная паста

Утилита: 88
Сложность: 3
Обучение за 10 минут

Захват (т. Е. Копирование) некоторых данных из одной ячейки и вставка их в другую ячейку — одно из наиболее распространенных действий в Excel.Но вы можете многое скопировать (форматирование, значение, формула, комментарии и т. Д.), И иногда вам не захочется копировать все это. Наиболее распространенный пример этого — когда вы хотите потерять форматирование — эти данные размещаются в вашей собственной электронной таблице с вашим собственным стилем.

4. Добавить несколько строк

Утилита: 87
Сложность: 0
Учиться за 10 минут

Вероятно, одно из наиболее часто выполняемых действий в электронных таблицах.Ctrl Shift + — это ярлык, но на самом деле он занимает больше времени, поэтому мы рекомендуем щелкнуть правой кнопкой мыши. И если вы хотите добавить более одного, выберите столько строк или столбцов, сколько хотите добавить, а затем щелкните правой кнопкой мыши и добавьте.

5. Абсолютные ссылки

Утилита: 85
Сложность: 2
Обучение за 15 минут

Незаменим! Доллар перед буквой фиксирует столбец, знак доллара перед числом фиксирует, строка F4 переключает четыре возможных комбинации.

Business Insider / Сара Силверштейн

6. Оптимизация печати

Утилита: 84
Сложность: 3
Обучение за 120 минут

У всех есть проблемы с печатью из Excel.Но только представьте, если бы то, что вы печатали, всегда было именно тем, что вы собирались напечатать. Это действительно возможно. Однако есть несколько компонентов: предварительный просмотр печати, размещение на одной странице, настройка полей, выбор печати, печать заголовков, портретная или альбомная ориентация и дизайн электронной таблицы. Потратьте время, чтобы освоиться с этим. Вы будете выполнять эту задачу много-много раз в своей трудовой жизни.

7. Расширить формулу поперек / вниз

Утилита: 84
Сложность: 1
Выучить за 5 минут

Прелесть Excel в его легкой масштабируемости.Получите формулу один раз, и Excel произведет правильный расчет миллион раз. + Перекрестие удобно. Двойной щелчок по нему полностью опустит, если у вас есть непрерывные данные. Иногда копирование и вставка (обычная вставка или формулы вставки) будет для вас быстрее.

Business Insider / Сара Сильверстайн

8.Flash Fill

Утилита: 84
Сложность: 2
Обучение за 30 минут

В 2013 году Excel разработал собственный ум. Допустим, у вас есть две колонки с именами, и вам нужно построить из них адреса электронной почты. все. Просто сделайте это для первой строки, и Excel поймет, что вы имеете в виду, и сделает то же самое для всего остального. До 2013 года это было возможно, но полагалось на комбинацию функций (НАЙТИ, ВЛЕВО, & и т. Д.). Это намного быстрее и БУДЕТ впечатлять людей.

9.ИНДЕКС-МАТЧ

Утилита: 82
Сложность: 4
Обучение за 45 минут

Это одна из самых мощных комбинаций функций Excel. Вы можете использовать его для поиска значения в большой таблице данных и возврата соответствующего значения в этой таблице. Предположим, в вашей компании 10 000 сотрудников, и есть таблица, в которой все они есть с большим количеством информации о них, такой как зарплата, дата начала работы, линейный руководитель и т. Д.Но у вас есть команда из 20 человек, и вас действительно интересуют только они. INDEX-MATCH найдет значение членов вашей команды (они должны быть уникальными, например, адрес электронной почты или номер сотрудника) в этой таблице и вернет желаемую информацию для вашей команды. Об этом стоит подумать, так как он более гибкий и, следовательно, более мощный, чем ВПР.

10. Фильтры

Утилита: 81
Сложность: 2
Обучение за 60 минут

Быстрое изучение данных в таблице.Фильтрация эффективно скрывает данные, которые не представляют интереса. Обычно есть значение, например «Синие машины», которые вы ищете, и фильтры откроют их, а остальные скроют. Но в более современных версиях Excel теперь вы также можете фильтровать числовые значения (например, больше, 10% и т. Д.) И цвет ячеек. Фильтрация становится более мощной, когда вам нужно отфильтровать несколько столбцов в комбинации, например. как цвета, так и автомобили, чтобы найти свою синюю машину.

5 функций Excel, которыми я с радостью воспользуюсь в 2020 году — TheSpreadsheetGuru

В конце последнего десятилетия Microsoft, казалось, пошла ва-банк на то, что они называют своими «мощными» приложениями.Это в основном облачные приложения, которые могут легко вводить данные из внешних источников (будь то базы данных или веб-сайты) и выполнять автоматизированные процессы, чтобы дать вам чистый конечный результат. Одним из примеров может быть получение результатов опроса, объединение их с данными о продажах и их компиляция в ежемесячную информационную панель, которую ваши вице-президенты по продажам просматривают на мобильном устройстве. Эти Power Apps предназначены для того, чтобы получать все виды данных из приложений, а также автоматизировать очистку и объединение этих данных.Очевидно, что это навык, который компании будут искать в ближайшем будущем, и есть два конкретных Power Apps, которые, я думаю, пользователи Excel (включая меня) могут сразу же приступить к обучению, приложив немного усилий. Power Query и Power BI — это две области, на которых я собираюсь сосредоточиться в 2020 году, и я вижу большой спрос на людей с такими навыками на рынке в настоящее время. Если вы не слышали об этих приложениях, я дам вам общий обзор того, что делает каждое приложение.

Что такое Power Query?

Power Query — это инструмент автоматизации очистки данных, который недавно был встроен непосредственно в Excel в группе Get & Transform Data на вкладке Data . По сути, вы можете подключиться к источникам данных или таблицам в файлах Excel и создать правила очистки, которые можно выполнить одним нажатием кнопки. Вы можете закодировать эти правила, используя меню / формы для их создания и упорядочить их по шагам, которые вы хотите выполнить.Примерами могут быть разделение имени / фамилии на отдельные столбцы, выполнение вычислений, удаление пустых строк и многое другое. После того, как вы создадите все свои правила, их можно будет сохранить и повторить по запросу в любое время.

Что такое Power BI?

Power BI — это специальное приложение для построения панелей мониторинга. Он позволяет легко подключаться к сторонним источникам данных, таким как Salesforce, Google Analytics и социальные сети, для получения информации в режиме реального времени.