3.08 Прочие базы данных

Так как функция ВПР использует в качестве критерия только один столбец, а в этой задаче необходимо сопоставлять два столбца, то создадим конкатенацию ячеек одной строки. Для этого в таблице Курсы валют в первом столбце (вместо ID) запишем формулу =B2&C2 и растянем ее на все строки таблицы. Таким образом мы получили для каждой строки уникальный ключ по которому и будем совершать поиск.

Теперь запишем формулу, которая будет определять курс конкретной валюты в конкретный день. На листе Поступления в ячейке F2 запишем формулу, а затем растянем на все строки таблицы:

Эта функция создает конкатенацию из ячеек, в которых содержится ID валюты и дата поступления, таким образом получая искомый ключ. Следующим шагом необходимо вычислить сумму поступления в рублях, для этого умножим курс валюты на сумму поступления. В ячейку G2 записываем формулу =F2*E2, растягиваем.

Фильтруем дату с по декабря, копируем всю получивушуюся таблицу и переносим на новый лист, настраиваемой сортировкой сортируем столбец со счетами, а затем столбец с датой. В ячейку H2 копируем численное значение ячейки G2, а в ячейку H3 вставляем формулу =ЕСЛИ(B3=B2; G3+H2; G3).

Растягиваем.

Получили движение средств по каждому счету. В ячейку I2 запишем формулу =ЕСЛИ(B2=B3; 0; H2), тогда мы получим , где движение по одному счету и другое число, которое будет финальным по текущему счету.

Растягиваем.

Найдем итоговую сумму по всем счетам — она равна , а далее поделим на кол-во счетов — на , и получим ответ: .

ID сырной пиццы равно 13, а стоит она 635 рублей. Сначала необходимо перенести в таблицу Заказы город клиента и код блюда, а так же количество этого блюда. В ячейке F2 пишем следюущую формулу и растягиваем на весь столбец:

Так теперь каждому заказу соотвентсвует город клиента.

В ячейке G2 пишем следюущую формулу и растягиваем на весь столбец:

Так мы знаем какое блюдо было в каждом заказе.

В ячейке Н2 пишем следюущую формулу и растягиваем на весь столбец:

Так мы знаем сколько блюд было в заказе.

Теперь нужно отфильровать три столбца: Фильтруем столбец Е и выбираем «Выполнено». Фильтруем столбец F и выбираем «г. Москва». Фильтруем столбец G и выбираем «13».

Теперь считаем итоговую стоимость. Выделяем получившийся столбец D и смотрим на сумму в правом рнижнем углу. Получем, что за доставку сырной пиццы клиенты Москвы заплатили 2237 рублей. Далее суммируем столбец H и видим, что всего было заказано 24 сырной пиццы: рублей. Итого: рублей.

Откроем файл электронной таблицы и для начала выгрузим в лист «Заказы» при помощи функции ВПР узнаем код блюда из заказа. Для этого запишем формулу:

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

Опираясь на код блюда, которого заказали выгрузим в лист «Заказы» цену данного блюда. Запишем формулу:

Опираясь на код заказа определим сколько блюд было заказано в данном заказе. Формула будет такой:

Осталось определить стоимость заказа. Запишем формулу: =H2*I2+D2. Выделим все столбцы листа «Заказы» и применим к ним «Фильтр». Нас интересуют заказы, где пицца начиналась на букву «С». Такие пиццы имеют следующие коды блюд: 12,13,14. В качестве фильтра для столбца код блюд в листе «Заказы» укажем только эти значения. Нам нужны только заказы, у которых была также доставка.

В столбце E в фильтре поставим галочку только напротив пункта «Выполнено». Клиенты должны быть из г. Москва. В фильтре столбца G уберем галочки с иных городов. В фильтре столбца B указываем, что заказ должен быть совершен в июле.

Осталось получить итоговую сумму заказов. Для этого выделим столбец с итоговой стоимостью каждого заказа и внизу получим значение. Ответ: 30076.

