Которые позволяют оптимизировать работу в MS Excel. А сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе. О них расскажет Николай Павлов - автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓

Быстрое добавление новых данных в диаграмму

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

Копирование без нарушения форматов

Вы, скорее всего, знаете про «волшебный» маркер автозаполнения - тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег - специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта , нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

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

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

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт - перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert - What If Analysis - Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Если этот подробный обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

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

• таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
• таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
• в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) - значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

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

Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:


Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля "Дерево" Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле "Возраст" больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6 , 0 (не равно нулю), >=7 , "Яблоня" .

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных - ссылка на ячейки данных таблицы, включая заголовок(A6:E12).
поле - в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках ("Дерево", "Возраст" или "Урожай"), так и число, задающее положение столбца в таблице: 1 - для первого поля(столбца) в таблице "Дерево", 2 - для второго поля "Высота", 3 - для третьего поля "Возраст" и так далее.
критерий - ссылка на диапазон ячеек с условиями отбора(A1:F3). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

• ДСРЗНАЧ (DAVERAGE) - Вычисляет среднее значение выбранных записей базы данных:
  =ДСРЗНАЧ(A6:E12 ;5; A1:F3)
  =ДСРЗНАЧ(A6:E12 ;"Прибыль"; A1:F3)
  =DAVERAGE(A6:E12 ,5, A1:F3) вернет значение 90 000р. , т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000 .
• БСЧЁТ (DCOUNT) - Подсчитывает количество числовых ячеек в базе данных:
  =БСЧЁТ(A6:E12 ;5; A1:F3)
  =БСЧЁТ(A6:E12 ;"Прибыль"; A1:F3)
  =DCOUNT(A6:E12 ,5, A1:F3) вернет число 2 , т.к. только две строки в таблице отвечают критериям
• БСЧЁТА (DCOUNTA) - Подсчитывает количество непустых ячеек в базе данных:
  =БСЧЁТА(A6:E12 ;4; A1:F3)
  =БСЧЁТА(A6:E12 ;"Прибыль"; A1:F3)
  =DCOUNTA(A6:E12 ,4, A1:F3) вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце "Прибыль"
• БИЗВЛЕЧЬ (DGET) - Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
  =БИЗВЛЕЧЬ(A6:E12 ;5; A1:F3)
  =БИЗВЛЕЧЬ(A6:E12 ;"Прибыль"; A1:F3)
  =DGET(A6:E12 ,5, A1:F3) для заданных условий вернет значение ошибки #ЧИСЛО! (#NUM!) , т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
  =БИЗВЛЕЧЬ(A6:E12 ;5; A1:F2) то функция вернет значение 75 000р. , т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 - Яблона, высота 5)
• ДМАКС (DMAX) - Находит максимальное значение среди выделенных записей базы данных:
  =ДМАКС(A6:E12 ;5; A1:F3)
  =ДМАКС(A6:E12 ;"Прибыль"; A1:F3)
  =DMAX(A6:E12 ,5, A1:F3) вернет сумму 105 000р. , т.к. это максимальная прибыль из всех отвечающих критериям строк.
• ДМИН (DMIN) - Находит минимальное значение среди выделенных записей базы данных:
  =ДМИН(A6:E12 ;5; A1:F3)
  =ДМИН(A6:E12 ;"Прибыль"; A1:F3)
  =DMIN(A6:E12 ,5, A1:F3) вернет сумму 75 000р. , т.к. это минимальная прибыль из всех строк, отвечающих критериям
• БДПРОИЗВЕД (DPRODUCT) - Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
  =БДПРОИЗВЕД(A6:E12 ;3; A1:F3)
  =БДПРОИЗВЕД(A6:E12 ;"Возраст"; A1:F3)
  =DPRODUCT(A6:E12 ,3, A1:F3) вернет 210 , т.к. будут перемножены все значения столбца "Возраст", отвечающие критериям(14*15=210)
• ДСТАНДОТКЛ (DSTDEV) - Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
  =ДСТАНДОТКЛ(A6:E12 ;4; A1:F3)
  =ДСТАНДОТКЛ(A6:E12 ;"Урожайность"; A1:F3)
  =DSTDEV(A6:E12 ,4, A1:F3) вернет 0,707107 , т.е. оценку стандартного отклонения урожайности по указанным критериям.
• ДСТАНДОТКЛП (DSTDEVP) - Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
  =ДСТАНДОТКЛП(A6:E12 ;4; A1:F3)
  =ДСТАНДОТКЛП(A6:E12 ;"Урожайность"; A1:F3)
  =DSTDEVP(A6:E12 ,4, A1:F3) вернет 0,5 , т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.
• БДСУММ (DSUM) - Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
  =БДСУММ(A6:E12 ;5; A1:F3)
  =БДСУММ(A6:E12 ;"Прибыль"; A1:F3)
  =DSUM(A6:E12 ,5, A1:F3) вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
  =БДСУММ(A6:E12 ;5; A1:A2)
  =DSUM(A6:E12 ,5, A1:A2) вернет сумму прибыли от всех Яблонь, т.е. 225 000р.
