Формулы в excel для чайников

Кому важно знать Excel и где выучить основы

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

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

Аналитик данных: новая работа через 5 месяцев
Получится, даже если у вас нет опыта в IT

Узнать больше

На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python. Программа подойдет даже тем, у кого совсем нет опыта в анализе и кто не любит математику. Вас ждут живые вебинары, мастер-классы, домашки с разбором, помощь наставников.

Урок из курса «Аналитик данных» в Skypro

Как скопировать выпадающий список в Excel

В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6.

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

• нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
• нажмите сочетание клавиш на клавиатуре CTRL+C;
• выделите ячейки в диапазоне А2:А6, в которые вы хотите вставить выпадающий список;
• нажмите сочетание клавиш на клавиатуре CTRL+V.

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

• нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;
• нажмите сочетание клавиш на клавиатуре CTRL+C;
• выберите ячейку, в которую вы хотите вставить выпадающий список;
• нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка“;

В появившемся окне в разделе “Вставить” выберите пункт “условия на значения“:

Нажмите “ОК“

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

Как вставить формулу в ячейку

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

Поставить знак «=».

Ввести значения: числа и алгебраические знаки (в этом случае после нажатия клавиши «Ввод» в выбранном поле появится результат алгебраического действия) или значения из других полей, тогда необходимо выделить эти поля и поставить необходимый алгебраический знак между ними. Например, в выделенном поле нам необходимо получить сумму стоимостей единицы Товара 5 и Товара 8. В таком случае после постановки знака «=» необходимо выделить поле С6, потом поставить «+», а затем выделить С9.

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

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

• + — сложение;
• — — вычитание;
• * — умножение;
• / — деление;
• ^ — степень.

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4останавливаетесь на нужном варианте.

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

Абсолютная ссылка в Excel

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

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

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка в Excel

Например, если у вас есть значение 10 в ячейке A1, и вы используете абсолютную ссылку на ячейку ($A$1), формула = $A$1+5 всегда будет возвращать число 15, независимо от того, в какие ячейки копируется формула. С другой стороны, если вы пишете ту же формулу с относительной ссылкой на ячейку (A1), а затем скопируете ее в другие ячейки в столбце, для каждой строки будет вычисляться другое значение. Следующее изображение демонстрирует разницу абсолютных и относительных ссылок в MS Excel:

Абсолютные и относительные ссылки в Excel – Разница между абсолютными и относительными ссылками в Excel

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

Пример использования абсолютной и относительных ссылок в Excel

Пусть в рассматриваемой выше электронной таблице необходимо дополнительно рассчитать десятипроцентную скидку. В ячейке Е2 вводим формулу =D2*(1-$H$1). Ссылка на ячейку $H$1 является абсолютной ссылкой на ячейку, и она не будет изменяться при заполнении других ячеек.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка

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

Абсолютные и относительные ссылки в Excel – Переключение между относительной, абсолютной ссылкой и смешанными ссылками

Или вы можете сделать абсолютную ссылку, введя символ $ вручную с клавиатуры.

Если вы используете Excel for Mac, то для преобразования относительной в абсолютную ссылку или в смешанные ссылки используйте сочетание клавиш COMMAND+T.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка (режим формул)

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

Примечание о сочетаниях клавиш Excel по умолчанию

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

Таким образом, существует три основных типа сочетаний клавиш:

• Стандартные сокращения, например Ctrl + I Для курсива, который нельзя изменить в Excel.
• Ярлыки другой , где вы нажимаете клавишу другой Активирует ярлыки для элементов ленты. Например, нажатие. Будет Alt -> N -> T Для выбора вкладки Вставлять в баре, а затем вариант جادول.
• Ярлыки макросов, которые полностью настраиваются. Об этом мы поговорим ниже.

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

Раздел 1: Основы Excel

1. Знакомство с Excel
   • Интерфейс Microsoft Excel
   • Лента в Microsoft Excel
   • Представление Backstage в Excel
   • Панель быстрого доступа и режимы просмотра книги
