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

Обновлено: 07.07.2024

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

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

Растягивание ячеек

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

Как это работает? Допустим, вам нужно сделать столбец, в котором будут числа от 1 до 15. Конечно, вы можете потратить время и сами забить цифры в каждую ячейку, но такую работу с большим трудом можно назвать продуктивной. Гораздо лучше – растянуть ячейку.

Умный Excel сделает все самостоятельно. Для этого:

Добавьте начальную цифру. В нашем случае это 1.
Зажмите клавишу Ctrl.
Потяните за правую нижнюю часть ячейки.

Удаление повторов

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

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

Для примера мы удалили дубликаты в онлайн-версии Excel. Но эта возможность также доступна и в обычной версии программы для Windows или macOS Для примера мы удалили дубликаты в онлайн-версии Excel. Но эта возможность также доступна и в обычной версии программы для Windows или macOS

Быстрое изменение регистра букв

Достаточно воспользоваться функцией «СТРОЧН» и выделить диапазон значений. Так вы быстро поменяете регистр.

И наоборот – если буквы во всех словах нужно сделать большими, используйте «ПРОПИСН».

Создание очень секретного листа

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

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

Нажмите клавиши Alt и F11.
Выберите номер листа.
Выберите свойство Visible.
Выберите опцию xlSheetVeryHidden.

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

Блокирование изменений в таблице задним числом

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

Вот что вам нужно сделать:

Кликните на ячейку и выберите раздел «Данные».
Нажмите на кнопку «Проверка данных».
Выберите «Другой» в списке «Тип данных».
Добавьте текст «=А2=СЕГОДНЯ()» в графу «Формула».
Уберите галочку с «Игнорировать пустые ячейки» и кликните «Ок».

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

Создание небольших графиков прямо в ячейках

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

Чтобы создать спарклайн:

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

Импорт курса валют

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

Для импорта курса валют:

Отображение данных из таблицы Excel на карте

Microsoft стремится, чтобы ее продукты работали вместе и дополняли друг друга. Например, начиная с Office 2013 возможен необычный кроссовер программы Excel и карты Bing. Кейсов использования такого дуэта предостаточно!

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

Чтобы добавить интерактивную карту:

Перейдите на вкладку «Вставка» и кликните на «Магазин приложений».
Найдите и установите плагин Bing Maps.
Выберите плагин в списке «Мои приложения» на вкладке «Вставка» и поместите его на рабочий лист.
Определите диапазон и кликните Show Locations на модуле карты.

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

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

Выберите ячейки, которые хотите перенести.
Нажмите «Ctrl + C».
Кликните правой кнопкой мышки по ячейке, в которую хотите транспонировать данные.
Выберите «Транспонировать» в «Параметрах вставки».

Где можно побольше узнать о возможностях Excel?

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

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

Если уж браться за обучение, стоит обращаться к профессионалам. Курс « Excel: от новичка до эксперта » – отличная возможность получить новые навыки и прокачаться как специалист. За время учебы вы узнаете, как работать со сводными таблицами, писать макросы и сложные формулы, красиво представлять полученную информацию с помощью графиков и диаграмм.

Преимущества обучения на курсе

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

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

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

✅ Помощь в трудоустройстве. Мы разошлем ваше резюме по нашей базе корпоративных клиентов, среди которых – ведущие российские и международные компании: Cбербанк, Gett, МТС, Билайн, Мегафон, Ростелеком, ПИК, X5 Retail Group.

Сейчас на обучение действует специальная цена. Достаточно при заказе курса назвать менеджеру промокод ДЗЕН, и ваша скидка составит 50 %!

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

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

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

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

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

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

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

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

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

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

Поиск и использование значения

По горизонтали

Используя функцию ГПР мы можем задать поиск по конкретной строке таблицы, а на выходе получить значение из другой ячейки того же столбца (на одну или несколько строк ниже), что соответствует заданному условию. Причем поиск задается либо на точное значение (используется оператор ЛОЖЬ), либо на приблизительное (с оператором ИСТИНА), что позволяет использовать интервалы. Синтаксис =ГПР(искомое_значение; таблица; номер_строки; интервальный_просмотр)

Применение: Для расчета бонуса конкретному сотруднику можно задать интервалы, начиная от которых действует тот или иной процент от прибыли. Скажем, формула =ГПР(E5;$D$1:$G$2;2;ИСТИНА) будет искать в первой строке таблицы из интервала D1:G2 значение, приблизительно схожее со значением из ячейки E5, а результатом формулы станет вывод ячейки со второй строки соответствующего столбца.

По вертикали

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

То есть для формулы =ВПР(E4;$I$3:$J$6;2;ИСТИНА) будет сравниваться значение ячейки E4 с ячейками столбца I из таблицы интервала I3:J6, а значение будет выдаваться из соседней ячейки столбца J.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формулы в Excel

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

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

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

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

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

Макросы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

Опция СУММ

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

Опция ЕСЛИ

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

Опция СУММЕСЛИ

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

Опция ВПР

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

Опция СУММЕСЛИМН

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

Опция ЕОШИБКА

Эту опцию незаслуженно забывают рядовые пользователи Excel, хотя она может спасти расчеты в критических ситуациях. Если вы работаете с большими базами данных, то возникновение ошибки частое явление. Если есть видимая ошибка, то ЕОШИБКА поможет проигнорировать вычисление и вставить нужный критерий. Опция удобна для логических вычислений, которые осуществляют бухгалтера и экономисты.

Опция ПОИСКПОЗ

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

Опция СЧЁТЕСЛИ

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

Опция СУММПРОИЗВ

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

Опция ОКРУГЛ

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

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

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

С помощью формулы вы можете:

посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

СЧЁТЗ

Англоязычный вариант: =COUNTA(A1:A10)

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

ДЛСТР

Англоязычный вариант: =LEN(A1)

Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

СЖПРОБЕЛЫ

Англоязычный вариант: =TRIM(A1)

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

Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)

B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
A1:B13 = таблица, в которой находится искомое значение.
2 – номер столбца, где находится искомое значение.
ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

Формула: =ЕСЛИ(логическое_выражение; "текст, если логическое выражение истинно; "текст, если логическое выражение ложно")

Англоязычный вариант: =IF(логическое_выражение; "текст, если логическое выражение истинно; "текст, если логическое выражение ложно")

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

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

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; "Норма выполнена"; "Норма не выполнена")

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

СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

Используя функции на предыдущем примере, мы можем узнать:

Формула «СУММЕСЛИ»

СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

Формула «СРЗНАЧЕСЛИ»

СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

Формула «СЧЁТЕСЛИ»

СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

Конкатенация

Формула: =(ячейка1&" "&ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&" "&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между " ", чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;" "; B1) или в англоязычном варианте =concatenate(A1;" "; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

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

Читайте также: