ifempty

    Оглавление

    Введение

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

    Запись макроса

    Файлы для скачивания

     

    Введение

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

    При записи макроса все необходимые действия записываются в виде кода Visual Basic для приложений (VBA). 

    Кнопка записи макроса находится на вкладке Разработчик, которая по умолчанию скрыта. Чтобы отобразить вкладку Разработчик нужно перейти в меню Файл - Параметры - Настроить ленту. Затем в правой части окна поставить галочку в поле Разработчик и нажать ОК:

     запись макросов рис1

    Рисунок 1. Отображение вкладки Разработчик

    После этого у вас появится дополнительная вкладка Разработчик, как правило, справа от вкладки Вид:

     запись макросов рис2

    Рисунок 2. Вкладка Разработчик

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

     Разберем следующий пример.

    Скачайте файл Запись макросов  - Приложение 1 по ссылке выше. Откройте лист Задача с решением. 

    На листе дана Таблица 1 с расчетом заработной платы на руки и Таблица 2, которая представляет собой таблицу данных. Таблица 2 заполняется и очищается автоматически с помощью кнопок Заполнить и Очистить (см. рисунок 3)

     запись макросов рис3

    Рисунок 3. Автоматическое заполнение и очищение таблицы данных с помощью макросов

     

    Запись макроса

    Создадим аналогичное заполнение Таблицы 2 на листе Задача без решения. 

    Переходим на лист Задача без решения и очищаем Таблицу 2, как показано на рисунке 4.

     запись макросов рис4

    Рисунок 4. Очищение таблицы данных

    Далее открываем вкладку Разработчик и нажимаем на кнопку Запись макросов, после чего заполняем заново Таблицу 2 с помощью инструмента Таблицы данных. В конце останавливаем запись макроса. Смотрите рисунок 5:

     запись макросов рис5

    Рисунок 5. Запись макроса - заполнение таблицы данных

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

     запись макросов рис6

    Рисунок 6. Вставка кнопка вызова макроса

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

     запись макросов рис7

    Рисунок 7. Вставка кнопка вызова макроса

    Теперь нужно записать макрос, который будет очищать таблицу данных. Для этого надо на вкладке Разработчик нажать на кнопку Запись макроса. Затем нужно очистить Таблицу 2 и остановить запись (см. рисунок 8):

     запись макросов рис8

    Рисунок 8. Запись макроса - очищение таблицы данных

    Далее нарисуем кнопку Очистить и привяжем к ней макрос (см. рисунок 9):

      запись макросов рис9

    Рисунок 9. Вставка кнопка вызова макроса

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     



    Оглавление

    Введение

    Контроль ввода чисел

    Контроль ввода даты и времени

    Создание простых выпадающих списков

    Создание динамических выпадающих списков

    Файлы для скачивания

     

    Введение

    Инструмент Проверка данных позволяет контролировать ввод данных в ячейки. Кнопка вызова инструмента находится на вкладке Данные - Проверка данных.

      проверка данных рис1

    Рисунок 1. Расположение кнопки вызова инструмента Проверка данных

    Окно инструмента состоит из трех вкладок:

    1. Параметры - основная вкладка, на которой устанавливаются настройки;
    2. Подсказка по вводу - на данной вкладке можно установить всплывающую подсказку для ячейки;
    3. Сообщение об ошибке - на данной вкладке можно установить вывод сообщения, в случае если пользователь ввел неправильные данные.

     проверка данных рис2

    Рисунок 2. Форма окна инструмента Проверка данных

     

    Контроль ввода чисел

    Скачайте и отктройте файл Проверка данных - Приложение1 по ссылке выше. Откройте лист Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование.  

    На листе дан простой расчет суммы двух чисел (ячейка С2) - А2+В2. 

    Задача: 

    Нужно сделать так, чтобы в ячейки А2 и В2 можно было вводить только целые числа от 10 до 20. Также сделать подсказки и сообщение об ошибке.

    Решение:

    Для решения задачи сначала надо выделить две ячейки А2 и В2, затем перейти на вкладку Данные и нажать на кнопку Проверка данных. 

     проверка данных рис3

    Рисунок 3. Вызов инструмента Проверка данных

    Далее в окне Проверка вводимых значений нужно заполнить поля (см. рисунок 4):

    1. Тип данных - указать Целое число;
    2. Значение  - между;
    3. Минимум - установить 10;
    4. Максимум - установить 20.

      проверка данных рис4

    Рисунок 4. Заполнение вкладки Параметры - инструмент Проверка данных

    После этого переходим на вкладку Подсказка по вводу  и заполняем поля, как показано на рисунке 5:

     проверка данных рис5

    Рисунок 5. Заполнение вкладки Подсказка по вводу - инструмент Проверка данных

    И в конце переходим на вкладку Сообщение об ошибке и заполняем поля, как показано на рисунке 6:

     проверка данных рис6

    Рисунок 6. Заполнение вкладки Сообщение об ошибке - инструмент Проверка данных

    После этого нажимаем на кнопку ОК. 

    Теперь, если вы выделите ячейки А2 или В2, то у вас появится подсказка как на рисунке 7:

     проверка данных рис7

    Рисунок 7. Отображение всплывающей подсказки -инструмент Проверка данных

    Если вы попытаетесь ввести числа меньше 10 или больше 20, то Excel выдаст ошибку (см. рисунок 8):

     проверка данных рис8

    Рисунок 8. Сообщение об ошибке - инструмент Проверка данных

     

    Контроль ввода даты и времени

    Скачайте и откройте файл Проверка данных - Приложение 2 по ссылке выше. Откройте лист Без решения. На листе даны две таблицы  - Таблица 1 и Таблица 2

    Задача: 

    Нужно сделать так, чтобы в Таблицу 1 в ячейки А3 и В3 можно было вводить только даты 2019 года. А в Таблицу 2 в ячейки А7 и В7 можно было вводить только время от 09:00 утра до 18:00 вечера. Также нужно сделать подсказки и сообщения об ошибке, в случае если пользователь введет неверные данные.

    Решение:

    Для начла нужно выделить ячейки А3 и В3 в Таблице 1, затем перейти на вкладку Данные и открыть инструмент Проверка данных. Сделайте настройки, как показано на рисунке 9:

     проверка данных рис9

    Рисунок 9. Настройки трех вкладок инструмента Проверка данных

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

      проверка данных рис10

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

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

     проверка данных рис11

    Рисунок 11. Настройки трех вкладок инструмента Проверка данных

    В результате у вас появится выпадающая подсказка, и в случае ввода неверных данных, например: 08:00, система выдаст сообщение об ошибке (см. рисунок 12).

      проверка данных рис12

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

     

    Создание простых выпадающих списков

    Скачайте файл Проверка данных - Приложение 3 по ссылке выше. Откройте лист Задача без решения.  

    Задача:

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

    Решение:

    Нужно выделить ячейку Е1 и вызвать инструмент Проверка данных.

    Настройки нужно установить как на рисунках 13 и 14.

     проверка данных рис13

    Рисунок 13. Настройка выпадающего списка - инструмент Проверка данных

     проверка данных рис14

    Рисунок 14. Настройка выпадающего списка - инструмент Проверка данных

    В результате при выделении ячейки Е1 справа появляется кнопка выпадающего списка городов:

     проверка данных рис15

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

     

    Создание динамических выпадающих списков

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

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

     

    Скачайте файл Проверка данных - Приложение 4 по ссылке выше. Откройте лист Задача без решения.  

    Сначала нужно таблицу со списком городов сделать умной. Как сделать таблицу умной смотрите в уроке Работа с умными таблицами 

    Далее выделяем ячейку Е1 и открываем инструмент Проверка данных. Настройки проверки данных показаны на рисунке 16.

     проверка данных рис16

    Рисунок 16. Настройка динамического выпадающего списка - инструмент Проверка данных

    Обратите внимание на поле Источник -  в нем указана формула:

    =ДВССЫЛ("Таблица1[Город]")

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

    В результате применения таких настроек, если мы добавим в конец списка еще один город, например Тараз, то он отобразится и в выпадающем списке ячейки Е1:

     проверка данных рис17

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

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление:

    Введение

    Защита листа

    Защита книги

    Защита файла

    Файлы для скачивания

     

    Введение

    К защите данных можно отнести следующее:

    1. Защита листа;
    2. Защита книги;
    3. Защита файла.

    Далее разберем подробнее какждый из этих элементов. 

     

    Защита листа

    Чтобы защитить лист от изменений, нужно перейти на вкладку Рецензирование - Защитить лист. У вас откроется окно, в котором можно установить пароль для защиты листа. А также можно задать разрешенные действия на листе после его защиты. 

     данных рис1

    Рисунок 1. Защита листа

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

    Например, скачайте и откройте файл по ссылке ниже:

    В таблице дан расчет ЗП на руки от суммы оклада за минусом удержаний в виде ОПВ  и ИПН. Нужно защитить лист от редактирования, но при этом оставить возможность редактировать ячейку А2  - сумму оклада.

     данных рис2

    Рисунок 2. Таблица по расчету ЗП на руку

    Для этого нужно правой кнопкой мыши щелкнуть по ячейке А2 и в контекстном меню выбрать Формат ячеек. Далее перейти на вкладку Защита и убрать галочку в поле Защищаемая ячейка.

     Защита данных рис3

    Рисунок 3. Установка разрешения на редактирование ячейки

    Далее нужно защитить лист. Для этого переходим на вкладку Рецензирование и нажимаем Защитить лист. В окне защиты листа вводим пароль и нажимаем ОК.

     Защита данных рис4

    Рисунок 4. Установка защиты листа

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

     

    Защита книги

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

    Чтобы защитить книгу нужно перейти на вкладку Рецензирование  и нажать Защитить книгу.

      Защита данных рис5

    Рисунок 5. Процедура защиты книги

     

    Защита файла

    Если нужно установить пароль на открытие файла, то нужно перейти в меню Файл - Сохранить как - Обзор. 

     данных рис6

    Рисунок 6. Установка пароля на открытие файла

    Далее в окне Общие параметры нужно установить пароль на открытие файла и его изменение.

     данных рис7

    Рисунок 7. Установка пароля на открытие и изменение файла

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Работа с подбором параметра

    Файлы для скачивания

     

    Работа с подбором параметра

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

    Разберем применение данного инструмента на следующем примере. Для этого скачайте файл по ссылке ниже:

    Откройте скачанный файл. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна.

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

    Для этого нужно встать на ячейку G3, затем перейти на вкладку Данные - Анализ, "что если" - Подбор параметра.  У вас должно открыться окно Подбор параметра, как на рисунке 1:

     подбор параметра рис1

    Рисунок 1. Форма окна инструмента Подбор параметра

    Далее в поле Значение нужно указать 300 000, а в поле Изменяя значение ячейки нужно указать ссылку на ячейку, которая содержит значение оклада:

     подбор параметра рис2

    Рисунок 2. Заполнение параметров инструмента Подбор параметра

    После этого нажимаем на кнопку ОК. При этом произойдет перерасчет данных в таблице. Результат долен получиться как на рисунке 3:

     подбор параметра рис3

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

    Для завершения также нужно нажать ОК. В результате получена сумма оклада в размере 366 878,52 тенге, которую нужно установить сотруднику, чтобы на руки он получал ровно 300 000 тенге.

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Введение

    Работа с диспетчером сценариев

    Файлы для скачивания

     

    Введение

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

    Для начала рассмотрим простой пример использования диспетчера сценариев: например, у нас есть расчет заработной платы на руки и мы хотим узнать, как будет меняться заработная плата на руки в течение 5 лет, если ежегодно оклад будет увеличиваться на 50000, ставка ОПВ на 1%, ставка ИПН на 1% и МЗП на 2000 тенге.

    Итоговый отчет, построенный с помощью диспетчера сценариев будет выглядеть следующим образом:

     диспетчер сценариев рис1

    Рисунок 1. Форма отчета, построенного с помощью диспетчера сценариев

    На рисунке1 строка Результат отражает изменение ЗП_на_руки при различных заданных сценариях. 

     

    Работа с диспетчером сценариев

    Скачайте файл Приложение 1.xlsx по ссылке ниже.

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

     диспетчер сценариев рис2

    Рисунок 2. Разрешение редактирования файла

    На листе в Таблице1 находится расчет заработной платы на руки. 

     диспетчер сценариев рис3

    Рисунок 3. Таблица с расчетом заработной платы на руки

    Задача:

    Определить, как будет меняться заработная плата на руки в течение 5 лет, если ежегодно оклад будет увеличиваться на 50000, ставка ОПВ на 1%, ставка ИПН на 1% и МЗП на 2000 тенге.

    Решение:

    Для начала присвоим ячейкам соответствующие имена. Это нужно, чтобы потом отчет имел осмысленные имена показателей. Для этого становимся в ячейку А3 и в левом верхнем углу в поле Имя, где указано А3,  пишем имя ячейки: Оклад. Имя нужно вводить без пробелов, в конце нажать клавишу Enter. У вас должно получиться как показано на рисунке 4.

     диспетчер сценариев рис4

    Рисунок 4. Присвоение ячейке имени

    Также нужно дать имена ячейкам: В3 - Ставка_ОПВ, D3 - МЗП, E3 - Ставка_ИПН, G3 - ЗП_на_руки.

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

     диспетчер сценариев рис9

    Рисунок 5. Таблица показателей по годам

    Далее становимся в ячейку G3, переходим на владку Данные - >Анализ "что, если" -> Диспетчер сценариев.

     диспетчер сценариев рис5

    Рисунок 6. Расположение кнопки Диспетчер сценариев на вкладке Данные

    У вас откроется окно Диспетчер сценариев, где нужно нажать на кнопку Добавить:

     диспетчер сценариев рис6

    Рисунок 7. Форма окна Диспетчера сценариев

    У вас откроется окно Добавление сценария, где нужно указать название сценария: Сценарий 1 год  и в поле Изменяемые ячейки,  удерживая клавишу CTRL, указать ячейки A3, B3, D3 и E3. У вас должно получиться как на рисунке 8.  

     диспетчер сценариев рис7

    Рисунок 8. Добавление сценария в диспетчер сценария

    Нажмите на кнопку ОК. У вас должно открыться окно Значения ячеек сценария, где нужно заполнить поля, как показано на рисунке 9 и нажать на кнопку Добавить:

     диспетчер сценариев рис8

    Рисунок 9. Заполнение значений ячеек сценария

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

     диспетчер сценариев рис10

    Рисунок 10. Параметры пятого сценария

    В окне Значения ячеек сценария нужно нажать на кнопку ОК. В итоге у вас должен получиться список из 5 сценариев:

     диспетчер сценариев рис11

    Рисунок 11. Список сценариев в диспетчере сценариев

    В окне Диспетчера сценариев кнопка Вывести позвоялет отобразить сценарий в Таблице1. Для этого нужно выбрать сценарий и нажать на кнопку Вывести. 

    Чтобы показать отчет по всем сценариям нужно нажать на кнопку Отчет, после чего у вас откроется окно Отчет по сценарию, где Тип отчета нужно указать, как Структура,  и в поле Ячейки результата выбрать ячейку с формулой расчета заработной платы на руки  - G3 и нажать на кнопку ОК:

     диспетчер сценариев рис12

    Рисунок 12. Настройки отчета по сценарию

     

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

     диспетчер сценариев рис13

    Рисунок 13. Отчет по сценариям в сравнительном виде

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление 

    Введение

    Работа с таблицей данных

    Файлы для скачивания

     

    Введение

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

     таблица данных рис1

    Рисунок 1. Расположение кнопки Таблица данных на вкладке Данные

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

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

     таблица данных рис2

    Рисунок 2. Конструкция таблицы данных

    На рисунке 3 показан пример таблицы данных. В Таблице1 находится расчет заработной платы, которую сотрудник получает на руки - за минусом пенсионных взносов и индивидуального подходного налога. В Таблице2 находится таблица данных, которая показывает какой будет зарплата на руки сотрудника, если ставка ИПН будет увеличиваться на 1% и оклад будет увеличиваться на 50 000. 

     таблица данных рис3

    Рисунок 3. Пример построения таблицы данных

     

    Работа с таблицей данных

    Скачайте файл Приложение 1.xlsx по ссылке ниже.

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

     таблица данных рис4

    Рисунок 4. Разрешение редактирования файла

    Задача:

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

    Решение:

    Для использования таблицы данных у нас должно быть:

    1. Расчет/формула, в которую с помощью таблицы данных мы будем подставлять изменяемые значения;
    2. Посмтроить нужную конструкцию, как на рисунке 2;
    3. Настроить и заполнить таблицу.

    Расчет ЗП на руки уже есть - формула находится в ячейке G3. Осталось создать конструкцию таблицы и настроить таблицу данных. На следующем рисунке 5 показана конструкция таблицы данных:

     таблица данных рис5

     Рисунок 5. Конструкция таблицы данных 

    На рисунке 5 по столбцам находятся ставки ИПН, а по строкам оклады к начислению. На пересечении строк и столбцов в ячейке C7 находится ссылка на ячейку с формулой расчета ЗП на руки - =G3. Ставка ИПН увеличивается на 1%, а оклады к начислению на 50000. 

    После того, как конструкция создана, нужно выделить данные начиная от ячейки с формулой до конца таблицы вправо, как показано на рисунке 6, а затем перейти на вкладку Данные  -> Анализ, "что если" - > Таблица данных. 

     таблица данных рис6

    Рисунок 6. Выделение таблицы и вкладка Данные

    У вас откроется окно Таблица данных, где нужно заполнить поля, как указано на рисунке 7 и нажать на кнопку ОК.:

     таблица данных рис7

    Рисунок 7. Окно настройки таблицы данных

    После этого Таблица2 заполнится данными и примет следующий вид:

     таблица данных рис8

    Рисунок 8. Результат работы инструмента Таблица данных

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Введение

    Пример создания сводной таблицы

    Работа с полями. Настройка вида таблицы через параметры полей

    Работа с полями. Добавление вычисляемых полей

    Файлы для скачивания

     

    Введение 

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

    Кнопка создания сводной таблицы находится на вкладке Вставка, в левом верхнем углу окна (см. рисунок 1).

     сводные таблицы рис1

    Рисунок 1. Расположение кнопки создания сводной таблицы на вкладке Вставка

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

     

    Пример создания сводной таблицы

    Скачайте файл Приложение 1.xlsx по ссылке ниже.

    Откройте лист Список сотрудников. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем угла окна, как показано на рисунке 2. 

     сводные таблицы рис2

    Рисунок 2. Кнопка Разрешить редактирование

    Задача:

    На основании таблицы на листе Список сотрудников нужно создать сводную таблицу и отобразить суммы окладов по каждому отделу.

    Решение:

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

     сводные таблицы рис3

    Рисунок 3. Создание сводной таблицы

    У вас откроется окно Создание сводной таблицы, в котором нужные поля уже заполнены. Нажмите на кнопку ОК.

     сводные таблицы рис4

    Рисунок 4. Окно Создание сводной таблицы

    У вас появится новый лист Лист2, на котором будет раполагаться сводная таблица (см. рисунок 5).

     сводные таблицы рис5

    Рисунок 5. Лист сводной таблицы

    Следующий рисунок 6 показывает назначение областей:

     сводные таблицы рис6

    Рисунок 6. Назначение областей на листе сводной таблицы

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

     сводные таблицы рис7

    Рисунок 7. Отчет сводной таблицы - суммы окладов по отделам 

     

    Работа с полями. Настройка вида таблицы через параметры полей

    Продолжаем работать в файле  Приложение 1.xlsx. 

    Задача:

    Отобразить ФИО сотрудников и их возраст. При этом фамилии, имена и отчества сотрудников должны быть в разных столбцах.

    Решение:

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

     сводные таблицы рис8

    Рисунок 8. Отчет сводной таблицы - ФИО сотрудников и их возраст

    Обратите внимание, что таблица представлена в иерархическом виде -  поля Фамилия, Имя и Отчество расположены в одном столбце. Нам нужно чтобы они были в отдельных столбцах. Для этого переходим на вкладку Конструктор -> Макет отчета, где выбираем Показать в табличной форме, как показано на рисунке 9.

     сводные таблицы рис9

    Рисунок 9. Настройка табличного вида сводной таблицы

    Таблица примет следующий вид:

     сводные таблицы рис10

    Рисунок 10. Отчет сводной таблицы - ФИО сотрудников и их возраст

    Чтобы не было промежуточных итогов, нужно перейти на вкладку Констурктор -> Промежуточные итоги, где выбрать Не показывать промежуточные итоги.

     сводные таблицы рис11

    Рисунок 11. Отчет сводной таблицы - ФИО сотрудников и их возраст

    В результате таблица примет вид, как на рисунке 12:

     сводные таблицы рис12

    Рисунок 12. Отчет сводной таблицы - ФИО сотрудников и их возраст

     

    Работа с полями. Добавление вычисляемых полей

    Продолжаем работать в той же таблице.

    Задача:

    Показать оклады сотрудников, вычислить и показать их обязательные пенсионные взносы (ОПВ), которые рассчитываются по формуле:

    ОПВ=Оклад*10%

    Решение:

    Справа в полях сводной таблицы убираем флаг с поля Возраст и активируем флаг на поле Оклад, как на рисунке 13.

     сводные таблицы рис13

    Рисунок 13. Отчет сводной таблицы - ФИО сотрудников и их оклады

    Далее переходим на владку Анализ -> Поля, элементы и наборы -> Вычисляемое поле

     сводные таблицы рис14

    Рисунок 14. Вкладка Анализ -> Поля, элементы и наборы

    У вас откроется окно Вставка вычисляемого поля, где:

    1. В поле Имя нужно указать - ОПВ;
    2. В поле Формула нужно задать формулу расчет ОПВ: =Оклад*10%, при этом поле Оклад выбирается из списка полей в разделе Поля либо двойным щелчком, либо кнопкой Добавить поле. 

     сводные таблицы рис15

    Рисунок 15. Вставка вычисляемого поля

    Далее нажимаем OK. В результате в сводной таблице появится новый столбец Сумма по полю ОПВ. При этом в правой части в области полей появится новое поле ОПВ, как показано на рисунке 16.

     сводные таблицы рис16

    Рисунок 16. Отчет сводной таблицы - ФИО сотрудников, суммы окладов и ОПВ 

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление:

    Введение

    Пример решения задачи

    Файлы для скачивания

     

    Введение

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

     промежуточный итог рис1

    Рисунок 1. Расположение кнопки Промежуточный итог

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

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

    Для лучшего понимания работы инструмента Промежуточный итог разберем пример решения следующей задачи.

     

    Пример решения задачи  с помощью инструмента Промежуточный итог

    Скачайте и откройте файл Приложение 1.xlsx, лист Лист (без решения)  по ссылке выше. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 2.

     промежуточный итог рис2

    Рисунок 2. Разрешение редактирования файла

    На листе дан список сотрудников. Нужно с помощью инструмента Промежуточный итог определить итоговые суммы окладов по каждому отделу. 

    Первое, что нужно сделать - это отсортировать таблицу по столбцу Отдел. Для этого выделяем таблицу целиком вместе с заголовками, переходим на вкладку Данные и нажимаем Сортировка. В окне Сортировка в поле Сортировать по указываем столбец Отдел,  также нужно убедиться, что стоит активным флаг в поле Мои данные содержат заголовки, как на рисунке 3.

     промежуточный итог рис3

    Рисунок 3. Сортировка таблицы по столбцу Отдел

    В результате таблица должна быть отсортирована по алфавиту по столбцу Отдел, как показано на рисунке 4.

     промежуточный итог рис4

    Рисунок 4. Таблица отсортированная по столбцу Отдел

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

     промежуточный итог рис5

    Рисунок 5. Выделение таблицы и переход на вкладку Данные

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

    1. При каждом изменении в -  в данном поле нужно указать столбец, по которому нужно группировать итоги, это столбец по которому мы сортировали таблицу;
    2. Операция -  в данном поле указывается математическая операция;
    3. Добавить итоги по - в данном поле нужно указать столбец, по которому нужно суммировать (или применить другую операцию) значения;
    4. Заменить текущие итоги - поле оставляется без изменений;
    5. Конец страницы между группами - поле оставляется без изменений. Данное поле позволяет каждую группу итогов расположить на отдельном листе;
    6. Итоги под данными - поле оставляется без изменений. Если снять флаг, то итоги будут отображаться над данными.

    У вас должно получиться, как показано на рисунке 6.

     промежуточный итог рис6

    Рисунок 6. Окно настройки промежуточных итогов

     

    Далее нажимаем кнопку ОК. Таблица должна принять вид, как показано на рисунке 7.

     промежуточный итог рис7

    Рисунок 7. Результат применения инструмента промежуточных итогов

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

     промежуточный итог рис8

    Рисунок 8. Расположение кнопки Убрать все

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Введение

    Пример решения задачи №1

    Способ 1 - суммирование путем последовательного перебора листов

    Способ 2 - консолидация листов с помощью трехмерных формул

    Способ 3 - консолидация листов с помощью инструмента консолидации

    Пример решения задачи №2

    Файлы для скачивания

     

    Введение

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

    Можно выделить три основных способа консолидации листов:

    1. Путем последовательного перебора листов при написании обычной формулы;
    2. С помощью трехмерных формул;
    3. С помощью инструмента консолидации.

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

     

     Пример решения задачи №1

    Скачайте и откройте файл Консолидация_приложение1.xlsx  по ссылке ниже. 

    Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 1.

     консолидация рис1

     Рисунок 1. Разрешение редактирования файла

    Файл состоит из 5 листов. На листах филиал 1, филиал 2 и филиал 3 находятся списки отделов с количеством сотрудников и суммами ЗП. Структура таблиц и их положение на листе одинаковые. Содержимое таблиц отличается. На листе Сборка (без решения) находятся таблицы, в которые нужно путем суммирования объединить данные трех филиаловВ Таблице1 нужно данные консолидировать первым способом: суммировать ячейки последовательным перебором листов, в Таблице2 нужно консолидаровать данные вторым способом: с помощью трехмерной формулы, и в таблице3 нужно консолидаровать данные с помощью инструмента консолидации. 

     

    Способ 1 - суммирование путем последовательного перебора листов

    В скачанном файле на листе Сборка (без решения) встаньте на ячейку B3 и поставьте знак равно  = , как показано на рисунке 2:

     консолидация рис2

    Рисунок 2. Начало формулы сложения

    Далее перейдите на лист филиал1 и выберите ячейку B2, поставьте знак плюс +  и затем также на листах филиал2 и филиал3 выберите и суммируйте ячейки B2. У вас должна получиться такая формула:

    ='филиал 1'!B2+'филиал 2'!B2+'филиал 3'!B2

    на листе это будет выглядеть как на рисунке 3:

     консолидация рис3

    Рисунок 3. Формула сложения ячеек из разных листов

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

     консолидация рис4

    Рисунок 4. Консолидация листов первым способом

     

    Способ 2 - консолидация листов с помощью трехмерных формул

    В скачанном файле на листе Сборка (без решения) в ячейке B14 вызовите функцию суммирования, для этого встаньте на ячейку В14, перейдите на вкладку Формулы и нажмите на кнопку Автосумма. Внутри скобок функции СУММ удалите ссылки, как показано на рисунке 5:

     консолидация рис5

    Рисунок 5. Настройка трехмерной формулы суммирования (шаг 1)

    Далее перейдите на лист филиал1 и выберите ячейку B2, затем, удерживая клавишу Shift мышкой выберите лист филиал3 и нажмите клавишу Enter. Обратите внимание, что при этом лист филиал3 не будет открыт, но формула станет трехмерной - то есть будет охватывать диапазон листов. Формула будет выглядеть так:

    =СУММ('филиал 1:филиал 3'!B2)

    На листе Сборка (без решения) в ячейке B14 у вас должен быть результат как на рисунке 6:

     консолидация рис6

    Рисунок 6. Трехмерная формула суммирования

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

     консолидация рис7

    Рисунок 7. Консолидация листов вторым способом

     

    Способ 3 - консолидация листов с помощью инструмента консолидации

    В скачанном файле на листе Сборка (без решения) встаньте на ячейку Е2, перейдите на вкладку Данные и нажмите на кнопку Консолидация, как показано на рисунке 8.

     консолидация рис8

    Рисунок 8. Вкладка Данные - кнопка открытия окна инструмента консолидации

    У вас откроется окно Консолидация, где поле Функция оставляем без изменений, так как нам нужно суммировать значения. Перемещаемся в поле ссылка и выбираем таблицу на листе филиал1. Таблицу нужно выбрать полностью вместе с заголовками, как показано на рисунке 10. После чего нужно нажать на кнопку Добавить.

     консолидация рис10

     Рисунок 10. Окно с настройками инструмента консолидации листов

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

     консолидация рис11

    Рисунок 11. Окно с настройками инструмента консолидации листов

    Далее нужно аналогично перейти на лист филиал2, выделить таблицу целиком вместе с заголовками и нажать на кнопку Добавить. И также сделать на листе филиал3. Посе этого установить активными флаги Подписи верхней строки и Значение левого столбца. В итоге у вас должно получиться как на рисунке 12.

     консолидация рис12

     Рисунок 12. Окно с настройками инструмента консолидации листов 

    Далее нажмите кнопку ОК. В результате Таблица3 должна заполниться сводными данными как на рисунке 13.

     консолидация рис13

    Рисунок 13. Результат вызова инструмента консолидации

     

    Пример решения задачи №2

    Скачайте и отктройте файл Консолидация_Приложение2.xlsx. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование, как показано на рисунке 1. Файл состоит из 5 листов. На листах Головной офис, Отделение 1 и Отделение 2 находятся списки сотрудников, где среди прочего указаны отделы, в которых они работают и их оклады. На листе Консолидация (без решения) нужно определить среднюю заработную плату в разрезе отделов по группе компаний. 

    Так как количество строк на листах разное, то тут нужно использовать инструмент консолидации. Для этого на листе  Консолидация (без решения) становимся на ячейку А1, переходим на вкладку Данные и нажимаем на кнопку Консолидация, как показано на рисунке 14.

     консолидация рис14

    Рисунок 14. Вкладка Данные - кнопка открытия окна инструмента консолидации

    У вас отктроется окно с настройками консолидации. В поле фукнция нужно указать Среднее, так как по сусловию задачи нужно определить среднюю заработную плату. Далее становимся в поле Ссылка, переходим на лист Головной офис и выбираем диапазон ячеек начиная от ячейки G6 и до конца таблицы до ячейки N56 включительно, как показано на рисунке 15.

     консолидация рис15

     Рисунок 15.  Настройка инструмента консолидации 

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

     консолидация рис16

     Рисунок 16. Окно настройки инструмента консолидации

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

     консолидация рис17

    Рисунок 17. Окно настройки инструмента консолидации

    Нажмите кнопку ОК. У вас должно получиться как на рисунке 18.

     консолидация рис18

    Рисунок 18. Результат использования инструмента консолидации

    Так как по условию задачи нам нужно определить среднюю заработную плату в разрезе отделов (по каждому отделу), то получается, что столбцы от В до G являются лишними. Удаляем эти столбцы и в резльтате получается так:

     консолидация рис19

    Рисунок 19. Итоговая таблица: данные о среднем окладе по отделам

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Введение

    Форматирование только ячеек, которые содержат

    Использование формул для определения форматируемых ячеек

    Файлы для скачивания

     

    Введение

    Условное форматирование позволяет автоматизировать форматирование ячеек путем создания правил. Кнопка вызова условного форматирования расположена на вкладке Главная - Условное форматирование.

    Наиболее часто используется два типа правила:

    1. Форматирование только ячеек которые содержат;
    2. Использование формул для определения форматируемых ячеек.

     

    Форматирование только ячеек которые содержат.

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

    Задача:

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

    Решение:

    Для этого нужно выделить столбец Образование, далее перейти на вкладку Главная - Условное форматирование и нажать Создать правило:

     форматирование рис1

    Рисунок 1. Вызов инструмента Условное форматирование

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

    1. Значение ячейки поменять на текст;
    2. Вид сравнения вместо Между установить Содержит;
    3. Указать значение ячейки - Высшее
    4. Далее указать формат ячеек

    Смотрите рисунок 2:

     форматирование рис2

    Рисунок 2. Условное форматирование - создание правила

    В конце нужно нажать на кнопку ОК. В результате у вас должно получиться как на рисунке 3:

     форматирование рис3

     Рисунок 3. Условное форматирование - результат работы условного форматирования

     

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

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

    Задача:

    Нужно фамилии у специалистов с высшим образованием закрасить зеленым цветом.

    Решение:

    Для этого нужно сначала встать на первую фамилию, а затем открыть Условное форматирование - Создать правило:

     форматирование рис4

     Рисунок 4. Вызов условного форматирования.

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

    1. Нужно указать тип правила - Использование формулы для определения форматируемых ячеек;
    2. Внизу указать формулу: =C2="высшее" Обратите внимание, что С2 указывается без знаков доллара, а слово "высшее" взято в кавычки;
    3. Указать формат ячеек, а именно выбрать заливку зеленым цветом.

     

     форматирование рис5

    Рисунок 5. Использование формулы для определения форматируемых ячеек

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

    Далее нужно формат первой ячейки скопировать и применить к остальным ячейкам в столбце. Лля этого можно использовать инструмент Формат по образцу. Смотрите рисунок 6.

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

    Рисунок 6. Использование инструмента Формат по образцу

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Введение

    Функция ВПР с интервальным просмотром 0 (ЛОЖЬ);

    Функция ВПР с интервальным просмотром 1 (ИСТИНА);

    Файлы для скачивания

     

    Введение

    Функция ВПР позволяет данные из одной таблицы перенести в другую таблицу с помощью общего критерия (искомого значения). 

    У функции есть четыре параметра:

    1. Искомое значение - значение, по которому будет осуществляться поиск;
    2. Таблица - таблица или диапазон ячеек, среди которых, по левому крайнему столбцу, будет осуществляться поиск искомого значения;
    3. Номер столбца - порядковый номер столбца таблицы, указанной выше, значение которого нужно перенести в текущую таблицу;
    4. Интервальный просмотр - переменная, которая отвечает за механизм сопоставления искомого значения и левого крайнего столбца таблицы. Может принимать значения 0 или 1 (ЛОЖЬ или ИСТИНА). Если указать 0 - то сопоставляться искомое значение в таблице будет по полному совпадению. Если указать 1 - то поиск будет осуществляться приближенно к искомому значению. 

     ВПР рис1

    Рисунок 1. Аргументы функции ВПР

    Чтобы лучше понять работу функции ВПР, рассмотрим следующие два примера  - с интервальным просмотром 0 и 1. Вы можете скачать файл примера по ссылке ниже:

    Откройте лист - Интервальный просмотр 0. На листе даны две таблицы. В Таблице1 находятся ID и наименования товаров. В Таблице2 известен ID, но неизвестно наименование. Наименование нужно установить в ячейку E3 из Таблицы1 с помощью функции ВПР. 

    Настройки функции будут выглядеть так:

     ВПР рис2

    Рисунок 2. Настройки функции ВПР с интервальным просмотром 0

    Красной рамкой указано искомое значение, синей рамкой - таблица (в данном случае это Таблица1). Номер_столбца  - 2, потому что Товар - второй столбец в Таблице1. Интервальный_просмотр в данном случае 0 (ЛОЖЬ)

    Теперь откройте лист Интервальный просмотр 1. На листе также даны две таблицы. В Таблице1 указана информация о скидках - порог заказа и размер скидки для каждого порога заказа. Таблицу1 можно прочитать так:

    Если заказали от 0 до 4 единиц товара, то скидка будет в размере 0%,если заказали от 5 до 9 единиц товара, то скидка будет в размере 10%. Если заказали от 10 до 14 единиц товара, то размер скидки будет 15%. Если заказали от 15 и выше единиц товара, то размер скидки будет 20%.

    В Таблице2 дан перечень заказов и процент скидки. Процент скидки был определен с помощью функции ВПР из Таблицы1. Настройки функции в ячейке Е3 выглядят следующим образом:

     ВПР рис3

    Рисунок 3. Настройки функции ВПР с интервальным просмотром 1

    Значение ячейки D3  - это искомое значение. Данное искомое значение функция будет искать в левом крайнем столбце Таблицы1. Размер скидки в Таблице1 находится во втором столбце, поэтому номер_столбца - 2. Интервальный просмотр -1. 

    В данном случае, функция в Таблице1 по левому крайнему столбцу находит значение ближайшее к искомому значению в сторону уменьшения.  Например, если поступившие заказы в Таблице2 - 10, то ближайший к нему порог заказа в Таблице1 - 10, и размер скидки соответственно - 15%. Если поступившие заказы в Таблице2  - 12, то ближайший к нему порог заказа в сторону уменьшения также будет 10 и размер скидки, соответственно  - 15%.

    У функции ВПР есть три ограничения:

    1. Поиск искомого значения производится по левому крайнему столбцу таблицы;
    2. Функция находит только первый элемент сверху вниз, который соответствует искомому значению и затем останавливает поиск;
    3. Крайний левый столбец таблицы, в которой будет производиться поиск искомого значения должен быть выстроен по возрастанию. Это относится только к функции ВПР с интервальным просмотром 1

     

    Функция ВПР с интервальным просмотром 0 (ЛОЖЬ)

    Скачайте файл Функция ВПР с интервальным просмотром 0 (без решения).xlsx по ссылке ниже.

    Отройте лист  - Задача без решения. Если у вас стоит защищенный просмотр, то нужно нажать на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 4.

     ВПР рис4

    Рисунок 4. Разрешение редактирования файла

    Задача:

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

     ВПР рис5

    Рисунок 5. Задача по практикуму 1

     

    Предварительный разбор:

    Функцию нужно будет вызывать в ячейке B2. Но прежде чем вызывать функцию, таблицу на листе Список сотрудников нужно изменить: столбец Табельный номер нужно скопировать и вставить слева от столбца Фамилия, как показано на рисунке 6. 

     ВПР рис6

    Рисунок 6. Установка столбца Табельный номер перед столбцом Фамилия

    Это нужно сделать, потому что искомое значение функция ищет по левому крайнему столбцу таблицы. Искомым значением в данном случае будет табельный номер в ячейке А2 на листе Задача без решения.. В качестве таблицы будет указан диапазон от А2 до D24 на листе Список сотрудников. Так как для начала нужно найти фамилию сотрудника, то номер столбца будет 2. Потому что на листе Список сотрудников столбец Фамилия является вторым по порядку. Обратите внимание, что речь не идет о столбцах листа, важно учитывать порядковый номер столбца таблицы. Интвервальный просмотр  - 0, так как нам нужно полное совпадение табельных номеров.

      ВПР рис7

    Рисунок 7. Обозначение цветом параметров функции ВПР

     

    Вызов функции ВПР:

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

    1. В поле Искомое_значение указываем: $A2
    2. В поле Таблица указываем: 'Список сотрудников'!$A$2:$F$24
    3. В поле Номер_столбца указываем: 2
    4. В поле Интервальный просмотр указываем 0

      ВПР рис8

    Рисунок 8. Аргументы функции ВПР

    После того, как закончите ввод данных нажмите ОК. У вас должно получиться как на рисунке 9:

     ВПР рис9

     Рисунок 9. Результат вызова фукнции ВПР.

    Далее с помощью маркера автозаполнения функцию в ячейке B2 нужно протянуть вправо. У вас должно получиться как на рисунке 10:

     ВПР рис10

     Рисунок 10. Результат вызова фукнции ВПР

    Обратите внимание, что вместо имени и отчества отображаются фамилии. Это связано с тем, что Номер_столбца в функции указан 2. Тогда как на листе Список сотрудников порядковый номер столбца Имя - 3, а Отчество - 4. Поэтому в ячейке С2 внутри функции Номер_столбца нужно указать - 3, а в ячейке  D2 внутри функции Номер_столбца нужно указать - 4.

      ВПР рис11

    Рисунок 11. Аргументы функции ВПР

    Чтобы изменить Номер_столбца внутри функции встаньте на ячейку С2 и нажмите на нопку ƒx. У вас откроется окно Аргументы функции, где в поле Номер_столбца нужно указать 3, как показано на рисунке 12.. И нажать на кнопку ОК.

     ВПР рис12

    Рисунок 12. Аргументы функции ВПР

    У вас должно получиться как на рисунке 13. Далее то же самое проделайте с ячейкой D2, только вместо 3 в поле Номер_столбца укажите 4. В результате у вас должно получиться как на рисунке 14.

     ВПР рис13

     Рисунок 13. Результат вызова функции ВПР 

     ВПР рис14

    Рисунок 14. Результат вызова функции ВПР 

    Далее выделите ячейки В2, С2 и D2 и протяните их вниз с помощью маркера автозаполнения. У вас должно получиться как на рисунке 15.

     ВПР рис15

     Рисунок 15. Результат работы функции ВПР 

     

    Разбор проделанной работы:

     Встаньте на ячейку B2  и нажмите кнопку ƒx, у вас откроется окно Аргументы функции. 

     ВПР рис16

     Рисунок 16. Заполненные аргументы функции ВПР

    Искомое значение - это табельный номер в текущей таблице, в которой мы пишем формулу. Обратите внимание, что используется смешанный тип ссылок с фиксацией столбца А- $A2. Это сделано для того, чтобы при копировании формулы вправо с помощью маркера автозаполнения, искомое значение для строки не менялось. Иначе, искомое значение будет плавать вправо: А2 станет В2 и т.д.

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

    В ячейку B2 функция должна вернуть фамилию сотрудника. Порядковый номер столбца с фамилиями в таблице на листе Список сотрудников  второй, поэтому в поле Номер_столбца нужно указывать -2.

    В поле Интервальный_просмотр указываем  - 0, так как нужно, чтобы табельные номера в текущей таблице полностью совпадали с табельными номерами в таблице на листе Список сотрудников.

    Один раз написав функцию, мы ее протянули вправо, после чего в ячейках C2 и D2 внутри функций в полях Номер_столбца поставили значения 3 и 4 соответственно. Это сделано, потому что в таблице на листе Список сотрудников столбцы с именами и отчествами имеют порядковые номера 3 и 4. Далее, выделив строку, протянули формулы вниз с помощью маркера автозаполнения.

    Посмотреть результат решения вы можете в файле Функция ВПР с интервальным просмотром 0 (с решением) по ссылке ниже.

     

    Функция ВПР с интервальным просмотром 1 (ИСТИНА)

    Скачайте файл Функция ВПР с интервальным просмотром 1 по ссылке ниже.

    Отройте лист  - Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 4. 

     

    Задача:

    На листе даны две таблицы. В Таблице1 указаны поступившие заказы со стоимостью. Нужно определить стоимость с учетом скидки. Размер скидки неизвестен. Размер скидки нужно определить из Таблицы2. В Таблице2 указаны порог заказа и соответствующая скидка.

     

    Предварительный разбор:

    Функцию нужно будет вызывать в ячейку С3. Один раз создав функцию, ее нужно будет протянуть вниз с помощью маркера автозполнения для ячейки С12. Искомым значением в данном случае будет являться ячейка А3 - в столбце Поступившие заказы. В качестве таблицы нужно указать Таблицу2. Номер_столбца - 2, потому что в размеры скидкок в Таблице2 находятся во втором столбце. Интервальный просмотр  - 1. Так как искомое значение в Таблице2 должно быть определено приближенно к порогу заказа. 

      ВПР рис17

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

     

    Вызов функции ВПР:

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

    1. В поле Искомое_значение указываем: A3
    2. В поле Таблица указываем: $G$2:$H$6
    3. В поле Номер_столбца указываем: 2
    4. В поле Интервальный просмотр указываем: 1

     ВПР рис18

     Рисунок 18. Аргументы функции ВПР

    После того, как закончите ввод данных нажмите ОК. У вас должно получиться как на рисунке 19:

     ВПР рис19

    Рисунок 19. Результат вызова функции ВПР

    Далее нужно протянуть формулу вниз до ячейки С12 включительно с помощью маркера автозаполнения. В результате у вас должно получиться как на рисунке 20.

     ВПР рис20

    Рисунок 20. Результат работы функции ВПР с интервальным просмотром 1

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Введение

    Пример решения задачи

    Файлы для скачивания

     

    Введение

    Функция СЧЕТЕСЛИ используется для подсчета количества ячеек, соответствующих определенному критерию. Функция находится в категории Статистические

     рис1

     Рисунок 1. Расположение функции СЧЕТЕСЛИ на вкладке Формулы 

    Функция имеет два аргумента:

    1. Диапазон - в данное поле указывается диапазон ячеек, в котором нужно определить количество совпадений;
    2. Критерий - в данное поле указывается значение (или ячейка, содержащая значение), которое нужно найти внутри диапазона, указанного выше.

     рис2

    Рисунок 2. Аргументы функции СЧЕТЕСЛИ

     

     Пример решения задачи

    Скачайте файл Файл примера функция СЧЕТЕСЛИ.xlsx по ссылке ниже.

    Откройте лист  - Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 3.

     рис3

     Рисунок 3. Кнопка Разрешить редактирование.

    Задача:

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

     

     рис4

    Рисунок 4. Ячейки , которые нужно заполнять через Счетесли

    Предварительный разбор:

    Для начала нужно встать на ячейку AG4  и вызвать функцию СЧЕТЕСЛИ. В качестве диапазона нужно указать строку с  буквенными обозначениями по сотруднику по строке 4, критерием будет выступать буква О в ячейке AG3.

     рис5

    Рисунок 5. Предварительный разбор

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

    Вызов функции:

    Становимся на ячейку AG4, далее переходим на вкладку Формулы и в разделе Статистические находим функцию СЧЕТЕСЛИ:

     рис6

    Рисунок 6. Вызов функции СЧЕТЕСЛИ

    Далее в поле Диапазон указываем строку 4 по первому сотруднику, в поле Критерий указываем ячейку AG3. Диапазон фиксируем смешанными ссылками с фиксацией столбца, критерий фиксируем смешанной ссылкой с фиксацией строки:

     рис7

    Рисунок 7. Заполнение аргументов функции СЧЕТЕСЛИ

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

     рис8

    Рисунок 8. Копирование функции СЧЕТЕСЛИ с помощью маркера автозаполнения

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.




     


    Оглавление

    Введение

    Функция СУММЕСЛИ  - пример решения задачи №1

    Функция СУММЕСЛИ  - пример решения задачи №2

    Файлы для скачивания

     

    Введение

    Функция СУММЕСЛИ используется, если необходимо суммировать диапазон ячеек по определенному критерию. 

    Функция имеет три параметра:

    1. Диапазон - в данное поле указывается диапазон ячеек, среди которых программа будет искать критерий;
    2. Критерий - в данное поле указывается значение или ссылка на ячейку со значением, которое программа будет искать внутри вышеуказанного диапазона;
    3. Диапазон_суммирования - в данное поле указывается диапазон ячеек, которые нужно суммировать.

     СуммЕсли рис1

    Рисунок 1. Аргументы функции СУММЕСЛИ

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

     СуммЕсли рис2

     Рисунок 2. Пример работы функции СУММЕСЛИ 

    Теперь посмотрите на рисунок 3, на котором обозначены заполненные аргументы функции. На рисунке Диапазон отмечен красным цветом, Критерий -  зеленым, а Диапазон суммирования - синим. 

     СуммЕсли рис3

    Рисунок 3. Аргументы функции СУММЕСЛИ

     Таким образом, получается, что функция ищет Критерий внутри Диапазона и, если находит его, то суммирует значения ячеек внутри Диапазона суммирования

     

    Функция СУММЕСЛИ  - пример решения задачи №1

    Скачайте файл Функция СУММЕСЛИ список сотрудников.xlsx по ссылке ниже.

    Откройте лист Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 4.

     СуммЕсли рис4

     Рисунок 4. Кнопка Разрешить редактирование.

    Задача:

    На листе даны две таблицы: в Таблице1 находится список сотрудников, а также указаны их должности, отделы, в которых они работают и оклады. В Таблице2 находится список отделов и пустая графа Сумма окладов, в которой должны отражаться суммы заработных плат по отделам. Задача  - заполнить графу Сумма окладов в Таблице2 с помощью функции СУММЕСЛИ.

     СуммЕсли рис5

    Рисунок 5. Задача по практикуму 1

    Предварительный разбор:

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

    Критерием для первой строки будет ячейка F3, которая содержит название отдела. Этот критерий нужно искать внутри диапазона, а значит диапазоном будут ячейки B3:B45 (от B3 до B45 включительно), то есть список отделов в Таблице1. Суммировать нужно оклады, поэтому диапазоном суммирования будут ячейки D3:D45 (от D3 до D45 включительно), то есть список окладов в Таблице1.

    На рисунке 6 разными цветами выделены соответствующие диапазоны.

     СуммЕсли рис6

    Рисунок 6. Обозначение аргументов функции разными цветами

    Вызов функции СУММЕСЛИ:

    Нужно встать на ячеку G3 перейти на вкладу Формулы, в разделе Математические выбрать СУММЕСЛИ (вы также можете вызвать функцию через мастер функций с помощью кнопки ƒx или написать ее вручную).

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

    1. В поле Диапазон указываем: $B$3:$B$45
    2. В поле Критерий указываем: F3
    3. В поле Диапазон_суммирования указываем: $D$3:$D$45

     СуммЕсли рис7

    Рисунок 7. Заполненные аргументы функции СУММЕСЛИ

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

     СуммЕсли рис8

    Рисунок 8. Результат вызова функции СУММЕСЛИ

    Далее с помощью маркера автозаполнения протяните формулу вниз до ячейки G9  включительно. У вас должно получиться как на рисунке 9.

     СуммЕсли рис9

    Рисунок 9. Графа Сумма окладов после копирования формулы с помощью маркера автозаполнеия

    Разбор проделанной работы:

    Теперь давайте попробуем разобраться с написанной функцией. Откройте окно с настройками функции первой ячейки - для этого встаньте на ячейку G3 и нажмите кнопку ƒx, как показано на рисунке 10:

     СуммЕсли рис10

    Рисунок 10. Открытие настроек написанной функции

    У вас должно открыться окно Аргументы функции как на рисунке 11:

     СуммЕсли рис11

     Рисунок 11. Аргументы функции СУММЕСЛИ

    Посмотрим внимательно на указанные аргументы функции.  Словами настройки функции можно было бы выразить так:

    Если значение ячейки F3 будет найдено среди ячеек от B3 до B45, то параллельные им ячейки из диапазона от D3 до D45 нужно суммировать.

    Значение ячейки F3 (отдел из списка в Таблице2)  - это критерий. Ячейки от B43 до В45 (весь список отделов в таблице 1) - это диапазон. Ячейки от D3 до D45 (весь список окладов в таблице 1) - это диапазон_суммирования.

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

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

     

    Функция ЕСЛИ  - пример решения задачи №2

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

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

    Задача:

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

     СуммЕсли рис12

    Рисунок 12. Задача по подсчету количества часов

    Предварительный разбор:

    Функцию СУММЕСЛИ  будем вызывать дважды. Один раз для графы Кол-во часов в праздничные дни и один раз для графы Кол-во часов в будние дни.

    Для начала функцию нужно будет вызывать в ячейке J6 - это первая ячейка графы Кол-во часов в праздничные дни.  У нас есть критерий для опредления того, что день праздничный  - это буква П. Диапазоном, внутри которого функция будет искать критерий, будут ячейки от B3 до I3. Диапазоном суммирования будут ячейки параллельные диапазону  - ячейки от B6 до I6

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

    Аналогично нужно будет вызвать функцию для графы Кол-во часов в будние дни. Функцию нужно будет вызывать в ячейку K6. Параметры функции останутся такие же, кроме критерия - в качестве критерия будет пустая ячейка  - "". Далее также нужно будет протянуть функцию вниз для остальных строк.

    Вызов функции СУММЕСЛИ:

    Нужно встать на ячейку J6, перейти на вкладу Формулы, в разделе Математические выбрать СУММЕСЛИ (вы также можете вызвать функцию через мастер функций с помощью кнопки ƒx или написать ее вручную).

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

    1. В поле Диапазон указываем: $B$3:$I$3
    2. В поле Критерий указываем: "п"
    3. В поле Диапазон_суммирования указываем: B6:I6

     СуммЕсли рис13

    Рисунок 13. Заполненные аргументы функции СУММЕСЛИ

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

     СуммЕсли рис14

    Рисунок 14. Результат вызова функции СУММЕСЛИ

    Далее с помощью маркера автозаполнения протяните формулу вниз до ячейки J15  включительно. У вас должно получиться как на рисунке 15.

     СуммЕсли рис15

    Рисунок 15. Графа Кол-во часов в праздничные дни

     Теперь осталось заполнить столбец Кол - во часов в будние дни. Для этого нужно встать на ячейку K6, перейти на вкладу Формулы, в разделе Математические выбрать СУММЕСЛИ (вы также можете вызвать функцию через мастер функций с помощью кнопки ƒx или написать ее вручную).

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

    1. В поле Диапазон указываем: $B$3:$I$3
    2. В поле Критерий указываем: ""
    3. В поле Диапазон_суммирования указываем: B6:I6

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

     СуммЕсли рис16

    Рисунок 16. Аругменты функции СУММЕСЛИ

    После заполнения полей, нажмите на кнопку ОК. У вас должно получиться как на рисунке 17.

     СуммЕсли рис17

     Рисунок 17. Результат вызова функции СУММЕСЛИ

    Далее протяните формулу вниз с помощью маркера автозаполнения, как показано на рисунке 18.

     СуммЕсли рис18

     Рисунок 18. Графа Кол-во часов в будние дни после заполнения

     Разбор проделанной работы:

    Откройте настройки функции в ячейке J6. Для этого встаньте на ячейку J6  и нажмите на кнопку нажмите кнопку ƒx, как показано на рисунке 19:

     СуммЕсли рис19

     Рисунок 19. Открытие настроек написанной функции 

     У вас должно открыться окно Аргументы функции как на рисунке 20:

      СуммЕсли рис20

    Рисунок 20. Аргументы функции СУММЕСЛИ

    В качестве диапазона указаны ячейки от B3 до I3 - это ячейки, по которым определяются праздничные или будние дни. В качестве критерия мы просто указали букву П в кавычках. Кавычки очень важны, так как буквы -  это строковый тип данных, а строковые типы данных в формулах/функциях берутся в кавычки. В качестве диапазона суммирования указаны ячейки параллельные ячейкам указанным в диапазоне - от B6 до I6.

    Таким образом, можно сказать, что если буква П будет найдена среди ячеек от B3 до I3, то параллельные ячейки в дипазоне от B6 до I6 должны будут суммироваться.

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

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

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

    Для столбца Кол-во часов в будни дни все было сделано аналогично, как и для столбца Кол-во часов в праздничные дни. Разница только в том, что в качестве критерия стоит не буква П, а пустое значение в кавычках  - "". Так как будние дни никакими буквами не обозначаются.

    Будние дни можно было бы обозначать буквой Б - тогда в качестве критерия нужно было бы указать букву Б в кавычках - "Б".

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.