• БДДИСП (DVAR) - Оценивает дисперсию по выборке из выделенных записей базы данных:
  =БДДИСП(A6:E12 ;4; A1:A2)
  =БДДИСП(A6:E12 ;"Урожайность"; A1:A2)
  =DVAR(A6:E12 ,4, A1:A2) вернет 0,5 , что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду
• БДДИСПП (DVARP) - Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
  =БДДИСПП(A6:E12 ;4; A1:A2)
  =БДДИСПП(A6:E12 ;"Урожайность"; A1:A2)
  =DVARP(A6:E12 ,4, A1:A2) вернет 10,66667 , т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду

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

Варианты записи функции ПРОСМОТР

Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:

Форма массива

Форма массива очень похожа на функции ВПР и ГПР . Основная разница в том, что ГПР ищет значение в первой строке диапазона, ВПР в первом столбце, а функция ПРОСМОТР либо в первом столбце, либо в первой строке, в зависимости от размерности массива. Есть и другие отличия, но они менее существенны.

Данную форму записи мы подробно разбирать не будем, поскольку она давно устарела и оставлена в Excel только для совместимости с ранними версиями программы. Вместо нее рекомендуется использовать функции ВПР или ГПР .

Векторная форма

Функция ПРОСМОТР (в векторной форме) просматривает диапазон, который состоит из одной строки или одного столбца. Находит в нем заданное значение и возвращает результат из соответствующей ячейки второго диапазона, который также состоит из одной строки или столбца.

Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.

Пример 1

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

В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР . Формула будет выглядеть следующим образом:

Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор. Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter , убеждаемся, что все верно.

Пример 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter , мы получим требуемый результат:

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

Так вот коротко и на примерах мы познакомились с функцией ПРОСМОТР и научились использовать ее в рабочих книгах Excel. Надеюсь, что данная информация оказалась для Вас полезной, и Вы обязательно найдете ей применение. Всего Вам доброго и успехов в изучении Excel.

Сегодня мы рассмотрим функцию ЕСЛИ .

Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах.

Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.

Синтаксис функции ЕСЛИ очень простой:

ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])

лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.

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

Например:

ЕСЛИ(А1=10 ; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ

Другой пример

ЕСЛИ(А1>30 ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ

Еще пример

ЕСЛИ(С1=”Да” ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ

ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])

значение_если_истина , значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения : ИСТИНА и ЛОЖЬ

Пример использования функции ЕСЛИ в Excel

Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)

Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ

ЕСЛИ(A3="Ведро";D3;"-")

Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.

Итак, первый аргумент (лог выражения ) A3="Ведро" проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина ), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь ), в нашем случае это «-» (т.е будет написано тире).

Таким образом, в ячейки E3 появится значение D3, т.е число 240.

Функция Excel - это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат.

Наиболее распространенные функции Excel являются краткой записью часто используемых формул.

Например функция =СУММ(А1:А4) аналогична записи =А1+А2+А3+А4.

А некоторые функции выполняют очень сложные вычисления.

Каждая функция состоит из имени и аргумента .

В предыдущем случае СУММ - это имя функции , а А1:А4 - аргумент . Аргумент заключается в круглые скобки.

Т.к. функция суммы используется наиболее часто, то на панель инструментов "Стандартная" вынесена кнопка "Автосумма".

Введите в ячейки А1, А2, А3 произвольные числа. Активизируйте ячейку А4 и нажмите кнопку автосуммы. Результат показан ниже.

Нажмите клавишу ввода. В ячейку А4 будет вставлена формула суммы ячеек А1..А3. Кнопка автосуммы снабжена выпадающим списком, из которого можно выбрать другую формулу для ячейки.

Для выбора функции служит кнопка "Вставка функции" в строке формул. При ее нажатии появляется следующее окно.

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

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

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

По умолчанию Microsoft Excel не распознает заголовки в формулах. Чтобы использовать заголовки в формулах, выберите команду Параметры в меню Сервис. На вкладке Вычисления в группе Параметры книги установите флажок Допускать названия диапазонов.

При обычной записи формула в ячейке В6 выглядела бы так: =СУММ(В2:В4).

При использовании заголовков формула будет выглядеть так: =СУММ(Кв 1).

Необходимо знать следующее:

• Если формула содержит заголовок столбца/строки, в котором она находится, то Excel считает, что вы хотите использовать диапазон ячеек, расположенных ниже заголовка столбца таблицы (или справа от заголовка строки);
• Если формула содержит заголовок столбца/строки, отличного от того, в котором она находится, Excel считает, что вы хотите использовать ячейку на пересечении столбца/строки с таким заголовком и строки/столбца, где расположена формула.
• При использовании заголовков можно указать любую ячейку таблицы с помощью пересечения диапазонов. Например, для ссылки на ячейку С3 в нашем примере можно использовать формулу =Строка2 Кв 2 . Обратите внимание на пробел между заголовками строки и столбца.
• Формулы, содержащие заголовки, можно копировать и вставлять, при этом Excel автоматически настраивает их на нужные столбцы и строки. Если будет произведена попытка скопировать формулу в неподходящее место, то Excel сообщит об этом, а в ячейке выведет значение ИМЯ? . При смене названий заголовков, аналогичные изменения происходят и в формулах.