2. Создание и открытие рабочих книг
   • Создание и открытие рабочих книг Excel
   • Режим совместимости в Excel
3. Сохранение книг и общий доступ
   • Сохранение и автовосстановление книг в Excel
   • Экспорт книг Excel
   • Общий доступ к книгам Excel
4. Основы работы с ячейками
   • Ячейка в Excel — базовые понятия
   • Содержимое ячеек в Excel
   • Копирование, перемещение и удаление ячеек в Excel
   • Автозаполнение ячеек в Excel
   • Поиск и замена в Excel
5. Изменение столбцов, строк и ячеек
   • Изменение ширины столбцов и высоты строк в Excel
   • Вставка и удаление строк и столбцов в Excel
   • Перемещение и скрытие строк и столбцов в Excel
   • Перенос текста и объединение ячеек в Excel
6. Форматирование ячеек
   • Настройка шрифта в Excel
   • Выравнивание текста в ячейках Excel
   • Границы, заливка и стили ячеек в Excel
   • Числовое форматирование в Excel
7. Основные сведения о листе Excel
   • Переименование, вставка и удаление листа в Excel
   • Копирование, перемещение и изменение цвета листа в Excel
   • Группировка листов в Excel
8. Разметка страницы
   • Форматирование полей и ориентация страницы в Excel
   • Вставка разрывов страниц, печать заголовков и колонтитулов в Excel
9. Печать книг
   • Панель Печать в Microsoft Excel
   • Задаем область печати в Excel
   • Настройка полей и масштаба при печати в Excel

Создание таблиц, виды и особенности содержащихся в них данных

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

Столбцы обозначены буквами латинского алфавита (A, B, C,… AA, AB, AC…), строки пронумерованы. Таким образом, каждая ячейка имеет свой уникальный адрес. Вот пример выделения ячейки C7.

Ячейка может содержать числовое значение, текст с заданным форматированием, символы, формулы и другие элементы. Числа по умолчанию располагаются в правой части ячейки, текст – в левой. При выделении ячейки с формулой последняя отображается в строке «fx».

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

На заметку! Предварительно необходимо выделить тот участок, который собираетесь отформатировать.

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

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

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

Кроме того, можно применить уже готовые стили, сгруппированные в пунктах «Условное форматирование», «Форматировать как таблицу» и «Стили ячеек».

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

Кроме уже рассмотренных групп «Шрифт», «Выравнивание» и «Стили», вкладка «Главная» содержит несколько менее востребованных, но все же необходимых в ряде случаев элементов. На ней можно обнаружить:

• «Буфер обмена» – иконки копирования, вырезания и вставки информации (они дублируются сочетаниями клавиш «Ctrl+C», «Ctrl+X» и «Ctrl+V» соответственно);

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

Вставка и удаление листов — Excel

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

Чтобы вставить новый лист, выполните одно из следующих действий.

• Чтобы быстро вставить новый лист после существующих, щелкните вкладку Вставить лист в нижней части экрана.

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

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

  Лист, а затем нажмите кнопку ОК.

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

Выберите нужное действие

Одновременная вставка нескольких листов

Изменение количества листов по умолчанию в новой книге

Вставка нового листа, основанного на настраиваемом шаблоне

Переименование листа

Удаление одного или нескольких листов

Одновременная вставка нескольких листов

1. Удерживая нажатой клавишу SHIFT, выберите столько ярлычков листов книги, сколько листов требуется вставить в открытую книгу.

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

2. На вкладке Главная в группе Ячейки щелкните Вставить, а затем выберите Вставить лист.

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

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

Изменение количества листов по умолчанию в новой книге

1. Откройте вкладку Файл.

   Что такое кнопка Microsoft Backstage и где она находится?

   Дополнительные сведения о кнопке Microsoft Backstage см. в статье Что такое представление Microsoft Backstage, и где оно находится?

2. В разделе Excel выберите пункт Параметры.

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

4. Чтобы вернуться к файлу, откройте любую другую вкладку.

Вставка нового листа, основанного на настраиваемом шаблоне

