Меню
  
Смотрите также:



 Главная   »  
страница 1 страница 2 страница 3 страница 4 страница 5 страница 6

Требования к оформлению работы


п/п







1

Размер бумаги

и ориентация листа



А4. Книжная.

2

Объем документа

Не менее 5-х страниц на 3 теоретических вопроса

3

Поля

Верхнее и нижнее – 2 см, левое – 3 см, правое – 1,5 см.

4

Шрифт

Times New Roman – 13 пт.

5

Выравнивание

Основной текст – по ширине, заголовки – по центру.

6

Отступы и интервалы

Междустрочный – полуторный, первая строка на 1,25 см, остальные – 0 см.

7

Оглавление

В начале документа после темы, сформированное автоматически средствами Word.

7.1

Формат заголовков

Шрифт – Times New Roman, 14, полужирный.

ЗАГОЛОВОК 1: регистр – все прописные.

Заголовок 2: регистр – как в предложениях.

8

Колонтитулы

Верхний – «Информационные технологии», выравнивание по правому краю,

9

Нумерация страниц

Внизу страницы, выравнивание по центру. Размер шрифта – 10 пт.

9.1

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

Третье задание


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

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


Правила создания списков в MS Excel


Чтобы достичь максимальной эффективности при дальнейшей работе со списком, желательно следовать некоторым простым правилам:

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

  • Лучше делить информацию на как можно большее количество столбцов (полей). Например, ФИО лучше делить на три отдельных столбца с фамилией, именем и отчеством, соответственно, чтобы впоследствии легко найти всех людей с нужным именем (а если завтра "Татьянин День"?)

  • Верхняя строка списка должна быть "шапкой", т.е. содержать заголовки столбцов. К сожалению, Excel в большинстве случаев не умеет корректно работать с многоуровневыми "шапками", поэтому красивую многострочную "шапку" с кучей объединенных ячеек лучше приберечь для отчета или презентации, а в реальном списке ограничиться однострочной без пустых и объединенных ячеек.

  • Список не должен содержать внутри пустых строк и столбцов - это может вызывать большое количество сложностей в будущем, поскольку  Excel считает пустые строки/столбцы окончанием текущего списка.

  • Не стоит размещать какие-либо другие данные слева или справа от списка - они могут быть скрыты во время фильтрации списка, например, с использованием Автофильтра.



Вариант № 1


Пусть имеется некоторая таблица отгрузки товара в период с 15.01.2009 по 05.03.2009 пяти организациям.


Наименование организации

Менеджер

ООО "Рога и копыта"

Чёрный С. И.

Бандитский Дом "Кидалово и Мочилово"

Проходимцев Г. И.

ЗАО "ККК" (Кто кого кинет)

Простой М. П.

ТОО "А нас уже нет"

Хитромудров Л. К.

ООО ТД "Книга" г.Москва

Климов П. Ю.


Чтобы быстрее заполнить столбец Получатель и Менеджер, после того как вы введёте по разу все названия организаций и соответствующего им менеджера, щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка или нажать сочетание клавиш Alt+стрелка вниз.


  1. В ячейке Е2 создать выпадающий список, состоящий из пяти менеджеров. Для этого:

  • Выделить список менеджеров (в нашем случае это ячейки Н7:Н11) и в меню ВставкаИмяПрисвоить присвоить имя Менеджер;

  • Находясь в ячейке Е2, Данные - Проверка



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

  • Выделить всю таблицу, кроме шапки и в меню Формат выбрать команду Условное форматирование


Т.о. должно получится примерно следующее:



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

  • В свободную ячейку ввести следующую формулу:

=СУММ((E6:E55=$E$2)*C6:C55) и нажать Ctrl + Shift + Enter.

Если всё выполнено верно, должны получиться следующие результаты:




Всего отгружено товара на сумму

Менеджер

44162,8

Чёрный С. И.

62366,06

Проходимцев Г. И.

39456,59

Простой М. П.

30492,39

Хитромудров Л. К.

46235,6

Климов П. Ю.


Вариант № 2


Задача (Тарифы за Интернет)
Каждый день я подключаюсь к сети 1 раз. Но никогда не работаю ночью с 23.00 вечера до 7.00 утра. В настоящее время у провайдера действуют следующие тарифы:


  • "Ночной тариф". 1 час работы в Интернет с 1.30 до 8.30 утра стоит 150 рублей;

  • "Дневной тариф". 1 час работы в Интернет с 15 до 19 часов в будние дни стоит 350 рублей;

  • "Обычный тариф". 1 час работы в Интернет в остальное время (будние дни - с 8.30 до 15 часов и с 19 до 1.30; выходные дни - с 8.30 утра до 1.30 ночи) стоит 250 рублей.

