Практическая работа №5 Тема: Табличный процессор Excel

Выполнена Практическая работа №5 Тема: Табличный процессор Excel

Данные для Нижегородской области

Задание 1. Подготовка таблицы

1.1 Откройте книгу Excel со своим вариантом. Скопируйте первый лист в новую книгу. Для этого нажмите правой клавишей мыши на ярлык первого листа, выберите ПЕРЕМЕСТИТЬ ИЛИ СКОПИРОВАТЬ.

В открывшемся окне выберите переместить в НОВУЮ КНИГУ и отметьте галочкой СОЗДАТЬ КОПИЮ. 

1.2 Переименуйте лист в новой книге в ЗАДАНИЕ 1

 1.3 Подготовка таблицы к автоматизированному анализу

Уберите все гиперссылки в таблице. Выделите весь лист (CTRL+A), вызовите контекстное меню, далее «удалить гиперссылки».

Лист с таблицей не должен содержать объединение ячеек.

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

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

Заголовки столбцов должны быть в одной строке.

Задание 2. Расчет показателей

Дополните таблицу, рассчитайте показатели:

1)   Средняя выручка,

2)   Возраст компании,

3)   Год регистрации

4)   Рост выручки год к году и за 4 года,

5)   Выручку на одного сотрудника в 2024г

2.1 Расчет средней выручки за пять лет

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

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

Вставьте функцию =СРЗНАЧ(G5:K5)/$L$2, где

2.2 Расчет возраста компании

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

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

В ячейке M5 вставьте функцию =ОКРУГЛ((СЕГОДНЯ()-D5)/365;0), где D2= первое значение столбца «Дата регистрации».

В следующем столбце «Год регистрации» используйте функцию =ГОД(D5).

2.3 Расчет прироста выручки

В столбцах N, O, P, Q, R вставить название «Прирост за период»

В пятой строке вести функцию для расчета прироста ЕСЛИ(ЕОШИБКА(H5/G5);"";H5/G5-1). Мы используем логическую функцию ЕОШИБКА потому, что существуют компании в которых не было выручки в предыдущий год и возникает ошибка «деление на ноль». Функция ЕОШИБКА оставляет поле прироста пустым и не выводит сообщение об ошибке.

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

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

2.5 Установка формата ячеек

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

Относительные значения в формате процентов (доля, прирост)

Задание 3. Составить таблицу

3.1 Скопировать лист «Задание 1» в эту же книгу и переименовать его в «Задание 3 решение».

3.2 Установить числовой фильтр по столбцу «средняя выручка, млн. руб.». используя функцию «ПЕРВЫЕ 10…».

3.3 Скопировать столбцы A, B, F, N на новый лист.

Переименовать новый лист в «Задание 3 ответ».

3.4 Выполнить сортировку данных

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

3.5 Настроить формат таблицы

Указать название таблицы.

Установить границы.

Настроить ширину столбцов и высоту строк.

Установить подходящий формат для числовых данных.

Задание 4. Топ 30 компаний по росту выручки (2020-2024)

Скопировать лист «Задание 1» в эту же книгу, переименовать его «Задание 4 решение».

Установить фильтр на каждый столбец с показателем роста выручки за год все, кроме «Пусто». С «Пусто» снять галочку. Настроить фильтр по столбцу РОСТ ВЫРУЧКИ ЗА ЧЕТЫРЕ ГОДА на отображение 30 наибольших элемента списка.

Скопировать столбцы A, B, F, N, U (прирост выручки за 4 года) на новый лист.

 Переименовать новый лист в «Задание 4 ответ».

Установить сортировку по столбцу РОСТ ВЫРУЧКИ по убыванию.

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

Задание 5. Составить таблицу по самым старым компаниям (ТОП 15).

Скопировать лист «Задание 1» в эту же книгу, переименовать его «Задание 5 решение».

Настроить фильтр по столбцу ВОЗРАСТ КОМПАНИИ на отображение 15 наибольших элемента списка.

Скопировать столбцы A, B, F, N, P на новый лист.

 Переименовать новый лист в «Задание 5 ответ».

Установить двухуровневую сортировку как указано на рисунке.

Настроить формат таблицы как указано в 2.5

Задание 6. Топ 10 по выручке в 2024 году (Группа УП считает топ 10 по показателю Выручка на одного сотрудника)

Скопировать лист «Задание 1» в эту же книгу, переименовать его «Задание 5 решение».

Установить фильтр на столбец ВЫРУЧКА ЗА 2024 «ПЕРВЫЕ 10 …».

Скопировать столбцы A, B, F, M, N на новый лист.

 Переименовать новый лист в «Задание 6 ответ».

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

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

Установить сортировку по столбцу РОСТ ВЫРУЧКИ по убыванию.

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