Ставим фильтр на два столбца: Тип операции и Фамилия водителя. В "Типе операции"выбираем только "привоз а в "Фамилии водителя"оставляем галочку только на Мохове и Сидорове. Таким образом, осталось только посчитать объем груза, привезенным Моховым и Сидоровым. Это можно сделать, выделив столбец "объем груза"и посмотрев на правый нижний угол, там будет указана сумма - 1376836. В ответ нужно указать целую часть от деления суммы на 2024: , в ответ запишем — 680.

Ставим фильтр на три столбца: "Город отправления "Город прибытия"и "Номер борта".

При фильтрации столбца "Город отправления"оставляем галочку только на Краснодаре, при фильтрации столбца "Город прибытия"только на Казани и Рязани. Столбец «Номер борта» предварительно сортируем от наименьшего к наибольшему, затем выделяем номера бортов, которые меньше 170, их получается 17.

Таким образом, нам подходит только 17 рейсов.

ID пиццы песто равно 11, а стоит она 530 рублей.

Сначала необходимо перенести в таблицу Заказы город клиента и код блюда, а так же количество этого блюда.

В ячейке F2 пишем следюущую формулу и растягиваем на весь столбец: =ВПР(C2;Клиенты!A$2:E$1995;5;0). Так теперь каждому заказу соотвентсвует город клиента.

В ячейке G2 пишем следующую формулу и растягиваем на весь столбец: =ВПР(A2;’Состав заказов’!B$2:D$1995;2;0). Так мы знаем какое блюдо было в каждом заказе.

В ячейке Н2 пишем следующую формулу и растягиваем на весь столбец: =ВПР(A2;’Состав заказов’!B$2:D$1995;3;0). Так мы знаем сколько блюд было в заказе.

Теперь нужно отфильтровать три столбца:

1) Фильтруем столбец Е и выбираем «Выполнено»

2) Фильтруем столбец F и выбираем «г. Москва»

3) Фильтруем столбец G и выбираем «11».

Теперь считаем итоговую стоимость. Выделяем получившийся столбец D и смотрим на сумму в правом нижнем углу. Получаем, что за доставку пиццы Песто клиенты Москвы заплатили 4393 рублей. Далее суммируем столбец H и видим, что всего было заказано 43 пиццы Песто: рублей. Итого: 22790+4393 = 27183 рублей.

Открываем таблицу «Клиенты» и находим, что нужный нам человек имеет ID 28. Возвращаемся в таблицу «Операции» и фильтруем столбец C, оставляя только клиета с ID 28. Также укажем в фильтрах тип операции «Продажа».

В ячейку H779 впишем формулу: . Таким образом мы получили стоимость купленных товаров. В ячейку J779 впишем формулу: , так мы нашли стоимость всей покупки.

Получаем, что самая дорогая покупка равна 41209 и совершалась в магазине М7. Значит, ответ 7.

Переходим в таблицу «Операции», в столбец H перенесем данные о покупателе из таблицы «Клиенты». Для этого запишем формулу: в ячейку H2. Растянем её вниз до конца таблицы.

В столбец I перенесем данные о магазине из таблицы «Магазины». Для этого запишем формулу: в ячейку I2. Растянем её вниз до конца таблицы.

В столбец J перенесем данные о стоимости товаров из таблицы «Товары». Для этого запишем формулу: в ячейку J2. Растянем её вниз до конца таблицы.

В столбце K будем искать общую стоимость покупки. Для этого в ячейку K2 запишем формулу и растянем её вниз до конца таблицы.

Теперь осталось проверить выполнение условия задачи. Для этого воспользуемся фильтром. Выделяем всю таблицу «Операции» и в разделе «Редактирование» выбираем «Сортировка и фильтр». Фильтруем только что заполненные стобцы «Покупатель» и «Магазин».

В столбце «Покупатель» оставляем только значения клиентов с фамилией Тихомиров, в столбце «Магазин» – значение Ленинского района.

Выделяем столбец К и получаем, что сумма покупок составила 431324. Возвратов не было, поэтому это и есть прибыль магазину.