Входная информация таблицы следующая:




  1. Список интервалов времени, проведенных в Интернет. На каждый день недели – одна строчка.

  2. Список тарифов с указанием стоимости и границ времени.

Я уже начал составлять таблицу и прошу тебя сохранить неизменной ее основную часть – расчет времени по интервалам разных тарифов. Вот так это выглядит:



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


  1. Считать время, проведенное в сети, для каждого дня в часах и минутах (ячейки I6:I12)

  2. Считать время, проведенное в сети, для каждого времени в часах (ячейки Е16:Н22)

  3. Считать время, проведенное в сети, для каждого дня в часах (ячейки I16:I22)

  4. Считать время, проведенное в сети, за всю неделю (ячейка I23)

  5. Рассчитывать стоимость услуг по каждому дню (ячейки J16:J22)

  6. Рассчитывать сумму расходов за неделю (ячейка J23)

  7. Автоматически выделять галочкой (  )день с наибольшими расходами


Подсказки: Использовать следующие функции:

Час - Возвращает час, соответствующий заданному времени в числовом формате. Час определяется как целое в интервале от 0 до 23.

МИНУТЫ - Возвращает минуты, соответствующие аргументу время_в_числовом_формате. Минуты определяются как целое в интервале от 0 до 59.

СИМВОЛ - Возвращает знак с заданным кодом.

ЕСЛИ - Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Чтобы поставить галочку напротив дня с наибольшими расходами, вначале необходимо найти наибольшее расходы за день (ячейка В15), затем используя функции ЕСЛИ и СИМВОЛ в столбец D, начиная с ячейки D6 занести соответствующую формулу, ставящую галочку в ячейке. Галочке соответствует шрифт Wingdings с кодом 252. Поэтому после написания формулы в диапазоне ячеек с D6 по D12 выбрать шрифт Wingdings.



Если всё будет выполнено правильно, должна получиться следующая таблица.

Вариант № 3


Средствами EXCEL на Листе1 подготовьте бланк службы МЕТЕО;

Осень 2009

г.Барановичи

Месяц




Нб

Нм

Ср

Дней > Ср

Дней < Ср

1

2

3

4

5



29

30

31

Сентябрь








































выше средней








































Октябрь








































выше средней








































Ноябрь








































выше средней











































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

  • к столбцам "Ср", "Нб", "Нм" установите следующие примечания: "средняя температура месяца", "наибольшая температура месяца", минимальная температура месяца" соответственно;

Примечание: (подсказка: Вид – Примечания)

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

сентябрь (8  t  20); октябрь (0  t  10); ноябрь (-10  t  5)

(подсказка: функция СЛУЧМЕЖДУ возвращает случайное число между двумя заданными числами. При каждом вычислении рабочего листа возвращается новое случайное число.



Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку «Пакет анализа»)

  • средствами EXCEL вычислите среднемесячную, максимальную и минимальную температуры по месяцам;

  • используя соответствующую функцию строки "выше средней" заполните словами "да" или "нет" в зависимости от значения среднемесячной температуры и температуры дня;

  • подсчитайте количество дней, температура которых выше и ниже среднемесячной;

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

Подсказка: (Вставка – Объект – Создание из файла)

Пример справочного текста приведен в Приложении;

  1. Переименуйте Лист1 в "Отчет";

  • на Листе2 постройте график "Температура в осенние месяцы", характеризующий изменение температуры по дням для каждого месяца;

  1. Переименуйте Лист2 в "Температура";

  • на Листе3 постройте диаграммы, сравнивающие значения среднемесячной, максимальной и минимальной температуры по месяцам;

  1. Переименуйте Лист3 в "Диаграмма";

  • результаты работы сохраните в файле "Метеослужба".

Приложение

Региональная служба METEO отмечает, что прошедшая осень в регионе отличалась обилием осадков в виде града с куриное яйцо, которым убило стадо петухов и повредило пастуха Васю, мирно отдыхавшего на травке после вчерашнего напряженного трудового вечера, на котором отмечался праздник 75-летия колхоза "30 лет без урожая". Также был поврежден аппарат высшего класса 17 сорта "Воздух – земля - вечный покой". В результате повреждений, нанесенных градом аппарату, и, соответственно, морального вреда жителям колхоза, работа последнего прервана на неопределенное время, до полного разъяснения обстоятельств, восстановления материальных и духовных ценностей, а так же пастуха дяди Васи.

