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

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

Рис. 3. 30. Функция СУММ

- Перетащите маркер ячейки (рис. 3.11) В8 в ячейку С8 (рис.3.31). Должно получиться число 2533. Формула изменит вид с = СУММ(В2:В7) на = СУММ(С2:С7). В этом случае будет задействован принцип " относительности " ссылок.

Рис. 3. 31. Создание формулы с помощью механизма " относительности " ссылок

В ячейке В9 подсчитайте максимальное значение фактических товарооборотов с помощью функции МАКС(В2:В7).

- Сделайте активной ячейку В9. Щелкните по списку кнопки Сумма командной вкладки Главная группы команд Редактирование (рис. 3.32). Выберите команду Максимум. Однако результат ее действия вас удивит (рис. 3.33.а).

Рис. 3. 32. Вызов функции из списка кнопкиСумма

а б

Рис. 3. 33. Результат действия функции МАКС

Вместо требуемого вам интервала В2:В7 у вас на экране отразится выделение блока ячеек В2:В8.

- Подведите курсор к правому нижнему маркеру, чтобы он принял вид разнонаправленной стрелки (рис. 3.33. б) и переместите его из ячейки В8 в В7. Завершите ввод формулы. В результате должно получиться число 465.

Ввод аналогичной формулы для ячейки С9 выполним по - другому.

- Сделайте активной ячейку С9. Можете щелкнуть по списку кнопки Сумма командной вкладки Главная группы команд Редактирование (рис. 3.32) и выбрать там опцию Другие функции или щелкните на кнопку командной строки Вставить функцию . В обоих случаях перед вами откроется окно Мастер функций.

- В окне Категории (рис. 3.34) выберите Статистические, а в окне Выберите функцию – МАКС.

- В окне Число 1 (рис.3.35) показано выделение области С2:С8. Но вы уже знаете, что это неправильно, поэтому нажмите на кнопку Перехода в правой части окна и самостоятельно выделите необходимый диапазон ячеек С2:С7 или напишите его сами в английской раскладке клавиатуры.

- Вновь щелкните по кнопке Перехода для возврата во второе окно мастера функций.

Рис. 3. 34. Окно Мастер функций

- Нажмите клавишу Enter или щелкните по кнопке ОК.

В ячейке появится значение 541.

Рис. 3. 35. Диалоговое окно функции расчета максимального значения Макс

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

- Процент выполнения плана вычисляется делением значений ячеек столбца Товарооборот факт на Товарооборот план. Установите курсор в ячейке D2. Нажмите клавишу "=". В ячейке и в Строке формул появится знак "=". Щелкните левой кнопкой мыши в ячейке С2. На цифровой клавиатуре нажмите кнопку "/". Затем щелкните в ячейке В2. В результате будет написана следующая формула =С2/В2. Закончите ввод формулы (п.3.6.1). Результат будет иметь следующий вид - 1,439252.

- Заполните ячейки D3:D8 формулой из ячейки D2 с помощью Маркера заполнения. Установите курсор на маркере заполнения (рис. 3.11) и, не отпуская левой клавиши мыши, переместите маркер из ячейки D2 до ячейки D8. При перемещении маркера вниз будут увеличиваться номера строк в формуле.

- Эти же формулы можно написать по-другому. Выделите блок ячеек D2:D8. В ячейку D2 введите формулу =С2/В2. Нажмите сочетание клавиш Ctrl + Enter.Во всех ячейках выделенного диапазона появятся нужные формулы.

- И совсем легкий способ тиражирования формул. Дважды щелкните по маркеру заполнения ячейки D2. Произойдет заполнение формулой ячейки в интервале ячеек D3:D9. Очистите лишние ячейки. В данном случае – это D9.

Во всех трех случаях результат вычислений может принять следующий вид (рис. 3.36).

Рис. 3. 36. Вычисление % выполнения плана (без форматирования)

G Примечание. Обратите внимание, что процент выполнения плана товарооборота считается по формуле, аналогичной для каждой группы товаров. Суммирование процентов приведет к смешному результату. Сравните, в первом случае имеем 133,73 %, а во втором - 683,77 %.

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

- В ячейку Е2 введите следующую формулу =С2/С8.