Переходим в таблицу «Операции», в столбец H перенесем данные о покупателе из таблицы «Клиенты». Для этого запишем формулу: в ячейку H2. Растянем её вниз до конца таблицы.

Теперь осталось проверить выполнение условия задачи. Для этого воспользуемся фильтром. Выделяем всю таблицу «Операции» и в разделе «Редактирование» выбираем «Сортировка и фильтр». Фильтруем только что заполненные стобцы «Покупатель».

В столбце «Покупатель» оставляем только значения клиентов с фамилией Беляев.

Также отфильтруем тип продажи и оставим только значения «Возврат» и «Продажа». Отсортируем столбец с типом продажи от А до Я. Заметим, что в некоторых магазинах покупатели делали возврат, с помощью фильтра удалим эти магазины.

Теперь для каждого из оставшихся магазинов нужно посчитать количесвто строк (т.е. количество посещений). Для этого скопируем полученную таблицу на новый лист и в ячейку J2 запишем формулу: =СЧЁТЕСЛИ(D:D;D2). Результаты отфильтруем, оставив только максимальное значение. Получаем, что покупки 5 раз совершались в магазинах М1, М11, М6. В остальных магазинах количество покупок меньше.

Адреса этих магазинов: просп. Мира, 45; Луговая, 21; просп. Мира, 10.

Получаем ответ: 102145

Открываем файл и создаем три новых столбца: «Город», «Код блюда» и «Количество». Для этого в ячейку F2 вставляем формулу: =ВПР(C2;Клиенты!A:E;5;0), в ячейку G2: =ВПР(A2;’Состав заказов’!A:C;2;0), а в ячейку H2: =ВПР(A2;’Состав заказов’!A:C;3;0) и растягиваем их вниз до конца таблицы.

Дополнительно добавим столбец с ценой на пиццу, в ячейку I2 вставим формулу: =ВПР(G2;Меню!A:C;3;0) и растянем вниз.

Остается только воспользоваться фильтром. Фильтруем столбец «Дата», оставляя только август. Столбец город – только Казань. Столбец код блюда – 16 (код пиццы «Флорида»)

За весь август было куплено 3 нужных нам пиццы по 535 рублей плюс стоимость доставки – 311 рублей. Значит, ответ 1916.

Открываем файл и создаем новый столбец: «Город». Для этого в ячейку F2 вставляем формулу: =ВПР(C2;Клиенты!A:E;5;0) и растягиваем их вниз до конца таблицы.

Теперь осталось проверить выполнение условия задачи. Для этого воспользуемся фильтром. Отфильтруем столбец с датой, оставив только значения 23, 24, 25, 26 и 27 июля. Также отфильтруем статус доставки на «Не выполнено» и город на «Москва».

Посчитаем количество строк, их оказалось 5.

Открываем файл и создаем три новых столбца: «Отчество», «Стоимость» и «Количество». Для этого в ячейку F2 вставляем формулу: =ВПР(C1066;Клиенты!A:E;4;0), в ячейку G2: =ВПР(ВПР(A1066;’Состав заказов’!A:C;2;0);Меню!A:C;3;0) (перед этим удалив столбец Код в таблице Состав заказов), а в ячейку H2: =ВПР(A1066;’Состав заказов’!A:C;3;0) и растягиваем их вниз до конца таблицы.

Остается только воспользоваться фильтром. Фильтруем столбец «Дата», оставляя только 1, 2 и 3 июня. Столбец отчество – только Ильич. Столбец стоимость – значения от 550 до 600 включительно.

Получаем, что найдена одна подходящая строка. Итого был сделан заказ стоимостью 590р*7 и доставка стоимостью 770р. Ответ

Открываем файл и создаем два новых столбца: «Стоимость», «Категория» и «Город». Для этого в ячейку G2 вставляем формулу: «=ВПР(C245;Номера!A:E;4;0)», в ячейку H2 – формулу: =ВПР(C2;Номера!A:E;5;0), в ячейку I2: =ВПР(B2;Клиенты!A:E;5;0) и растягиваем их вниз до конца таблицы.