Вариант № 4


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


Создайте таблицу следующего вида:

Указание.

Сохранить её под именем Заказы.

Присвоить Листу 1 имя – Клиенты.



  1. Ввести следующие данные в таблицу, используя форму ввода данных:

Указание

Встать на ячейку А1.

В меню Данные выбрать команду Форма.

Появится окно формы Клиенты:



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



После ввода последней записи нажать кнопку Закрыть.

  1. Отформатируйте таблицу так, чтобы на экране была видна вся информация.

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

  3. Расположите данные в первой строке по центру столбцов и выделите их «полужирным» шрифтом.

  4. Установите для первой строки голубой фон заливки и синий цвет шрифта.

  5. Перейдите на второй лист книги Заказы.

  6. Присвойте листу имя Товары.



  1. Введите следующую информацию:

  2. Расположите данные в первой строке по центру столбцов и выделите их «полужирным» шрифтом.

  3. Установите для первой строки голубой фон заливки и синий цвет шрифта.

  4. Перейдите на третий лист книги Заказы.

  5. Присвойте листу имя Заказы.



  1. Введите заголовки столбцов в таблицу и отформатируйте их по образцу.

  2. Установите для первой строки голубой фон заливки и синий цвет шрифта.

  3. Присвойте столбцам А-G следующие имена:

    A

    Дата

    В

    Заказ

    С

    Номер2

    D

    Товар2

    E

    Количество

    F

    Цена2

    G

    Сумма

  4. Перейдите на лист Товары.

  5. Присвойте столбцам А-С следующие имена:

    А

    Номер

    В

    Товар

    С

    Цена

  6. Перейдите на лист Клиенты.

  7. Присвойте столбцу А имя Фирма.

  8. Перейдите на лист Заказы.

  9. Ввести в ячейку D2 формулу, обеспечивающую автоматическое заполнение поля Наименование товара при вводе его кода в поле Номер товара.

Указание

Используйте следующую формулу:

=ЕСЛИ($С2=””;””;ПРОСМОТР($С2;Номер;Товар))


  1. Ввести в ячейку F2 формулу, обеспечивающую автоматическое заполнение поля Цена при вводе его кода в поле Номер товара.

Указание.

Используйте следующую формулу:

=ЕСЛИ($С2=””;””;ПРОСМОТР($С2;Номер;Цена))


  1. Ввести в ячейку G2 формулу, обеспечивающую автоматическое заполнение поля Сумма при вводе количества в поле Количество.

Указание.

Используйте следующую формулу:

=ЕСЛИ($Е2=””;””;Е2*F2)


  1. Скопируйте формулы в ячейках D2, F2 и G2 вниз.



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

  2. Перейдите на следующий чистый лист и присвойте ему имя Таблица. На этом листе мы создадим сводную таблицу для анализа данных заказов.

  3. Создайте сводную таблицу, используя Мастер сводных таблиц.

Указание.

В меню Данные выбрать команду Сводная таблица.

1-й шаг. В диалоговом окне установите В списке или базе данных и нажмите кнопку Далее.

2-й шаг. Указать диапазон ячеек для анализа. Так как данные находятся на листе Заказы, то перейти на него и выделить ячейки с А1 по G11. Должно быть примерно следующее:



Нажать Далее.

3-й шаг. Создание структуры сводной таблицы. Перетащите кнопку Номер заказа в поле Столбец, кнопки Наименование товара и Количество в поле Строка, а кнопку Сумма в поле Данные.

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

4-й шаг. Выбор ячейки рабочего листа, откуда будет начинаться сводная таблица. По умолчанию устанавливается первая ячейка листа. Если это не устраивает, то щёлкните на листе ячейку, откуда должна начинаться таблица.

Нажать кнопку Готово.



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

Указание.

Скрыть первую строку, так как там нет никакой полезной информации, выделив её и выбрав из контекстного меню команду Скрыть.

Скрыть столбец с Общим итогом, выделив его и выбрав из контекстного меню команду Скрыть.

Скрыть строку с Общим итогом, выделив её и выбрав из контекстного меню команду Скрыть.

Скрыть все строки со словом Всего.

Должно получиться приблизительно следующее:





  1. Создать диаграмму реализации товаров по заказам.



Вариант № 5


  1. Создать таблицу по продажам следующего вида:



Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Решить данную задачу четырьмя способами, переименовав соответственно столбцы



СПОСОБ 1. Функция СУММЕСЛИ