1. При необходимости создайте шаблон листа, на основе которого будет создан новый лист.

   Как создать шаблон листа

   1. Выберите лист, который вы хотите использовать в качестве шаблона.

   2. Откройте вкладку Файл.

   3. В разделе Сведения нажмите кнопку Сохранить как.

   4. В поле Имя файла введите имя шаблона листа.

      • Чтобы создать настраиваемый шаблон листа, введите нужное имя.

      • Чтобы создать стандартный шаблон листа, введите sheet.

        Примечание: Настраиваемые шаблоны автоматически сохраняются в папке Шаблоны. Стандартный шаблон листа (sheet.xltx или sheet.xltm) должен сохраняться в папке XLStart, которая обычно находится по адресу C:\Program Files\Microsoft Office\Office14\XLStart.

   5. Выполните одно из указанных ниже действий.

      • На компьютере под управлением Windows Vista выберите в списке пункт Шаблон Excel или Шаблон Excel с поддержкой макросов.

      • На компьютере под управлением Microsoft Windows XP в окне Сохранить как выберите пункт Шаблон Excel или Шаблон Excel с поддержкой макросов.

   6. Нажмите кнопку Сохранить .

2. Щелкните правой кнопкой мыши ярлычок листа и выберите команду Вставить.

3. Дважды щелкните нужный шаблон листа.

Переименование листа

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

2. Выделите текущее имя и введите новое.

   Совет: При печати листа можно вывести его имя.

   Как печатать имена листов

   1. На вкладке Вставка в группе Текст нажмите кнопку Колонтитулы.

   2. В режиме разметки щелкните в том месте, где должно отображаться имя листа.

   3. В группе Работа с колонтитулами нажмите кнопку Имя листа

Удаление одного или нескольких листов

1. Выделите лист или листы, которые требуется удалить.

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

   

   

   

   

   

   

   

   

   

   

2. На вкладке Главная в группе Ячейки щелкните стрелку справа от кнопки Удалить и выберите пункт Удалить лист.

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

Как работать в программе Excel: пособие для начинающих

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

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

Давайте разберёмся, как работать с элементами таблицы – стройками, столбцами и отдельными ячейками.

Как выделить столбец/строку

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

Соответственно, для выделения строки нужно щелкнуть мышкой по цифре, соотносящейся с нужной строкой.

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

• щёлкаем правой кнопкой по первому индексу (буква или цифра), при этом строка/столбец выделится;
• отпускаем кнопку и ставим курсор на второй индекс;
• при нажатой Shift щёлкаем ПКМ на второй цифре/букве – выделенной станет соответствующая прямоугольная область.

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

Изменение границ ячеек

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

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

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

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

Отмена внесенных изменений производится стандартным способом, с помощью кнопки «Отмена» или комбинации CTRL+Z. Желательно такие откаты делать сразу – потом это может и не сработать.

Если требуется отметить форматирование строк, можно воспользоваться вкладкой «Формат», в которой выбрать пункт «Автоподбор высоты строки».

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

Как вставить столбец/строку

Как обычно, вставку строки или столбца можно производить двумя способами, через вызов контекстного меню мышкой или с помощью горячих клавиш. В первом случае кликаем ПКМ на ячейке, которую необходимо сдвинуть, и в появившемся меню выбираем пункт «Добавить ячейки». Откроется стандартное окно, в котором можно задать, что именно вы хотите добавить, и будет указано, где произойдёт расширение (столбцы добавляются слева от текущей ячейки, строки – сверху).

Это же окно можно вызвать комбинацией CTRL+SHIFT+«=».

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

Растягивание функций и обозначение константы

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

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

В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

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

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

• $В$2 – при растяжении либо копировании остаются постоянными столбец и строка.

• B$2 – неизменна строка.

• $B2 – константа касается только столбца.

Имена в формулах Эксель

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

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

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

excel 2007

excel 2010

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

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

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

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

Используйте таблицу для автоматического ввода формул

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

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

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

Когда вы обновляете формулу в таблице, Excel снова обновляет все аналогичные формулы в том же столбце.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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