Теперь осталось проверить выполнение условия задачи. Для этого воспользуемся фильтром. Фильтруем столбец с категорией, оставляя только значение «3» и столбец с городами, оставляя города на букву И.

В итоге получаем одну строку со стоимостью номера 4936. C помощью формулы найдём количество дней проживания, оно равно 41. Значит, ответ .

Откроем таблицу «Фильмы» и создадим три новых столбца: «Режиссёр» и «Жанр», а также столбец «Разница», где будем считать прибыль либо убыток фильма.

В столбце «Режиссёры» в ячейку I2 запишем формулу: и растянем вниз до конца таблицы. Таким образом, мы перенесли информацию о стране происхождения из таблицы «Режиссёры».

В столбце «Жанр» в ячейку J2 запишем формулу: и растянем вниз до конца таблицы. Таким образом, мы перенесли информацию о жанре фильма из таблицы «Жанры».

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

Теперь воспользуемся фильтром в разделе «Редактирование»–«Фильтр» и отфильтруем полученную таблицу согласно условию задачи. В столбце «Жанр» выбираем только жанры «Драма» и «Мелодрама». В столбце «Режиссёр» – «СССР» и «Россия». В столбце «Год выхода» – 1974, 1979, 1983 и 1986 года.

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

Открываем файл и создаем два новых столбца: «Город», «Этаж»,«Стоимость». Для этого в ячейку G2 вставляем формулу:

=ВПР(B2;Клиенты!A:E;5;0),

в ячейку H2 – формулу:

=ВПР(C2;Номера!A:E;2;0),

в ячейку I2:

=ВПР(C2;Номера!A:E;4;0)

и растягиваем их вниз до конца таблицы.

В столбце J посчитаем количество дней проживания с помощью формулы: . В столбце К – общую стоимость проживания за все дни: . Растянем всё вниз.

Теперь осталось проверить выполнение условия задачи. Для этого воспользуемся фильтром. Фильтруем столбец с этажами, оставляя значение 14. В столбце с городами – значение г. Уфа.

Выделяем полученные значения в столбце К и получаем ответ: 634385.

Из исторической справки вспомним, что год распада СССР – 1991. Также создадим столбец E, куда перенесем название вида животного. В ячейку E2 вставим формулу:

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

Теперь воспользуемся фильтром и отфильтруем год поступления – 1991 и ID вольера – A9, C5 и B11. Получаем трех различных видов. Из них 11 особей бобра, 3 особи ласки и 3 особи газели. В ответ пойдет число 11.

Откроем файл Excel.

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

Теперь можем на лист «Поступления» при помощи ВПР перенести курс валюты для каждой конкретной даты. Запишем следующую формулу и растянем её вниз до конца таблицы:

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

А в ячейку запишем формулу для нахождения общей суммы поступления на данный счёт:

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

Посчитаем среднее арифметическое столбца и запишем целую часть в ответ.

Откроем файл Excel.

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

Теперь можем на лист «Поступления» при помощи ВПР перенести курс валюты для каждой конкретной даты. Запишем следующую формулу и растянем её вниз до конца таблицы:

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

А в ячейку запишем формулу для нахождения общей суммы поступления на данный счёт:

Теперь в любую ячейку запишем следующую формулу, целую часть от её результата и запишем в ответ:

Откроем файл Excel.

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

Теперь можем на лист «Поступления» при помощи ВПР перенести курс валюты для каждой конкретной даты. Запишем следующую формулу и растянем её вниз до конца таблицы:

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

А в ячейку запишем формулу для нахождения общей суммы поступления на данный счёт:

В ячейку запишем следующую формулу:

Теперь при помощи фильтров найдём суммарные поступления Тимофеева А. Д. и Захарова С. И.. Найдём их модуль разницы и запишем целую часть в ответ.

Откроем файл Excel.

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

Теперь можем на лист «Поступления» при помощи ВПР перенести курс валюты для каждой конкретной даты. Запишем следующую формулу и растянем её вниз до конца таблицы:

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

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

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

Найдем минимальное значение в полученной колонке с помощью формулы:

Её результат умножим на и целую часть запишем в ответ.