Если бы в нашей задаче было только одно условие (все заказы Григорьева или все заказы в "Копейку"), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ из категории Математические. Но в нашем случае имеются два условия, а не одно, поэтому этот способ не подходит...!!!!!!



СПОСОБ 2. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:





СПОСОБ 3. Волшебная формула массива

В нашем случае задача решается одной формулой:

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.



СПОСОБ 4. Функция баз данных БДСУММ

В категории Базы данных можно найти функцию БДСУММ, которая также может помочь нам решить нашу задачу:



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



=БДСУММ(A1:D26;D1;F1:G2)



СПОСОБ 5. Мастер Частичной Суммы

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



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



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



Легко заметить, что нечто похожее на эту формулу массива мы использовали в Способе 3. Только здесь можно к клавиатуре вообще не прикасаться - да здравствует лень - двигатель прогресса!


Вариант № 6


  1. Задайте первым трем листам рабочей книги имена: Математические, Статистика+Логика, Текстовые.

  2. Перейдите на лист Математические и постройте таблицу, приведенную на рисунке 1.

Рисунок 1 – Лист Математические




  1. В ячейке А2 поставьте цифру 0, и заполните столбец до ячейки А30 с помощью арифметической прогрессии с шагом 0,5. Для этого поставьте курсор на ячейку А2. Выберите пункт меню Правка – Заполнить – Прогрессия. В появившемся окне поставьте флажок Расположение – по столбцам. Задайте шаг – 0,5, и предельное значение – 14.

  2. Рассчитайте функции, указанные в таблице для всего диапазона значений Х от 0 до 14,0, используя математические функции Excel (пункт меню Вставка – Функция, категория – Математические).

  3. На листе Статистические постройте таблицы, представленные на рисунке 2:

Рисунок 2 – Лист Статистика+Логика



  1. Столбец Баллы заполните с помощью функции случайных чисел (диапазон от 0 до 100).

(функция СЛУЧМЕЖДУ возвращает случайное число между двумя заданными числами. При каждом вычислении рабочего листа возвращается новое случайное число. Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку «Пакет анализа»)

  1. В столбце Зачет необходимо проставить зачет по тем дисциплинам, количество баллов по которым превышает 61 балл, используя функцию ЕСЛИ (Вставка – Функция – Логические, =ЕСЛИ(B2>=61;"Зачтено";"Не зачтено")).

  2. К столбцу Зачет применить условное форматирование. Если по дисциплине проставлен зачет, то шрифт ячейки должен стать Синим, Полужирным курсивом; если не проставлен зачет, то Красным, Полужирным курсивом. Для этого выделите столбец Зачет. Выберите пункт меню Формат – Условное форматирование. В графе Условие1 выберите значение – равно – зачтено, далее нажмите кнопку Формат и выберите необходимые критерии форматирования. Затем нажмите кнопку А также и введите необходимые параметры для условия – не зачтено, и нажмите кнопку ОК.

  3. Произведите расчет среднего, максимального и минимального балла по дисциплине, а также рассчитайте количество дисциплин, используя функции СРЗНАЧ, МИН, МАХ, СЧЕТ (Вставка – Функция, категория – Статистические).

  4. На листе Текстовые постройте таблицу, приведенную на рисунке 3:

Рисунок 3 – Лист Текстовые




  1. Заполните столбцы Фамилия, Имя, Отчество произвольными данными (5 человек). Фамилия, Имя, Отчество должны начинаться с большой буквы.

  2. Вставьте в соответствующие столбцы следующие функции (Вставка – Функция, Категория – Текстовые): ЛЕВСИМВ для столбца Имя, ЛЕВСИМВ для столбца Отчество, СЦЕПИТЬ(B2;" ";E2;".";" ";F2;"."), ДЛСТР (для столбца Фамилия), ПРОПИСН(для столбца Фамилия), СТРОЧН(для столбца Имя).


Вариант № 7


Вас пригласили на должность директора кинотеатра «Кристалл-Палас». Для того чтобы узнать сколько Вам будут платить, а заодно проверить свои деловые способности, произведите необходимые расчёты, используя следующие исходные данные.

В кинотеатре три зала: «Красный», «Зелёный», «Синий». Залы одинаковые и представляют собой квадраты 1111 кресел. В результате маркетинговых исследований были установлены показатели по заполнению зала в среднем:




8

9

7

9

9

0

8

9

7

2

5

1

5

8

9

3

9

1

1

0

7

0

5

8

8

4

0

4

6

0

0

4

0

4

1

2

3

7

3

1

7

9

7

3

4

8

9

8

3

6

5

