Практическая работа в Excel из 6 заданий с разбором

Практическая работа в MS Excel

Начало работы

1) Открываем MS Excel.

2) Создаем 5 листов. Для каждого задания свой лист, кроме 3 и 4 – они объединены.

3) Во всех заданиях для подсчета должны использоваться функции или вычислительные действия, вписанные в ячейки.

4) Файл называем: ФИ курс(класс) дата.

Задание №1

Рассчитать сколько планировала заработать на сольном концерте некая начинающая музыкальная группа, если в ВКонтакте положительно ответили 462 человека. Учитывая, что для каждого 100го человека цена билета понижалась на 15% (ПР: для 99 и 101 она была обычной). Начальная - 750 рублей.

Решение:

Для начала во вкладке “Задание 1” создадим таблицу на 463 строки и 2 столбца. Для этого выделяем все ячейки столбцов А1 и В1 до строчки №463 включительно. Для выделенного диапазона выбираем понравившийся стиль в отделе “Форматировать как таблицу” в верхней панели программы.

Ставим галочку на “Таблица с заголовками”, чтобы программа обозначила строкой с заголовками первую строчку выбранного нами диапазона, а не вы добавила еще одну строку.

По желанию можно очертить каждую клетку (знак клетки под настройками шрифта), убираем фильтр.

В А2 вписываем единицу и используем автозаполнение (черный плюс в правой нижнем углу выделенной клетки) и кликаем на знак , выбираем «Заполнить».

Для ячейки В2 оставляем общий формат и вставляем функцию “=ЕСЛИ(ОСТАТ(A2;100)=0;637,5;750)”, протягиваем вниз. Данная формула выявляет все №, делящиеся на 100 и для них выводит акционную стоимость билета, для остальных - остается начальная цена в 750 рублей. Мы не заключаем числа в кавычки, тогда они читаются именно как цифровое значение, если мы хотим что-то вписать, тогда ставим кавычки такого вида “”.

Например для клиента под номером « 100» (номера клиентов выделены голубым, а номера строк черным) цена балета будет 637,5.

…....

Далее для сложения выбираем функцию СУММ(В2:В463),и складываем все показатели от В2 до В463 в ячейке D2, С2 подписываем как “Итого:”.

Ответ: 346050 рублей.

Задание №2

Рассчитать, сколько людей итого пришли на концерт, если собирались приходить 462 человека, и, дополнительно, каждый 4 человек привел друга.

Решение:

1) Копируем таблицу с первой страницы на вторую.

2) К ней добавляем еще один столбец С, который называем “+1”. Чтобы отобразилось именно +1 необходимо выбрать текстовый формат.

В первой строке А1-С1 текст выровнять посередине, настройка находится справа от строки выбора шрифта.

3) Для выявления каждого 4-го человека в ячейку С2 вставляем формулу “=ЕСЛИ(ОСТАТ(A2;4)=0;1;"")”, тогда функция будет выявлять тех, кто взял с собой друга и ставить там единицу.

В ячейке С464 с помощью функции “=СУММЕСЛИ(C2:C463;1)” (либо СЧЕТЕСЛИ()) находим сколько человек, помимо 462 пришли.

4) В ячейке E2 складываем значения ячеек А463 и С464 - узнаем, сколько всего человек посетили концерт.

Ответ: 577.

Задание №3

Рассчитать, сколько денег в юанях всего заработает музыкальная группа на продвижение сольного альбома, используя данные по ценам на билеты из первого задания и фактическое число посетителей. Учитываем, что каждый “пришедший друг” из задания №2 заплатил в 2 раза больше, и мерчандайзеры продали 253 футболки по 700 рублей, 68 бейсболок по 400 рублей, 400 браслетов по 300 рублей, 124 значка по 150 рублей.

Решение:

1) На странице “Задание №3” создаем таблицу на 6 столбцов и 4 строки. Выбираем формат и выставляем “таблица с заголовками”, как в первом примере. Для строк с денежными значениями выбираем финансовый формат

Заполняем согласно образцу:

В столбце математическим действием, без применения формул вычисляем общую выручку за билеты в ячейке «Прибыль в рублях» - «Билеты» “=346050+115*750*2”. Также заполняем по остальным категориям. Первым всегда ставится знак “=” - так система понимает, что это вычисления.

2) Теперь переведем в юани, рассчитывая, что 1 CYN = 11,6 RU. Для перевода в другую валюту нужно делить на количество рублей, приходящихся к денежной единице той валюты, в которую переводим, а если наоборот, то сумму в другой валюте умножать на курс.

Для этого в ячейку В3 вставляем формулу “=B$2/11,6”.

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

Например, при протяжке формулы B$2/11,6 мы получаем:

С3=C$2/11,6

D3=D$2/11,6

E3=E$2/11,6

F3=F$2/11,6

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

3) Результат СУММирования денег в юанях записываем около “Итого” в ячейке В4.

Ответ: ¥ 74262,93.

Задание №4

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

Решение:

1) Там же, во вкладке задание 3 будем находить минимальное и максимально значение в сербских динарах. 1 рубль=1,3 динара. Для строки 4 и 5 выбираем RSD.

2) Для поиска минимального и максимального значений в диапазоне выбираем функции МИН(диапазон) и МАКС(диапазон), которые указываем в В5 и В6 соответственно.

3) Составляем диаграмму по категориям и прибыли в рублях. Заходим во “Вставку”, выделяем нужные ячейки, выбираем понравившуюся диаграмму. Подпись данных настраиваем через конструктор:

Итого, после выполнения 3 и 4 задания получаем:

Дополнительное задание: Задание №5

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

Входные данные ниже:

Решение:

1) Составляем 2 таблицы и заполняем их как на изображениях. По каждой из таблиц найдем среднее значение с помощью функции СРЗНАЧ(диапазон) и запишем в А13 и Е8 соответственно. После вычисления в рублях переведем в USD – значение записываем в В13.

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

2) Через окно “вставка” создаем диаграмму в виде графика, на которой будут отражены сборы гастролеров согласно дате и городу. Окно для вывода по условию должно находиться рядом справа и начинаться “Вывод: ...”, его вписываем вручную, излагая кратно свои мысли. После оформления получается страница вида:

Дополнительное задание: Задание №6

Создать таблицу, скопировать данные Fan-базы, исходя из количества человек, ранжировать города по степени I, II, III, какие города скорее всего будут включены в тур. Например: в городе N всего 5 слушателей, по сравнению с городами R и B, где по 200 и 700 соответственно, в нем мало слушателей. После проведения ранжирования, Нужно рассортировать строки: сначала I, потом II, потом III.

Решение:

1) Для заполнения столбца «Степень», необходимо использовать функцию ЕСЛИ(), то есть начала формулы будет: =ЕСЛИ($B$2:$B$23III";(ЕСЛИ($B$2........)))), где всего 3 ЕСЛИ.

2) Для сортировки необходимо выделить диапазон и в настраиваемой сортировке выбрать

Справа налево: степень, значения, настраеваемый список, в нем внести знаки I, II, III. И получаем:

Сохраняйте файл после каждого сделанного задания! Берегите нервы)