- Любым способом заполните ячейки Е3:Е8 формулой из ячейки Е2. При перемещении вниз будут увеличиваться номера строк в формуле, что приведет к появлению сообщения #ДЕЛ/0! (рис. 3.37.а). Действительно, при перемещении маркера заполнения вниз увеличивается адрес ячейки, а он во всех формулах должен быть С8. Решить эту проблему можно тремя путями:

а б

Рис. 3. 37. Вычисление доли по формуле без фиксации итогового товарооборота

- Сделайте активной ячейку Е2. Дважды щелкните в этой ячейке или переместите курсор в строку формул. Курсор установите после текста С8 (в ячейке или строке формул) и нажмите клавишу F4 столько раз, пока вместо текста С8 не появится $С$8. Адрес ячейки с суммарным товарооборотом стал абсолютным и при дальнейшем заполнении интервала Е3:Е8 не будет изменяться (рис. 3.37.б).

- Можете сразу написать правильную формулу. Выделите блок ячеек Е2:Е8. В ячейку Е2 введите формулу = С2/$С$8. Нажмите сочетание клавишCtrl + Enter. Во всех ячейках выделенного диапазона появятся нужные формулы. А можно просто ввести правильную формулу = C2/$С$8.

- Выделите ячейки D2:D8. Установите в них процентный формат, нажав на кнопку Процентный формат командной вкладки Главная, группы команд Число (рис. 3.38)

- Увеличьте точность полученного значения до двух знаков после запятой. Для этого достаточно несколько раз щелкнуть по кнопкам Увеличить разрядность или Уменьшить разрядность группы команд Число.

Рис. 3. 38. Группа команд Число

- Выделите ячейки Е2:Е8. В группе команд Число щелкните на стрелке в правом нижнем углу. Откроется диалоговое окно Формат ячеек предыдущих версий Excel. На вкладке Выравнивание установите параметры, как на рис. 3.39.

Рис. 3. 39. Установка параметров чисел в диалоговом окне Формат ячеек

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

Рис. 3. 40. Результат форматирования ячеек

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

- Установите курсор в ячейке F2.

- Щелкните по кнопке командной строки Вставить функцию. В категории Логические выберите функцию Если (рис.3.41.).

- В окне Лог_выражение введите условия выполнения плана свыше 100 %. Для этого щелкните в ячейке D2 (% выполнения плана), перейдите на английскую раскладку клавиатуры, Комбинацией клавиш Shift + Ю введите знак "больше", затем напишите 100 %. Если, вместо % вы напишете просто число 100, то оно будет умножено на 100. Поэтому, вместо 100 % можно написать 1. Признаком правильного написания формулы является появление справа от окна текста =ИСТИНА.

- В окне Значение_если_истина напишите формулу, по которой 20 % от перевыполнения плана товарооборота пойдет на премирование сотрудников =20%*(С2-В2). Признаком правильного написания формулы является появление справа от окна текста = 28,2.

- В окне Значение_если_ложь, напечатайте какое–либо сочувственное выражение, например "Увы!".

- Если все выполнено верно, то в диалоговом окне Аргументы функции в двух местах появится значение 28,2.

- Нажмите кнопку ОК. В ячейке появится найденное значение.

Рис. 3. 41. Диалоговое окно функции Если

- Щелкните правой кнопкой мыши в ячейку F2. В появившемся контекстном меню выберите команду Копировать. Вокруг ячейки появится движущееся обрамление. Содержимое ячейки будет скопировано в буфер обмена.

- Выделите ячейки F3:F7. Щелкните правой кнопкой мыши в любом месте выбранного диапазона. В появившемся контекстном меню выберите команду Вставить. Во всех ячейках диапазона появились значения.

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

- В ячейке F8 подсчитайте суммарное значение начисленной премии по всей фирме, как =СУMM(F2:F7).

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

Рис. 3. 42. Копирование ячейки таблицы

В ячейках появятся следующие значения (рис. 3.43). Видно, что формат чисел не тот, что вам нужен.

Рис. 3. 43. Фрагмент расчетной таблицы без форматирования ячеек

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

- Выделите интервал ячеек D8:F8. Щелкните левой кнопкой мыши на кнопке Формат по образцу. Тем самым вы задали образец формата.

- Выделите интервал ячеек D9:F9. Формат ячеек будет преобразован по выделенному образцу (рис. 3.44.)

Рис. 3. 44. Фрагмент расчетной таблицы после форматирования ячеек с помощью кнопки Формат по образцу

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

Вычисления закончены. Приступайте к оформлению таблицы.