0

3

7

3

6

8

1

6

8

1

4

3

5

5

7

3

1

0

7

5

2

6

3

9

6

5

1

1

6

7

9

6

3

1

4

3

3

8

6

3

6

9

3

1

2

5

3

6

9

6

5

9

5

2

2

0

1

2

1

7

3

7

1

2

6

9

2

5

7

3

Рис. Рассадка зала
Здесь цифры 1 и 2 обозначают мужчин, 3 и 4 - женщин, 5 и 6 – детей, 7 и 8 тех, кто имеет право на льготные билеты, 0 – пустые места и 9 – знакомые и родственники служащих кинотеатра, которые ходят в кино бесплатно. К исходным данным также относится курс доллара = 2165 руб. Цены на билеты в зависимости от времени сеанса и принадлежности к той или иной категории зрителей приведены в таблице 1, а затраты от общей прибыли – в таблице 2.
Таблица 1. Цены билетов в кинотеатре «Кристалл-Палас»

Категории зрителей

Цены билетов (руб.)

Утро (1 сеанс)

День (3 сеанса)

Вечер (2 сеанса)

Мужчины

Женщины


Дети

Льготники



5500

4000


2500

800


12000

8000


4000

1500


25000

16000


8000

4000



Таблица 2. Затраты из общей прибыли за месяц

Затраты

%

Затраты

%

Амортизация

Налоги


Зарплата сотрудникам

12

22

16



Охранные структуры

Новы фильмы

Зарплата директору


25

22

3



Необходимо вычислить прибыль кинотеатра за день, месяц (считая в месяце 30 дней), расходы по статьям в рублях и долларах. Вывести эти данные на экран, выделив зарплату директора.

Вариант № 8


Создать следующую таблицу:

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


Эта задача решается с помощью одной формулы(!):

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

Т.е. все что надо сделать - это ввести названия товаров в ячейках E3:E8 и названия заказчиков в ячейки F2:H2, а потом ввести в ячейку F3 формулу с нашей функцией:

=СУММПРОИЗВ(($A$2:$A$20=$E3)*($B$2:$B$20=F$2)*($C$2:$C$20))

Говоря простым языком, эта функция суммирует значения стоимости из диапазона $C$2:$C$20, если значения диапазона $A$2:$A$20 (наименования) равны $E3 (грейпфрут), а значения диапазона $B$2:$B$20 (заказчики) равны F$2 (Ланит). Все, что осталось сделать - это скопировать эту формулу на все ячейки отчета (F2:H8).

Другими словами синтаксис этой функции можно представить так:

=СУММПРОИЗВ((условие1)*(условие2)*(что_суммировать))

Причем, никто не мешает Вам добавить третье, четвертое и т.д. условия, расширяя свой отчет.


В исходную таблицу добавить столбец с номером склада, с которого происходила отгрузка товара, то можно отразить это в отчете, добавив третье условие в функцию СУММПРОИЗВ

Если всё сделать правильно, должно получиться следующее




Вариант № 9


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

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

В столбце G создать два выпадающих списка: 1) список для выбора заказчика и 2) список товаров.

Создать первый выпадающий список для выбора Заказчика



  • Выделите G4 и откройте меню Данные - Проверка, затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник - выделите ячейки J2:J12. После нажатия на ОК первый выпадающий список готов.

  • Второй выпадающий список делается аналогично.

В ячейку G6 вводим формулу =СУММ((C3:C21=G4)*(B3:B21=G5)*D3:D21) и жмём Ctrl + Shift + Enter.

Вариант № 10


Создать два выпадающих списка: 1) список для выбора марки автомобиля и 2) второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки, т.е. если выбрать марку автомашины из первого выпадающего списка, то содержимое второго автоматически изменится, отображая модели выбранной марки.

  1. Создать список моделей автомобилей Toyota, Ford и Nissan:



  1. Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota в меню Вставка - Имя - Присвоить. Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.

  2. Создать первый выпадающий список для выбора марки автомобиля

Выделите пустую ячейку и откройте меню Данные - Проверка, затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник - выделите ячейки с названиями марок (серые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов:



  1. Создать второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Точно так же, как в предыдущем случае, выделите пустую ячейку и откройте меню Данные - Проверка - далее Список. В поле Источник нужно будет ввести вот такую формулу:

=ДВССЫЛ($F$3)

где F3 - адрес ячейки с первым выпадающим списком - замените на свой.

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



страница 1 страница 2 страница 3 страница 4 страница 5 страница 6
скачать файл

Смотрите также: