Клавиатурные комбинации

РАЗДЕЛ 2. работА в MS Excel

Содержание раздела

2.1 Введение в Excel 3

2.2 Клавиатурные комбинации. 4

2.3 Сортировки. 7

Простая сортировка таблицы по данным одного столбца. 8

Сортировка по формату. 11

Сортировка по нескольким столбцам.. 13

Сортировка части диапазона. 14

Сортировка строк. 14

Сортировка по значению.. 15

Сортировка по настраиваемым спискам.. 16

Сортировка по части значения в столбце. 19

Сортировка текста и чисел одновременно. 19

2.4 Фильтры.. 22

Отбор данных. 22

Установка фильтра. 22

Работа с фильтром.. 24

Отбор по значению.. 25

Отбор по формату. 26

Отказ от отбора. 28

Отбор по условию для числовых значений. 28

Отбор по условию для дат.. 30

Отбор по условию для текстовых значений. 31

Отбор наибольших и наименьших значений. 33

Отбор по ячейке. 35

2.5 Защита листа, книги, режимы доступа. 36

Уровни защиты.. 36

Пароли. 36

Защита от ввода некорректных данных в ячейку. 36

Защита файлов от открытия и изменения. 38

Открытие защищенных файлов. 39

Снятие пароля. 40

Защита книги от изменений. 40

Защита книги. 40

Снятие защиты книги. 41

Защита от несанкционированного просмотра и изменения листов. 41

Защита элементов листа от просмотра. 42

Защита элементов листа от изменений. 44

Выборочная защита диапазонов для разных пользователей. 44

2.6 ДИАГРАММЫ EXCEL. 45

Создание диаграммы.. 45

Настройка и редактирование диаграмм.. 47

Изменение размеров диаграммы и ее элементов. 61

Оформление диаграммы.. 62

Создание диаграммы в виде графика. 75

Дополнительные возможности при работе с графиками. 77

Создание круговой диаграммы.. 80

Диаграмма "План-Факт". 81

Диаграмма с заливкой зоны между линиями плана и факта. 83

Линии проекции в диаграмме. 85

2.7 Формулы в Excel 89

Структура формулы.. 89

Операторы.. 89

Создание и редактирование формул. 90

Редактирование формул. 97

Статистические вычисления. 99

Работа с базами данных. 107

Функции даты и времени. 111

Использование логических функций. 115

Функции просмотра и ссылок. 117

Использование ссылок в формулах. 119

Использование имен ячеек и диапазонов. 123

Проверка ошибок. 126

Организация вычислений. 131

2.8 ТЕХНОЛОГИИ ФИНАНСОВЫХ РАСЧЕТОВ В MS EXCEL. 134

1. Расчет будущих стоимостей инвестиций. 134

2. Вычисление ренты. Расчетов сроков вклада (займа). 139

3. Составление планов погашения займа. 142

2.9 ФОРМИРОВАНИЕ СВОДНОЙ ИНФОРМАЦИИ. КОНСОЛИДАЦИЯ ДАННЫХ И СВОДНЫЕ ТАБЛИЦЫ 146

Сводные таблицы.. 148

Консолидация данных. 154

Умные таблицы.. 159

2.10 Импорт данных в MS Excel из внешних источников. 166

Импорт данных из текстового файла. 166

Импорт данных из MS Access. 171

Импорт данных из Веба. 171

2.11 Информационные технологии обработки данных при решении экономических задач. 175

Заполнение бланков таблиц. 175

Составление смет и расчет стоимости заказов. 178

Организация учета данных о клиентах в торговой фирме. 185

2.12 Решение оптимизационных задач с помощью MS Excel 192

Оптимизация процесса перевозок. 192

Задача о назначениях. 194

Постановка и решение задачи линейного программирования в MS Excel 198

Введение в Excel

Любой вид деятельности требует систематизации хранимых данных. С расширением перечня продуктов, услуг и клиентов любому бизнесу необходима компьютерная система для хранения большого объема финансовой и другой информации. Microsoft Excel - программа, предназначенная для организации данных в таблицы для документирования, сопоставления и графического представления информации. Например, можно использовать Excel для суммирования, вычисления среднего или максимального числа продаж за день; создания графика, показывающего определенный процент продаж, сравнения общего объема продаж за день с тем же показателем других дней недели. Excel освобождает от проведения этих вычислений вручную.

