Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали

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

1. Сумма альтернативных столбцов/строк

Все что нам нужно сделать, это добавить дополнительный столбец и заполнить его единицами и нулями (введите 1 и 0 в две строки, выделите обе ячейки и перетащите до конца таблицы). Теперь мы можем использовать наш столбец для определения условия с помощью функции =СУММЕСЛИ(диапазон условия; 1; диапазон суммирования).

2. Подсчет количества повторений в листе А элементов из листа Б

Часто, когда вы работаете с несколькими листами, необходимо подсчитать количество повторений какого-либо элемента на другом листе. Это можно реализовать с помощью старой доброй СЧЕТЕСЛИ, которая считает количество ячеек, удовлетворяющих заданному условию.

В примере выше я использовал функцию СЧЁТЕСЛИ, чтобы посчитать, сколько клиентов находиться в определенном городе (где список с клиентами находится на Листе 2, список с городами на Листе 1). Формула выглядит следующим образом =СЧЁТЕСЛИ(диапазон условий; условие). К примеру, формула СЧЁТЕСЛИ($ E $6:$ E $16; “Волгоград”) скажет мне, сколько клиентов находиться в Волгограде.

3. Быстрый подсчет данных с помощью СЧЁТЕСЛИ и СУММЕСЛИ

Теперь, когда мы выяснили, как пользоваться формулами СЧЁТЕСЛИ и СУММЕСЛИ, может использовать их вместе.

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

4. Поиск второго, третьего … n -ного вхождения элемента в списке EXCEL

Часто случается так, что мы работаем со списком данных, имеющих много дубликатов, например, в список телефонов клиента со временем могут добавляться новые данные. Получить второе, третье … десятое вхождение может быть не простым. С помощью комбинаций СЧЁТЕСЛИ и ВПР мы можем найти любое появление в списке.

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

Далее, при поиске информации о Кристине Агилере, мы будем пользоваться измененными данными клиентов. К примеру, формула ВПР(“Кристина Агилера3”, диапазон поиска, 2, ЛОЖЬ) выдаст нам третий номер телефона Кристины Агилеры. Обратите внимание, что последний аргумент формулы ВПР будет ЛОЖЬ, так как наш список не отсортирован по алфавиту и нам требуется точное совпадение значений поиска.

5. Сокращаем количество вложений функции ЕСЛИ

Как я перевел значение буквы в число в функции ВЫБОР(), ну пускай это будет вашим домашним заданием)

Вам также могут быть интересны следующие статьи

24 комментария

К примеру 5:
Функция КОДСИМВ() возвращает значения от букв A, B, C, D (англ.) — 65, 66, 67, 68 соответственно вычитая из этих значений 64, получаем 1, 2, 3, 4 для первого аргумента функции ВЫБОР().

В 4-м пункте (дабы было удобнее пользоваться этим приемом) лучше в ячейках B6:B9 сделать пользовательский формат 0″-й», а в формуле ссылаться на соседнюю ячейку, то есть так (формула для ячейки C6):
=ВПР($C$5&B6;$F$6:$G$24;2;ЛОЖЬ)

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

а каким образом найти клиента в огромной базе по номеру телефона? Т.е. в таблице много параметров о клиентах и контактные данные в том числе (это как бы уникальный номер, только по нему можно найти нужного клиента). Мне это нужно, чтобы повторно не вносить одного и того же клиента, а такой риск у меня 95 к 100.

Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС.

Основы Excel для SEO

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

Функции Excel — простые примеры для начинающего

Сразу хотелось бы отметить, что все примеры будем рассматривать в Microsoft office 2010.

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

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

Функция Excel – Сцепить

Функция Excel – ВПР

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

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

С описанием полей проблем не должно возникнуть, там все написано. Далее жмете «ОК» и получаете результат:

Функции Excel – Правсимв и Левсимв

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

Функция Excel – Если

Это обычная функция на проверку выражения или значения. Иногда бывает полезна. Например, нам необходимо в столбец C записывать значение «Больше» или «Меньше» на основании сравнения полей A и B т.е. например, если A больше B то записываем «Больше» если меньше то соответственно записываем «Меньше»:

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

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

Выполнение выборки

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