Как пользоваться формулами в экселе: Как Работать с Формулами в Excel: Копирование, Вставка и Автозаполнение

Как пользоваться Microsoft Excel (Эксель)

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

Структура программы Excel

В качестве рабочей области программы выступает своеобразный лист с клетками (ячейками). Также в рабочей области есть дополнительные элементы управления:

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

Редактирование значений в ячейке

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

Для редактирования также доступна строка формул, которая располагается сразу над рабочим полем программы. Чтобы редактировать через строку формул, сначала необходимо выделить ячейку – данные сразу отобразятся в строке. Вводим в нее данные и нажимаем «Enter». Если требуется удалить значение в ячейке, выделяем ее и нажимаем кнопку на клавиатуре «Delete». Для перемещения ячейки выделяем ее, нажимаем на клавиатуре сочетание клавиш «Ctrl+X» и вставляем комбинацией «CTRL+V» в необходимое место. Точно также можно перенести несколько ячеек, предварительно выделив каждую.

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

Создаем таблицу с формулами Excel

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

У нас есть графа «Стоимость», где соответственно должны отображаться числовые значения по результатам цены и количества позиций. Для заполнения графы «Стоимость» нам необходимо ввести формулу Excel, чтобы программа автоматически посчитала результат колонок B и С. В строке «Стоимость» в каждой ячейке вводим знак «=». Программа готова к вводу формулы. Выделяем ячейку «В2» ставим знак умножения «*» и выделяем ячейку «С2» и нажимаем на клавиатуре «Enter». Программа автоматически показало значение «1000» — это стоимость 100 шариковых ручек по цене 10р за каждую – все верно и логично. Идем дальше.

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

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

Наиболее простейшим вариантом создания таблицы будет ее обычная вставка, как и в Word. Жмем комбинацию горячих клавиш «CTRL+T», у нас откроется окно с требованием ввести диапазон данных. Такая таблица достаточно умная, у нее уже есть заголовки, но их можно редактировать, так же как и двигать элемент в любое удобное место.

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

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

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

Microsoft Excel для менеджера по персоналу

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

График тренингов

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

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

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

Теперь выделим все пустые квадратные ячейки, начиная с F5 и до конца таблицы вправо-вниз и выберем на вкладке Главная — Условное форматирование — Создать правило (Home — Conditional formatting — Create Rule).  В открывшемся окне уточним тип создаваемого правила — Использовать формулу для определения форматируемых ячеек и введем следующую формулу:

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

В результате получаем:

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

Расчет бонусов или доплаты за выслугу лет

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

То есть, если сотрудник проработал у нас меньше 12 месяцев — он не получает ничего. Если проработал от года до двух — получает 10% доплаты (или бонуса). Если от двух до трех — 15%. Если от трех до пяти — 25% и т. д.Максимальный бонус в 100% полагается только старожилам — тем, кто работает в компании больше 10 лет.

Можно пойти классическим путем и использовать функцию проверки ЕСЛИ (IF). Причем, нам придется вкладывать одну ЕСЛИ в другую несколько раз, т. к. надо проверить попадание в несколько диапазонов:

Бррр… Ужас, правда? Задачу можно решить гораздо изящнее, если использовать известную в узких кругах финансистов и аналитиков, функцию ВПР (VLOOKUP):

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

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

Лепестковая диаграмма компетенций

Любой HR занимавшийся когда-либо подбором персонала, не понаслышке знает, как сложно порой бывает подобрать правильных людей на вакантные должности. Думаю, все могут припомнить последствия неудачного выбора, когда сотрудники потом или «не тянут» или быстро «перерастают» занимаемую должность и процесс приходится повторять заново, тратя время, ресурсы и деньги компании. Как же наглядно и качественно оценить — насколько данный кандидат подходит на определенную должность?

В такой задаче имеет смысл использовать хоть и не очень распространенный, но весьма удобный в данном случае тип диаграммы в Microsoft Excel — Лепестковая (Radial). В английской терминологии этот тип диаграмм иногда называют еще Spider Chart — за её внешнее сходство с паутиной.

Составим для нашей вакантной должности список из 5–10 ключевых компетенций (навыков, требований). Под 0 в данном случае понимается отсутствие требований, под 10 — максимальная потребность. Например, на должность директора по продажам этот список может выглядеть так:

• Навыки устного и письменного общения — 8
• Навыки проведения презентаций — 7
• Знание/понимание английского — 5
• Знание технологии производства товаров — 2
• Знание финансов и бухгалтерии — 7
• Знание компьютера и ПО — 4

… и т. д.

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

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

Дополнительно, для наглядного отображения набранных баллов в диапазоне B2:D10 я использовал условное форматирование гистограммами (Главная — Условное форматирование — Гистограммы), а в диапазоне C12:D12 — цветовыми шкалами (Главная — Условное форматирование — Цветовые шкалы).

Какие же выводы можно сделать по диаграмме?

Хорошо видно, что Кандидат2 хотя и имеет больший общий суммарный балл по сравнению с Кандидатом1 (61 против 52), но к данной должности подходит меньше, т. к. имеет высокие знания и навыки не там, где нужно (знания технологии или финансов), а по нужным параметрам (навыки ведения переговоров и презентаций) как раз сильно отстает. Кандидат1 напротив, по всем необходимым к данной должности компетенциям укладывается в требования очень неплохо. Если немного «подтянуть» его по презентациям и переговорам, что легко можно сделать отправив его на соответствующие тренинги, то он идеально впишется в эту вакансию.

Для вычисления итогового численного значения «попадания в должность» можно использовать следующую формулу (для ячейки C12):

=СУММ(ЕСЛИ(C2:C10

Обратите внимание на то, что это формула массива, т. е. она должна вводиться с использованием не клавиши Enter в конце, как обычно, а с помощью сочетания клавиш Ctrl+Shift+Enter. Формулы массива отличаются от обычных формул Excel и позволяют работать сразу с целыми массивами данных. В строке формул они отображаются в фигурных скобках (но ставить их с клавиатуры нельзя). Данная формула массива вычисляет отклонение качеств кандидата от требований вакансии и представляет это в виде доли, подразумевая за 100% идеальное совпадение по всем требованиям. Причем перебор навыков, т. е. ситуация, когда кандидат превосходит требования — не учитывается и не дает ему преимуществ.

Извлечение имен из ячеек с помощью формулы Excel — База знаний / Программное обеспечение / Microsoft Office

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

Содержание

• Имя и фамилия
• Фамилия, Имя
• Фамилия, имя с возможным инициалом отчества/именем
• Коричневый адрес электронной почты

Имя и фамилия

В нашем первом примере полное имя имеет формат Имя Фамилия.

Предполагая, что полное имя находится в ячейке A2, вот формулы:

Имя:

=ЛЕВЫЙ(A2, НАЙТИ(" ", A2)-1)

Фамилия:

=ВПРАВО(A2, НАЙТИ(" ", A2)+1)

Как это работает:

Это хорошая демонстрация использования функций ВЛЕВО и ВПРАВО. ЛЕВЫЙ дает вам указанное количество символов (это могут быть буквы, цифры, пробелы и т. д. — все, что находится в ячейке) слева от ячейки (другими словами, начало текста в ячейке), а ПРАВЫЙ делает то же справа (конец текста в ячейке). Это «синтаксис» функций ВЛЕВО и ВПРАВО, или то, как они хотят, чтобы вы давали им необходимую им информацию:

=ЛЕВО(в какую ячейку смотреть, сколько символов нужно)

=ПРАВО(в какую ячейку смотреть, сколько символов нужно)

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

Чтобы найти фамилию, считаем справа от ячейки, а после пробела хотим все. Как и в случае с именем, мы используем НАЙТИ, чтобы узнать, где находится пробел, но на этот раз мы добавляем 1, потому что наша фамилия начинается после пробела.

Фамилия, Имя

Во втором примере наше полное имя имеет формат Фамилия, Имя. Опять же, мы предполагаем, что это в ячейке A2.

Имя:

=ПРАВО(A5, ДЛСТР(A5)-НАЙТИ(", ", A5)-1)

Фамилия: LE (", ", A2)-1)

Как это работает:

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

Фамилия, имя с возможным отчеством/именем

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

Имя:

=если(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2," ",""))+1>2, MID(A2, НАЙТИ(", ", A2)+2, (НАЙТИ(" ", A2 , НАЙТИ(", ", A2)+2)-НАЙТИ(", ", A2))-2), ПРАВО(A2, ДЛСТР(A2)-НАЙТИ(", ", A2)-1))

Фамилия:

=ЛЕВОЕ(A2, НАЙТИ(", ", A2)-1)

Это становится немного сложнее, так что держитесь! Если вы еще этого не сделали, прочтите предыдущие объяснения формул, чтобы понять, как работают функции ВЛЕВО и ВПРАВО.

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

=MID(в какой ячейке искать, с какого символа начинать, сколько символов нужно)

Мы также используем функцию НАЙТИ, которая находит расположение символа в ячейке.

=НАЙТИ(что вы ищете, в какой ячейке искать, [с чего начать поиск]) 

[С чего начать поиск] заключено в скобки, поскольку это необязательно.

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

LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Если это 2, у нас есть только два имени, а если больше двух, у нас есть еще и отчество. На этой странице объясняется, как это работает.

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

=ЕСЛИ(что нужно проверить, что делать, если правда, что делать, если ложь)

В нашем случае мы проверяем, больше ли 2. ..

if(LEN(A2 )-ДЛСТР(ПОДСТАВИТЬ(A2," ",""))+1>2,

и если да, мы делаем это волшебство, чтобы найти первое имя между двумя пробелами во фразе:

MID(A2, НАЙТИ(", ", A2)+2, (НАЙТИ(" ", A2, НАЙТИ( ", ", A2)+2)-НАЙТИ(", ", A2))-2)

и если нет, то используем более простую формулу имени из последнего примера:

ПРАВО(A2, ДЛСТР(A2) -FIND(", ", A2)-1)

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

=MID(смотрите в ячейке A2, начните с двух символов после запятой, возьмите сколько угодно символов между первым пробелом и вторым пробелом)

Как мы начинаем с двух символов после запятой: НАЙТИ(", ", A2)+2 — это два символа после запятой. Нам нужны два символа после, а не один, как в предыдущих примерах, потому что там и запятая, и пробел.

Как узнать, сколько символов находится между первым и вторым пробелами (это длина имени): (НАЙТИ(" ", A2, НАЙТИ(", ", A2)+2)-НАЙТИ(", " , А2))-2.

• Чтобы найти место второго пробела: НАЙТИ(" ", A2, НАЙТИ(", ", A2)+2) Ищем пробел в A2, не начиная с начала, а начиная с двух символов после запятая
• Затем мы вычитаем начало

Фамилия очень похожа на предыдущую формулу фамилии, но она ищет запятую (",") вместо пробела.

Адрес электронной почты Brown

В компании Brown большинство адресов электронной почты имеют формат [email protected]. Эта формула помогает извлечь имена и фамилии из адресов электронной почты в этом формате.

Имя:

=ПРОП(ЛЕВАЯ(A12, НАЙТИ("_", A2)-1))

Фамилия:

(2MIDPRO1, INDAPRO1,

2

2

(«_», A12)+1, НАЙТИ («@», A2)-НАЙТИ («_», A12)-1))

Почему формула Excel дает неверные ответы?

ЗАГРУЗИТЕ НАШУ БЕСПЛАТНУЮ ЭЛЕКТРОННУЮ КНИГУ
101 ЛУЧШИЕ СОВЕТЫ И ПОДСКАЗКИ!

Готов к представить свой отчет но в последнюю минуту формула Excel дает неверный ответ или не обновляет ?

Вы не одиноки!

В этой статье мы рассмотрим 3 причины, по которым ваша формула может работать неправильно, и способы устранения неполадок формулы Excel :

1. Расчет настроен на «Вручную»
2. Формула хранится в виде текста

Загрузите эту книгу Excel , чтобы попрактиковаться вместе с нами:

СКАЧАТЬ EXCEL WORKBOOK

1.

Расчет настроен на «Вручную»

Предположим, у вас есть рабочий лист для расчета общих расходов и доходов .

Скажем, вы обновляете значение в исходных данных. то есть электроэнергия за 2020 год изменилась с 1944 до 2210 долларов. Вы замечаете, что общие расходы содержат старые значения , даже несмотря на то, что исходные данные для ячеек с формулами были изменены .

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

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

Способ 1: Изменить настройку формулы обратно на автоматический

ШАГ 1: Выберите вкладку Файл .

Шаг 2: Нажмите Опции из меню на левой стороне

Шаг 3: в диалоговом окне, нажмите Формулы На левой панели

9002 . : В разделе «Параметры расчета» выберите «Автоматически» в разделе «Расчет рабочей книги».

Или вы можете просто перейти к Вкладка «Формулы» > группа «Расчет» , нажмите кнопку Параметры расчета и выберите Автоматически .

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

Способ 2. Пересчет всего листа

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

Нажмите кнопку Кнопка «Рассчитать сейчас» на вкладке «Формулы» > группа «Расчет».

Вы можете просто нажать F9, чтобы пересчитать весь рабочий лист и Shift + F9, чтобы пересчитать только активный рабочий лист .

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

2. Показать

Кнопка «Формула» включена

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

Чтобы отключить:

Перейдите на вкладку Формула > В группе Аудит формул щелкните Показать формулы .

Или Нажмите Ctrl + `

Это отключит режим отображения формулы и покажет вычисленный результат!

3. Формула сохраняется в виде текста

Даже после отключения режима отображения формулы в ячейке отображается функция, как показано на рисунке ниже:

Это потому, что ваша формула хранится как текст .

Чтобы изменить его, перейдите на вкладку «Главная» > выберите Общие из раскрывающегося списка.