Microsoft Excel - это идеальное средство для решения учетных задач, обработки экспериментальных данных, составления отчетов и т.п.

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

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

Microsoft Excel имеет массу встроенных функций для математических, статистических, финансовых и других вычислений.

Одна из важнейших функций программы - возможность представления данных в виде разнообразных графиков, диаграмм и карт.

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

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

Главной составной частью документа Excel является поле, которое содержит определенную информацию. В Excel это поле называется ячейкой. Каждая ячейка находится на пересечении строки (горизонтальной последовательности ячеек) и столбца (вертикальной последовательности ячеек); строки обозначены числами, а столбцы - буквами. Номер строки и буква столбца, обозначающие определенную ячейку, называются ссылкой на ячейку. Например, ячейка в левом верхнем углу окна рабочей книги имеет ссылку А1. Рабочий лист состоит из набора строк и столбцов и представляет страницу в документе Excel. Рабочей книгой, в свою очередь, называются один или несколько рабочих листов.

Важным аспектом при работе с большим объемом данных является возможность находить в рабочем листе наиболее важную информацию, например, о десяти самых удачных днях продаж в месяце или о категориях плохо продаваемых товаров, цены на которые необходимо снизить. Microsoft Excel предоставляет мощные и гибкие средства, с помощью которых можно ограничить отображение данных в рабочем листе, чтобы документ не выглядел громоздко. Можно производить вычисления над конкретными данными, которые необходимо обработать. Можно вычислить процент продаж, осуществленных за 10 самых удачных дней торговли текущего месяца от общего числа продаж за месяц, или установить, в какой день месяца торговля шла хуже всего.

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

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

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

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

Клавиатурные комбинации

При работе с Excel зачастую бывает рационально использовать сочетания клавиш вместо мыши. Использование сочетаний клавиш облегчает и ускоряет работу с программой..Используя сочетания клавиш, можно открывать, закрывать документы и листы, перемещаться по документу, выполнять различные действия над ячейками, выполнять вычисления и т.д. (табл. 2.1)

Таблица 2.1. Клавиатурные комбинации

Рассмотрим простой пример по использованию клавиатурных комбинаций.

Пример 1. Главный элемент пользовательского интерфейса Microsoft Excel 2007 (и новее) представляет собой ленту, которая идет вдоль верхней части окна каждого приложения (рис.2.1).

Рис. 2.1 Лента в MS Excel

Для быстрого сворачивания и разворачивания ленты можно воспользоваться комбинацией клавиш Ctrl + F1.

Рис. 2.2 Окно со свернутой лентой

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

Сортировки

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

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

Сортировка – расположение данных на листе в определенном порядке.

Можно выполнять сортировку данных по тексту (от А к Я или от Я к А), числам (от наименьших к наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых к старым) в одном или нескольких столбцах. Можно также выполнять сортировку по настраиваемым спискам (таким как состоящий из элементов «Большой», «Средний» и «Маленький»») или по формату, включая цвет ячеек, цвет шрифта, а также по значкам.

Таким образом, существуют три типа сортировки:

1. в возрастающем порядке

2. в убывающем порядке

3. в пользовательском порядке

Если используется порядок сортировки по возрастанию, то

- Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

- При сортировке алфавитно-цифрового текста сравниваются значения по знакам слева направо.

- Логическое значение ЛОЖЬ ставится перед значением ИСТИНА.

- Все значения ошибки равны.

- Пустые значения всегда ставятся в конец.

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

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

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

Поскольку при сортировке Microsoft Excel автоматически определяет связанный диапазон данных, сортируемый диапазон не должен иметь пустых столбцов. Наличие пустых строк допускается, но не рекомендуется.

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

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

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

Для упорядочения и анализа данных в списке применяется команда Сортировка.

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

Как правило, список состоит из записей (строк) и полей (столбцов). Столбцы должны содержать однотипные данные. Список не должен содержать пустых строк или столбцов. Если в списке присутствуют заголовки, то они должны быть отформатированы другим образом, нежели остальные элементы списка.

Поиск по сайту: