Excel-helper.ru

Содержит основные команды для работы с данными. Именно на этой вкладке Вы будете проводить основную часть работы с Excel.

Группа Буфер обмена

Cодержит команды для копирования данных и форматов.

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

Группа Шрифт

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

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

Группа Выравнивание

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

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

Группа Число

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

Для отображения всех форматов нужно нажать на стрелочку в правом нижнем углу группы.

Группа Стили

Содержит 3 раздела:
Условное форматирование
Форматировать как таблицу
Стили ячеек

Группа Ячейки

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

Группа Редактирование

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

Допустим у нас есть таблица с данными. Нужно посчитать количество пустых ячеек.

Для этого используем функцию СЧИТАТЬПУСТОТЫ.

Нажимаем на кнопочку Вставить функцию (слева от строки формул).
В появившемся окне в поле Категория выбираем Статистические.
В поле Выберете функцию выбираем СЧИТАТЬПУСТОТЫ.
Нажимаем ОК.

В появившемся окне мышкой встаем в поле Диапазон и мышкой выделяем данные в которых нужно посчитать пустые ячейки. Нажимаем ОК.

Функция СЧИТАТЬПУСТОТЫ подсчитывает не только пустые ячейки, но и ячейки выдающие как результат пустую строку.

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

Для примера воспользуемся функцией ЕСЛИ.
Введем данные в поля:

Лог_выражение: В2>0

Значение_если_истина: В2

Значение_если_ложь: “”

Протянем нашу формулу на следующие ячейки. Наша табличка примет вид.

Формулу СЧИТАТЬПУСТОТЫ протянем на следующий столбец. Результат будет тот же.

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

Для подсчета только пустых ячеек используем функции СУММПРОИЗВ и ЕПУСТО.

Для начала вызовем функцию СУММПРОИЗВ. Для этого нажмем на кнопочку Вставить функцию (Fx). В появившемся окне выберем категорию Математические, в поле Выберете функцию, выбрать СУММПРОИЗВ. Нажать ОК.

