Функции для работы с датами.
Функция СЕГОДНЯ( ) – отображает сегодняшнее число в формате дата. Если на ячейку, в которую введена функция СЕГОДНЯ( ) наложить формат «Общий», мы увидим количество дней, прошедших от начала ХХ века, т.е. с 1900 до сегодняшнего дня.
Функция относится к категории «дата и время». Эта функция не имеет аргументов.
Работа со списками в Excel. Понятие списка в Excel.
Списком в Excel называют однотабличную базу данных.
Столбцы – это поля.
Строки – записи.
Список должен удовлетворять следующим условиям.
§ на рабочем листе должен находиться только список и не должно быть других данных. В крайнем случае, список должен быть ограничен пустыми строками и столбцами, чтобы можно было его выделять как текущую область и отделить от других данных;
§ столбцы списка должны содержать однородную информацию, т.е. либо только числовую, либо только текстовую, либо даты;
§ столбцы списка должны иметь различающиеся текстовые заголовки (имена полей).
Работа со списками предполагает сортировку, поиск информации в соответствии с критериями, перегруппировку, обработку информации.
Сортировка данныхпозволяет сортировать ячейки по возрастанию и убыванию.
Для сортировки необходимо поставить курсор в базу данных и выбрать пункт меню Данные–Сортировка.
Сортировка может производиться по одному, двум, трем ключам по возрастанию и убыванию.
Эти параметры задаются в диалоговом окне Сортировка.
Пример. Имеются данные в 3-х столбцах А, В, С. Зададим сортировку данных по 3-м ключам, по убыванию.
Сначала Excel расставит строки по убыванию (если содержит текст, то по алфавиту от Я до А) данных в столбце А, а внутри групп с одинаковыми значениями А – по убыванию данных в столбце В, внутри групп с одинаковыми значениями В – по убыванию данных в столбце С.
| А | В | С |
| Канал | Товар | Кол-во |
| Рынок | Ручка | |
| Рынок | Ручка | |
| Рынок | Альбом | |
| Рынок | Альбом | |
| Магазин | Тетрадь | |
| Магазин | Тетрадь | |
| Магазин | Ручка |
Автофильтр.
Отфильтровать список – показать только те записи, которые удовлетворяют заданному критерию, т.е. возможность видеть не всю таблицу, а только ту часть её, которая нам нужна в данный момент.
Произведем отбор по одному полю. Покажем товары и их количество, проданные на рынке.
Для выборки сделать следующее:
- поставить курсор в базу данных;
- выбрать пункт меню Данные–Фильтр–Автофильтр, после чего в каждой ячейке верхней строки появится по кнопке со стрелкой, обозначающей наличие какого-то списка.
Щелкнем по стрелочке в столбце Канал. Появится список всех введенных нами каналов (рынок, магазины). Выберем строку Рынок (это будет наш критерий отбора). Excel уберет все лишнее и покажет нам таблицу в измененном виде, где будут показаны только товары, продаваемые на рынке.
Полученную таблицу можно напечатать. Отфильтрованные строки можно выделить цветом или иным образом отформатировать. Отключив потом режим фильтрации (Данные–Фильтр–Автофильтр), мы получим наглядную разметку таблицы.
Отбор по нескольким полям
Усложним задачу: выведем количество ручек проданных с рынка. Для этого выберем соответствующие элементы в выпадающих списках: в столбце Канал–Рынок, в столбце Товар – ручка. В результате получим только строки, содержащие информацию о ручках, проданных с рынка.
С помощью Автофильтра можно решать и более сложные задачи.
Для каждого столбца можно создать критерий, состоящий из одного или двух условий, соединенных логическими операторами И, ИЛИ.
Например, сделать выборку товаров, проданных из магазина количеством от 200 до 800 шт.
Курсор в базу данных. Данные–Фильтр–Автофильтр. Щелкаем по стрелке в ячейке Количество. В выпадающем меню выбираем Условие, после чего появится диалоговое окно, в котором можно задать условие.
Заносятся границы интервала и выбирается логический оператор И (для интервала, чтобы одновременно удовлетворить обоим условиям).
Отмена режима фильтрации: Данные–Фильтр–Автофильтр. Появится исходная таблица, исчезнут кнопки со стрелками в заголовках.
Расширенный фильтр.
В большинстве практических задач достаточно возможностей Автофильтра. Но профессиональный пользователь должен владеть и более богатыми возможностями, которыми обладает расширенный фильтр.
Расширенный фильтр позволяет:
- Сразу копировать отфильтрованные запасы в другое место рабочего листа, на котором находится исходный список.
- Сохранять критерий отбора для дальнейшего использования (это нужно, когда список изменяется, а нужно периодически извлекать из него информацию в соответствии с критерием).
- Показывать в отфильтрованных записях не все столбцы, а только указанные.
- Объединять операторами И, ИЛИ условия для разных столбцов.
- Для одного столбца объединять операторами И, ИЛИ боле двух условий.
- Создавать вычисляемые критерии.
- Выводить только уникальные значения.
Рассмотрим работу расширенного фильтра на примерах.
| А | В | С | D | E | F |
| Канал | Товар | Кол-во | Цена | Выручка | Дата |
| Магазин | Тетрадь | ||||
| Магазин | Ручка | 1,5 | |||
| Рынок | Тетрадь | 2,5 | |||
| Рынок | Ручка | 1,8 | |||
| Рынок | Альбом | ||||
| Магазин | Тетрадь | 2,3 | |||
| Магазин | Ручка | 1,7 | |||
| Магазин | Альбом | 6,5 | |||
| Рынок | Тетрадь | 2,8 |
Применив расширенный фильтр, сделать выборку тетрадей, проданных с рынка по цене от 2 до 2,9 руб.
Для выборки сделать следующее:
1.Создать таблицу критериев в строке от базы (сбоку, пропустив одну пустую строку) или внизу базы (также пропустив одну пустую строку), если список не будет добавляться другими данными.
Создадим таблицу критериев в ячейках А12: D13.
Для этого в ячейки А12: D12 введем имена полей, по которым необходимо сделать выборку. Лучше нужные поля скопировать из заголовка базы данных во избежание ошибок. «Канал» в ячейку А12; «Товар» в ячейку В12; «Цена» в ячейки С12 и D12.
| A | B | C | D | |
| Канал | Товар | Цена | Цена | |
| Рынок | Тетрадь | >=2 | <=2,9 |
В ячейки А13: D13 ввести условия выборки, т.е. в ячейку А13 скопировать слово «рынок» из списка, в ячейку В13 «тетрадь». В ячейкиС13 и D13 вводим условия С13: >=2; D13: <=2,9
2.Выбрать в меню пункт Данные–Фильтр–Расширенный фильтр.
Появится диалоговое окно.
В окне щелкнуть по «Скопировать результат в другое место», указать исходный диапазон базы данных, диапазон критериев и ячейку, с которой будет начинаться диапазон для результата выборки.
3.Далее щелкнуть ОК. В результате в ячейках, начиная с А20 вправо и вниз, расположится таблица, в которой будут выведены тетради, проданные на рынке по цене от 2 до 2,9 р.
| A | B | C | D | E | F | |
| Канал | Товар | Кол-во | Цена | Выручка | Дата | |
| Рынок | Тетрадь | 2,5 | ||||
| Рынок | Тетрадь | 2,8 |
Условие, записанное в одну строку, соответствует логической функции «И».
Рассмотрим пример с использованием функции «ИЛИ».
Сделать выборку тетрадей и ручек, проданных на рынке.
Составляем таблицу критериев:
| Канал | Товар |
| Рынок | Тетрадь |
| Рынок | ручка |
Условие критерия может быть задано формулой. Дана база данных.
| A | B | C | D | E | |
| Канал | Товар | Кол-во | Цена | Дата | |
| Магазин | Тетрадь | 19.03.04 | |||
| Магазин | Ручка | 1,5 | 20.03.04 | ||
| Рынок | Тетрадь | 2,5 | 18.03.04 | ||
| Рынок | Ручка | 1,8 | 26.03.04 | ||
| Рынок | Альбом | 25.03.04 |
Выбрать товар, проданный на рынке с выручкой более 3500.
Составляем таблицу критериев.
| Канал | Сумма |
| Рынок | =B2*C6>3500 |
Работа с датами с использованием расширенного фильтра.
Пример: выбрать тетради, поступившие неделю назад на сумму меньше 7000 руб.
Таблица критериев:
| Товар | Дата | |
| тетрадь | =С2*D2<7000 | =СЕГОДНЯ()-7 |
Выделить проданный товар с 20.03.04 по 26.03.04
| Дата | Дата |
| >20.03.04 | <25.03.04 |
Выделить проданный товар, кроме промежутка с 20.03.03 по 26.03.04
| Дата | Дата |
| >20.03.04 | <25.03.04 |
Выделить количество товара, поступившего в течение последней недели.
=Е2>СЕГОДНЯ()-7
Сводные таблицы – это средство обработки данных, с помощью которого можно одновременно подвести итоги, отсортировать список, произвести фильтрацию. Сводные таблицы позволяют представлять одни и те же данные по-разному.
Построим таблицу следующего вида:
| Канал | |
| Товар | Данные кол-во |
В меню Данные выбираем команду Сводная таблица. Откроется диалоговое окно Мастера сводных таблиц, т.к. нужно построить сводную таблицу на основе списка (базы данных) рабочего листа MS Excel, то на 1-м шаге Мастера сводных таблиц оставляем опцию «В списке или базе данных MS Excel». Далее.
В поле Диапазон следующего диалогового окна уже должен быть введен адрес списка (проверяем). Далее.
Далее на следующем шаге появится диалоговое окно построения сводной таблицы
Для получения таблицы, нужно кнопку «Товар» перетащить в область «Строка», кнопку «Канал» в область «Данные».
В этом случае для каждого вида товара будет подсчитано его количество в магазине и на рынке. Получится таблица следующего вида:
Далее появится диалоговое окно, позволяющее определить расположение сводной таблицы. Если поле «Поместить таблицу в» оставить пустым, то таблица будет помещена на отдельном листе.
Готово.
Можно быстро изменить данные сводной таблицы с помощью панели инструментов «Запрос и сводная таблица» или щелкнув правой кнопкой мыши по таблице.
Поиск оптимальных решений.
С давних времен, с тех пор, как человеку для обеспечения жизни понадобилась целая уйма вещей, которых в готовом виде в природе нет; одежда, посуда, оружие, лодки, дома и т.д. возник вопрос каких размеров, формы делать вещи, из какого материала.
Например, прежде чем приступить к изготовлению самого простого кувшина, необходимо определить, какой формы должен быть кувшин, чтобы при использовании имеющегося количества глины его объем был максимальным.
Поскольку глина, как и любое другое сырье, имеет определенную стоимость, то этот вопрос может быть сформулирован несколько иначе: какой формы должен быть кувшин, чтобы при заданной стоимости его объем был максимальным.
Или та же задача, но в несколько другой постановке: какой формы должен быть кувшин заданного объема, чтобы его стоимость была минимальной.
Пройдут годы, века, тысячелетия, а такая постановка задачи сохранится независимо от того, что будут проектировать, потому что при проектировании объективно существует одна из двух постановок задачи. Либо спроектировать изделие заданной стоимости с наилучшими свойствами, либо спроектировать изделие с заданными свойствами, но наименьшей стоимости.
Какая-либо другая постановка задачи проектирования просто невозможна.
Наилучшее решение этих задач может быть получено лишь с помощью методов оптимального проектирования.
Виды задач.
А тем временем на смену индивидуальному приходит массовое производство, и возникают новые вопросы, например, у управляющего производством – кого на какую работу назначить, т.е. приходится решать задачу, которая будет называться задачей распределения ресурсов, а также задача распределения ресурсов во времени.
Если она будет решена неправильно, то мясо появится задолго до костра, а когда придут на обед, он уже давно остынет и костер погаснет. Чтобы избежать этого, ресурсы во времени должны распределяться оптимально.
Кроме того, возникают задачи, которые вообще не имеют решения. Например, как из комка глины, помещающегося на ладони, изготовить кувшин, чтобы в нем хранилась вода, которой хватило бы для семьи на целый месяц или как одним хлебом накормить все человечество.
Такие задачи, но не в такой очевидной своей невозможность решения постановке, встречаются в жизни довольно часто. Им дали специальное название: несовместные задачи.
Большая беда таких несовместных задач заключается в том, что нереальность их выполнения очевидна далеко не всегда. Получив задачу, начинают месить глину, чтобы сделать такой кувшин, на который имеющейся глины не хватит.
И это вместо того, чтобы сначала рассчитать, а потом начать работать.
Любое решение, а особенно оптимальное принять очень трудно, не выбрав критерия.
Перед тем, как принять решение, надо знать, что мы хотим. Если критерий не принят, то и оптимального решения быть не может.
И еще, оказывается, что при решении почти каждого вопроса необходимо знать ответы на дополнительные бесчисленные вопросы, которые можно объединить в 2 группы:
- что будет, если….
- что надо, чтобы…
В это время вопросы анализа, без ответа на которые обоснованное решение принять невозможно. Основные вопросы, требующие решения: если, еще и не всегда сформулированы, то объективно уже существуют:
- проектирование изделия: либо заданной стоимости с наилучшими свойствами, либо с заданными свойствами наименьшей стоимости;
- задачи распределения ресурсов вообще и задачи распределения ресурсов во времени;
- выбор критерия;
- анализ принимаемого решения.