Можно в строке формул написать =СУММПРОИЗВ(, встать на название функции курсором и нажать Вставить функцию (Fx). Появится окно с аргументами функции.

В поле Массив1 вписываем: –ЕПУСТО(В2:В10)

Функция ЕПУСТО определяет пустая ячейка или нет и возвращает логическое значение ИСТИНА или ЛОЖЬ.

— (два минуса) преобразуют функцию ЕПУСТО в математические значения 1 или 0. Если поставить один минус, формула вернет отрицательное значение. Два минуса дадут положительное значение.

При нажатии на ОК формула выдаст такой же результат, что и функция СЧИТАТЬПУСТОТЫ.

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

Добавим в данные пустую строку. Результат изменится на 1.

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

Диапазон в формуле автоматически изменится.

Функция СЧЕТЗ подсчитывает количество непустых ячеек с любыми данными в любых форматах, в том числе ячейки содержащие формулу с пустой строкой (=””), формулы результаты которых отображают ноль, и формулы содержащие ошибку.

Синтаксис
СЧЕТЗ(значение1;значение2 и т.д.)

Значение1 – обязательный аргумент, содержащий ссылку на ячейку или диапазон ячеек с любыми данными.
Значение2 и т.д. – необязательный аргумент. Всего может быть использовано до 255 аргументов.

Для того, чтобы вставить функцию, нажмите на кнопочку Вставить функцию (слева от строки формул). В появившемся окне выберете категорию Статистические. В поле Выберете функцию, выбрать СЧЕТЗ. Нажать ОК.

В появившемся окне встаньте мышкой в поле Значение1 и выделите нужный диапазон (можно ввести вручную). Нажмите ОК.

В нашем случае Excel не возьмет только 1 пустую ячейку.

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

Синтаксис
СЧЕТ(значение 1;значение2…)

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

Функция СЧЕТ будет учитывать:
В указанном диапазоне – только ячейки содержащие числа, даты и время.
В списке аргументов – текстовые значения чисел, логические выражения.
Никогда – текст, пустые ячейки и значения ошибок.

Функцию СЧЕТ можно найти, вызвав мастер функций.

1. Для этого нажмите на кнопочку Вставить функцию (слева от строки формул).
2. В появившемся окне выбрать категорию Статистические.
3. В поле Выберете функцию, найдите функцию СЧЕТ.
4. Нажмите ОК.

В появившемся окне в поле Значение1 выделите мышкой диапазон (или введите вручную), в котором нужно посчитать количество значений. Нажмите ОК.

В результате отобразится число 3, Excel учтет только первые 3 ячейки.

Для того, чтобы учитывались текстовое представление числа и логические выражения внесем их непосредственно в значения функции (в поля Значение2 и Значение3).
Максимально можно ввести до 255 значений.

Попробуем в аргументы ввести любой текст и ошибку. В результате получится число 3. Excel возьмет из диапазона Значения1 3 первые ячейки, а аргументы Значение2 и Значение3 учитываться не будут.

Допустим нам надо применить прогрессивную скидку в зависимости от суммы заказа.

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

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

Обязательно нужно прописать первую строчку, иначе наша формула с заказами меньше 15000 руб будет выдавать ошибку.

Для построения формулы встанем в нужную ячейку и нажмем на кнопочку Вставить функцию (слева от строки формул). В появившемся окне в поле Категория выбрать Ссылки и массивы. В поле Выберете функцию выбрать ВПР.

Появится окно с аргументами функции ВПР.

Искомое значение: Сумма заказа по которой будет определятся скидка. (В2)

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

1. Таблица должна быть отсортирована по возрастанию.
2. По 1-му столбцу таблицы будет определяться искомое значение.
3. Для того, чтобы протягивать формулы на следующие заказы, диапазон таблицы не должен изменяться, поэтому для этого поля делаем ссылки абсолютными.
   Для этого мышкой выделяем диапазон таблицы (F2:G8) и начинаем нажимать клавишу F4, пока диапазон не изменится на $F$2:$G$8. Если у вас ноутбук, нужно одновременно нажать на клавиши Fn+F4. Знак доллара можно ввести и вручную.

Номер столбца: Столбец из которого необходимо вернуть значения. Отсчет будет вестись, начиная с первого столбца, по которому будет определяться искомое значение.

Интервальный просмотр: Логическое значение по которому будет определяться точно(ЛОЖЬ или 0) или приблизительно (ИСТИНА или 1) должен производится поиск в первом столбце. Если этот аргумент отсутствует, Excel будет определять приблизительные значения.

В нашем случае нужны приблизительные значения, так как первый столбец указывает не точную сумму, а диапазон (от 15 000 до 20 000, от 20 000 до 25 000 и т.д.). Поэтому это поле можно не заполнять или написать ИСТИНА или 1.

Далее протягиваем формулу для следующих заказов.

Мы вывели скидку в отдельную колонку. Также можно функцию ВПР сразу использовать в формуле и выводить уже готовый результат.

Для этого в ячейке пропишем

=B2*(100-ВПР(B2;$G$2:$H$8;2))/100

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

Синтаксис

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

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

Значение_если_истина – значение, которое возвращается, если Лог_выражение имеет значение ИСТИНА.

Значение_если_ложь – значение, которое возвращается, если Лог_выражение имеет значение ЛОЖЬ.

Например:
=ЕСЛИ(A1<>””;1;0)
<> – не равно,
“” – пустая.
То есть, если ячейка А1 не пустая, возвращается 1, если пустая, то 0.
При этом не важно будет ли в ячейке А1 число, текст или любые другие знаки.

Допустим нам нужно, за заказ свыше 15000 руб применить скидку 10%.
В поле Лог_выражение напишем условие: сумма заказа(В2) больше 15000. Пропишем в Значениe_если_истина В2*0,9, а в Значениe_если_ложь вернем сумму заказа (В2).

Протянем формулу для остальных заказов.

Неправильное построение вложенных функций ЕСЛИ

Часто бывает, что необходимо применить более 1 условия.
Допустим нам надо при сумме заказа свыше 15000 руб, сделать скидку 10%, а свыше 20000 руб 15%.

Excel воспринимает до 7 уровней вложения функций и проверяет условия начиная с первого.

Например, мы напишем формулу неправильно:
=ЕСЛИ(B2>15000;B20,9;ЕСЛИ(B2>20000;B20,85;B2))

То есть если мы напишем первым условием В2>15000, то для суммы свыше 20000, скидка 15% применяться не будет.

Например, для ячейки В4 проверится первое Лог_выражение (B4>15000), Excel увидит, что это выражение верно и применит скидку 10%.

Поэтому начинать будем с суммы заказа больше 20%.

Как построить вложенные функции ЕСЛИ

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

Лог_выражение напишем В2>20000
Значение_если_истина – В2*0,85
В пустое поле Значение_если_ложь необходимо встать курсором и в поле имени (слева от строки формул) выбрать функцию ЕСЛИ.

При этом откроется новое окно, но начало формулы не пропадет.

В новом окне пропишем следующее условие:

Лог_выражение – В2>15000

Значение_если_истина – В2*0,9

Значение_если_ложь – В2.

Протянем формулу для остальных заказов.

Функция ИЛИ (результат изменяется при выполнении одного из нескольких условий)

Добавим к нашему примеру еще одно условие:
Если заказ оплачен более чем на 50%, предоставляется скидка тоже 10%.
Для наглядности занесем все наши условия в таблицу

Первую функцию ЕСЛИ оставляем без изменений.

Встаем курсором на вторую функцию ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

Появятся аргументы второго условия ЕСЛИ.
Стереть поле Лог_выражение (В2>15000)

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию ИЛИ.

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

В появившемся окне прописываем наши 2 условия:

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строку автоматически появляется 3-я. Оставляем ее пустой и нажимаем ОК.

Протянем формулу для остальных заказов.

Функция И (результат изменяется при выполнении нескольких условий)

Скидки за заказ оставим на прежнем уровне:
Заказ более 20000 – 15%
Заказ более 15000 – 10%

Но если заказ более 15000 и оплачен более чем на 50% – скидка 20%

Для наглядности занесем все наши условия в таблицу

Первую функцию ЕСЛИ оставляем без изменений.

Встаем курсором на вторую функцию ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

В появившемся окне стираем Лог_выражение: B2>15000.

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию И.

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

В появившемся окне вписываем 2 наших условия

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строку автоматически появляется 3-я. Оставляем ее пустой и нажимаем ОК.

При нажатии на ОК окно закроется.

Мы прописали только первую строчку (Лог_выражение) в нашем втором условии ЕСЛИ. Снова встаем на 2-ую ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

В первой строке (Лог_выражение) уже прописаны 2 наших условия.
Значение_если_истина исправляем 0,9 на 0,8, чтобы при соблюдении 2-х условий Excel считал скидку 20%
Значение_если_ложь стираем B2 (будем прописывать в нее 3-е условие).

Встаем курсором на Значение_если_ложь и в поле имени выбираем функцию ЕСЛИ.

Появится окно в которое мы будем прописывать 3-е условие.

Лог_выражение: B2>15000

Значение_если_истина: B2*0.9

Значение_если_ложь: B2

Протягиваем формулу для остальных заказов.

Обратите внимание в какой последовательности мы применяем вложенность функций ЕСЛИ.

Если мы пропишем 2-м условием Заказ более 15000 – 10%, а 3-м выполнение 2-х условий, Excel определит 2-е условие как истина и 3-е условие проверять не будет.

Допустим, нужно посчитать время работы оборудования. Сделать это можно несколькими способами.

1. Вычесть из конечной ячейки, начальную.
   Важно: Все ячейки должны быть в формате времени. При введении времени через двоеточие Excel обычно сам форматирует ячейки в нужный формат (3:00).
   Через Формат ячеек в формате Время можно выбрать Тип, необходимый для отображения. Как открыть формат ячеек.

При суммировании отработанного времени Excel покажет неправильный результат.

Для того, чтобы Excel просуммировал все ячейки с отработанным временем, в формате Время выберете Тип: 37:30:55.

2. Разницу во времени можно посчитать в десятичных долях.
Для этого разницу во времени необходимо умножить на 24. Например =(С3-В3)*24.

При суммировании отработанного времени проблем не возникнет.

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

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

Для формата записи ч:мм

Лог_выражение: Дата окончания больше даты начала

Значение_если_истина: К разнице во времени прибавляем 1.

Значение_если_ложь: От времени окончания отнимаем время начала.

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