4 техники анализа данных в Microsoft Excel
Если вам по работе или учёбе приходится погружаться в океан цифр и искать в них подтверждение своих гипотез, вам определённо пригодятся эти техники работы в Microsoft Excel. Как их применять — показываем с помощью гифок.
1. Сводные таблицы
Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.
В Telegram-канале «Лайфхакер» только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь!
Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.
Как работать
Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.
- Откройте файл с таблицей, данные которой надо проанализировать.
- Выделите диапазон данных для анализа.
- Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
- Должно появиться диалоговое окно «Создание сводной таблицы».
- Настройте отображение данных, которые есть у вас в таблице.
Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.
Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.
Можно её детализировать, например, по странам. Переносим «Страны».
Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.
2. 3D-карты
Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.
Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.
Как работать
- Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
- Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
- Выделите диапазон данных для анализа.
- На вкладке «Вставка» есть кнопка 3D-карта.
Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.
Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.
3. Лист прогнозов
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза — наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).
Как работать
- Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
- Выделите два ряда данных.
- На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
- В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
- Выберите дату окончания прогноза.
В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).
Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.
На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.
4. Быстрый анализ
Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
Как работать
- Откройте таблицу с данными для анализа.
- Выделите нужный для анализа диапазон.
- При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Источник
Как в excel обработать большой объем данных без макросов. Пошаговые инструкции. Часть 1
Excel открывает большие возможности в обработке массива цифр и строк. Сегодня мы разберем, как в excel обработать большой объем данных. В этой части мы не будем разбирать макросы. Цель этой статьи — научиться работать с самыми доступными и простыми формулами excel, которые помогут выполнить нашу работу в большинстве случаев.
Статья будет разделена на 2 части. Содержание первой части, представлена ниже. Начнем без теории. Вряд ли она вам интересна.
- Как в excel найти повторяющееся значение
- Как в excel быстро удалить дублирующиеся строки
- Работа со сводной таблицей в excel
- Как в excel «подтянуть» данные из другого листа или файла
- Что такое функции правсимв и левсимв и как их применять
Как в excel найти повторяющееся значение
В своде данных мы можем столкнуться с проблемой, когда нам нужно из большого количества строк быстро найти повторяющиеся строки. Ведь в одной строке может быть одно значение, а во второй, по такому же наименованию товара, дубль или другое значение.
Возьмем таблицу. В столбец Е ставим равно и затем, в поиске «Другие функции» ищем нужную нам формулу (см. рис 1)
Для поиска повторяющегося значения, в данном случае, в коде товара по столбцу А, мы будем пользоваться простой формулой = СЧЕТЕСЛИ
Выделяем весь столбец «А», и в диапазоне аргументов функций ( маленькое голубое окошко посреди экрана), у нас появляется А:А, то есть весь выделенный диапазон по этому столбцу. см. рис 3.
Переходим в окно «критерий», и выделяем только первую строку по коду товара. У нас она отразится, как А2. см. рис. 3.
Далее, нажимаем «ок», и в столбце «Е» появляется цифра 1. Это значит, что по товару 100101200 Молоко Вологодское 1% жирности, только один такой товар, нет дублей. См. рис 5.
«Протягиваем» значения по столбцу «Е» вниз, и мы получаем результат, а именно, какие товары у нас имеют дубль в нашем списке, см рис 6. У нас проявилось 2 одинаковых товара, (их excel обозначил цифрой 2), которые, для наглядности вручную выделил желтым.
Если бы у нас было три одинаковых товара в списке, то excel, соответственно, проставил цифру 3. И так далее. Уже через простой фильтр, можно выделить, все, что больше 1 и увидеть полную картину.
Как в excel удалить дублирующиеся строки
По сути, метод, указанный выше, уже выполняет наш запрос. Однако, если Вам не нужны данные с повторяющихся строк, а требуется просто их удалить, тогда есть наиболее простой способ быстрого удаления дублей.
Мы воспользуемся функцией, которая уже встроена в панель excel. См. на панели закладку » ДАННЫЕ». Наша функция так и называется «Удалить дубликаты».
Мы выделяем область поиска, у нас это вновь столбец А. См рис 7.
(В более поздней версии excel, можно все находить через поисковое окно.)
Далее нам просто нужно подтвердить удаление. Однако, для наглядности, выделил зеленым те задвоенные строки, которые у нас есть. Это строка 7 и 21. См рис 8.
Теперь на панели жмем кнопку «удалить дубликаты». У нас появляется окошко. Здесь нам автоматически предлагает удалить всю горизонтальную строку, то есть «автоматически расширить выделенный диапазон». Жмем на кнопку «удалить дубликаты». См рис 9
После этого мы видим, что указаны столбцы отмеченные галочками, которые будут удалены по дублирующейся горизонтальной строке. См. рис 10. Мы жмем «ок».
Все. Теперь мы видим окно с оповещением, что дубль в количестве 1 строки был удален. Теперь, на месте 21-ой строки по товару-дублю, появился следующий товар из нижнего списка. См. рис 11.
Исходя из описания, может показаться, что по времени занимает не меньше, чем в первом варианте, но на самом деле это не так. Я просто эту функцию расписал очень подробно.
Как в excel обработать большой объем данных, сводная таблица
Сводная таблица служит для объединения разрозненной информации воедино. Сегодня мы также научимся это делать. Здесь нет ничего сложного. К примеру нам требуется, сколько же у нас есть одного и того же товара, не по брендам, а по виду товара.
Смотрим нашу таблицу. В панели инструментов ищем закладку «ВСТАВКА». Под панелью инструментов, в верхнем левом углу, появляется иконка, которая так и называется «Сводная таблица». см. рис 12. (Или ищем ее в поиске новой версии excel)
Мы выделяем все столбцы или столбцы интересующих нас значений.
Затем нажимаем на иконку «сводная таблица». У нас выходит окошко, в котором выделен диапазон столбцов. По умолчанию, excel предлагает сводную таблицу вынести на новый лист. см. рис 13. Мы так и делаем.
Подтверждаем команду нажав кнопку «ок». Получаем на новом листе нашей страницы excel возможность построения сводной таблицы, см рис 14.
Теперь мы выбираем нужные нам значения из правого верхнего участка. Раз мы договорились, что нам нужно знать сколько у нас товара по одному наименованию, то выбираем галкой наименование товара. См. рис 15.
По аналогии, мы ставим галку напротив количества (остатки в шт, склад 1).
При этом, перемещаем данные с количеством не в окно «название строк», а в окно «Значения». см. рис 16
Здесь мы видим, что у нас появился дополнительный столбец, но пока не по количеству штук каждого товара, а по количеству строк. Далее мы делаем следующую операцию.
Правой клавишей мыши нажимаем на столбец с количеством. См. рис 17. У нас открывается окно, где в строке ИТОГИ ПО, мы ставим галку не по количеству (строк), как на картинке, а по сумме.
Теперь мы получаем именно сведенное количество по каждому товару. См рис 18.
Для сравнения и наглядности, возвращаемся в исходный лист, (см. рис 19) и мы видим:
одинаковые товары по наименованию, помеченные синим цветом 3+3 = 6 штук.
одинаковые товары, помеченные зеленым 5+56 = 61 штука.
Тоже самое у нас в сводной таблице ( рис 18), 6 и 61 штука.
В сводную таблицу можно добавить поставщика и так далее. Можно ее сделать более сложной в плане количества учитываемый столбцов. Это уже дело необходимости и практики. Один-два раза сделаете, поймете суть. Потом, навык, как в excel обработать большой объем данных на уровне сводной таблицы, уже никогда не забудете.
Как в excel подтянуть данные из одного диапазона в другой, с помощью функции ВПР
Будет логичным, если сразу же покажу, как в excel «подтянуть» данные из другого листа или файла, в другой. Для этого есть замечательная функция ВПР. Мы разберем, как пользоваться этим на уже знакомых нам данных.
К примеру, Вам нужно свести цифры воедино с другого магазина, склада заявки на один лист Excel. Это делается по ключевому значению, которое должно быть во всех источниках данных. Это может быть уникальный код товара или его наименование.
Сразу оговорюсь по наименованию или текстовому значению, функция ВПР бескомпромиссна.
Если в наименовании товара есть пробел или точка, (любое отклонение) то для нее это будет уже другое значение.
Также необходимо, что бы все источники были в одном формате. Если мы говорим о числах, то в числовом формате.
Итак, у нас есть исходный файл, на листе 1, (см. рис 20)
Из листа 2, (рис 21) мы будем подтягивать цифры в лист 1. Обратите внимание, что количества на листах разное. Строки также могут быть смещены в списке или перемешаны, поэтому, простым сложением одной цифры с другой нам не обойтись.
Для нас данные на листе 1 те, к которым нужно подтянуть другие значения. Также действуем через знак равно «=». В левом верхнем углу, через поиск других функций, находим ВПР, см рис 22.
Затем, у нас открывается окно и мы выделяем весь столбец А, то есть искомое значение. Оно в новом окне выделяется, как А:А, см рис 23.
Далее, мышкой переходим в самом окошке на вторую строку «таблица», только после этого переходим на лист 2 нашего файла.
И от столбца «А» выделяем и протягиваем к столбцу с количеством. В данном случае, к столбцу «D», см рис 25.
Столбец D, это четвертый столбец начиная с искомого значения, то есть с кода товара в столбце А.
Поэтому, мы ставим в третьем поле окошка «номер столбца» цифру 4. и в поле «интервальный просмотр» всего ноль. В итоге у нас получается заполненное окошко, см рис 26.
Нажимаем «ок», и получаем подтянутую цифру со второго листа, по коду товара 100101200. см. рис 27.
Протягиваем значение вниз, столбец D заполняется цифрами с листа 2. см. рис 28. Здесь нам остается просто сложить одни цифры с другими простой формулой сложения и протянуть вниз.
Таким образом, мы можем подтянуть значение из большого массива данных, которое вручную искать долго и не целесообразно, если есть функция ВПР.
Важный момент. Если Вы подтягиваете из другого файла, то файлы должны быть сохранены. И еще. Формулы подтянутых значений остаются. Вам нужна цифры привести в значения или не удалять и не менять значения, которые Вы подтягивали.
Как в excel обработать большой объем данных, функция правсимв и левсимв
Бывает, что необходимо для работы с функцией ВПР, привести искомые значения, и значение которые мы подтягиваем в единую форму. Как мы говорили выше, для ВПР любое отклонение, даже пробел, это уже другое значение.
Для этого, нам в помощь функция excel: правсимв и левсимв. То есть с помощью этой функции можно слева или справа нашего значения, например наименования товара, убрать лишние знаки.
Итак,, нам нужно взять только часть от полного наименования. Смотрим наш рис 29, к примеру, нам нужно только слово «молоко». Мы также в окне поиска формул ищем = левсимв.
Источник
Как упростить работу с цифрами: 5 инструментов Excel
Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных.
Любой сотрудник компании, работающий в отделе продаж, финансов, маркетинга, логистики, сталкивается с необходимостью работать с данными, анализировать их.
Excel — незаменимый помощник для достижения этих целей. Мы импортируем информацию, "подтягиваем" ее, систематизируем. На ее основе строим диаграммы, сводные таблицы, планируем, прогнозируем.
Однако в Excel до недавнего времени было 2 важных ограничения:
Мы не могли разместить на рабочем листе Excel более миллиона строк (а наши данные о продажах за 2 года занимают, например, 10 млн строк).
Мы знали, как создать и настроить интерактивные и обновляемые отчеты, но это отнимало много времени.
Единственный инструмент в Excel — сводные таблицы — позволял быстро обрабатывать наши данные.
С другой стороны, есть категория пользователей, которые работают со сложными BI-системами. Это системы бизнес-аналитики (business intelligence), которые дают возможность быстро визуализировать, "крутить" данные и извлекать из них ценную информацию (data mining). Однако внедрение и поддержка таких систем требует значительного участия IT-специалистов и больших финансовых вложений.
Начиная с версии 2010, в Excel добавили инструменты, в названиях которых присутствует слово power: Power Query, Power Pivot и Power View. Они позволили сгладить грань между пользователями Excel и комплексных BI-систем.
Power Query
Чтобы работать с данными, к ним нужно подключиться, отобрать, преобразовать или, другими словами, привести их к нужному виду.
Для этого и необходим Power Query. До версии Excel 2013 включительно этот инструмент был в виде надстройки, которую можно было установить бесплатно с сайта Microsoft.
В версии 2016 это уже встроенный в программу инструментарий, находящийся на вкладке "Данные" (Data) в разделе "Скачать и преобразовать" (Get and Transform).
Перечень источников информации, к которым можно подключаться — огромный: от баз данных (их в последней версии 10) до Facebook и Google таблиц (рис. 1).
Рис 1. Выбор источника данных в Power Query
Вот некоторые возможности Power Query по подготовке и преобразованию данных:
отбор строк и столбцов, создание пользовательских (вычисляемых) столбцов
преобразование данных с помощью числовых, текстовых функций, функций даты и времени
транспонирование таблицы, разворачивание по столбцам (Pivot) и наоборот — сворачивание данных, организованных по столбцам, в построчный вид (Unpivot)
объединение нескольких таблиц: как вниз — одну под другую, так и связывание по общей колонке (единому ключу)
Рис 2. Окно редактора Power Query
Ну и конечно, после выгрузки подготовленных данных в Excel они будут автоматически обновляться, если в источнике данных появятся новые строки.
Пример
Компания в своей аналитике использует текущие курсы трех валют, которые ежедневно обновляются на сайте Национального банка.
Таблица на сайте непригодна для прямого использования (рисунок 2-1):
все валюты не нужны
в колонке "Курс" в качестве разделителя целой и дробной частей используется точка (в наших региональных настройках — запятая)
в колонке "Курс" отображается показатель за разное количество единиц валюты: за 100, за 1000 и т. д. (указано в отдельной колонке "Количество единиц")
Рис. 2-1. Так выглядит таблица с курсами валют на сайте Нацбанка.
С помощью Power Query мы подключаемся к таблице текущих курсов валют на сайте НБУ и в этом редакторе готовим запрос на извлечение данных:
В колонке "Курс" меняем точку на запятую (инструмент "Замена значений").
Создаем вычисляемый столбец, в котором курсы валют в колонке "Курс" делятся на количество единиц валюты из колонки "Количество единиц".
Удаляем лишние столбцы и оставляем только строки валют, с которыми работаем.
Выгружаем полученную таблицу на рабочий лист Excel.
Результат показан на рисунке 2-2.
Рис. 2-2. Так выглядит результирующая таблица в нашем Excel файле.
Курсы валют на сайте Нацбанка меняются каждый день. Но при обновлении данных в документе Excel наш, один раз подготовленный, запрос пройдет через все шаги, и результирующая таблица всегда будет в нужном виде, но уже с актуальными курсами.
Power Pivot
У вас данные находятся в разрозненных источниках? Некоторые таблицы содержат больше 1 млн строк? Вам нужно все это объединить в одну модель данных и анализировать с помощью, например, сводной таблицы Excel? Здесь понадобится Power Pivot — надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).
В Power Pivot вы можете добавлять данные из разных источников, связывать таблицы между собой (рисунок 3). Таблицы при этом не обязательно должны находиться на рабочих листах Excel. Вместо этого они по-прежнему будут храниться в файле Excel, но просматривать их можно в окне Power Pivot (рис. 4). Поэтому нет ограничения на количество строк — в вашем файле Excel могут находиться таблицы и в сотни миллионов строк.
Рис. 3. Окно Power Pivot в представлении диаграммы
Рис. 4. Окно Power Pivot в представлении данных
Вот некоторые возможности Power Pivot, помимо описанных выше:
добавлять вычисляемые столбцы и поля (меры), в том числе основанные на расчетах из нескольких таблиц
создавать и мониторить в сводной таблице ключевые показатели эффективности (KPI)
создавать иерархические структуры (например, по географическому признаку — регион, область, город, район)
И обрабатывать все это с помощью сводной таблицы Excel, построенной на модели данных.
Пример. У предприятия в базе данных (или отдельных файлах Excel) в 5 таблицах хранится информация о продажах, клиентах, товаре и его классификации, менеджерах по продажам и закупочных ценах продукции. Необходимо провести анализ по объемам продаж и маржинальности по менеджерам.
С помощью Power Pivot:
добавляем все 5 таблиц в модель данных
связываем таблицы по общим ключам (столбцам)
в таблице "Продажи" создаем вычисляемый столбец "Продажи в закупочных ценах", умножив количество штук из таблицы "Продажи" на закупочную цену из таблицы "Цена закупки"
создаем вычисляемое поле (меру) "Маржа"
с помощью инструмента "Ключевые показатели эффективности" устанавливаем цель по маржинальности и настраиваем визуализацию — как выполнение цели будет визуализироваться в сводной таблице
Теперь можно "крутить" эти данные в сводной таблице или в отчете Power View (следующий инструмент) и анализировать маржинальность по товарам, менеджерам, регионам, клиентам.
Power View
Иногда сводная таблица — не лучший вариант визуализации данных. В таком случае можно создавать отчеты Power View. Как и Power Pivot, Power View — это надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).
В отличие от сводной таблицы, в отчет Power View можно добавлять диаграммы и другие визуальные объекты. Здесь нет такого количества настроек, как в диаграммах Excel. Но в том то и сила инструмента — мы не тратим время на настройку, а быстро создаем отчет, визуализирующий данные в определенном разрезе.
Вот некоторые возможности Power View:
— быстро добавлять в отчет таблицы, диаграммы (без необходимости настройки)
организовывать срезы и фильтры
уходить на разные уровни детализации данных
добавлять карты и располагать на них данные
создавать анимированные диаграммы
Пример отчета Power View — на рисунке 5.
Рис. 5. Пример отчета Power View
Даже самые внушительные массивы данных можно систематизировать и визуализировать — главное не ограничиваться поверхностными возможностями Excel, а брать из его функций все возможное.
Источник
ОБРАБОТКА ДАННЫХ СРЕДСТВАМИ MS EXCEL
Учебное пособие соответствует ГОС ВПО специальностям инженерно-техническим, гуманитарным, экономическим.
Включает теоретическую часть, в которую входит четыре раздела.
На различных примерах продемонстрированы широкие возможности
MS Excel при решении инженерных, математических, экономических и других видов задач, проведении расчетов и их визуализации в виде графиков, сводных таблиц и т.д.
В настоящее время Microsoft Excel – ведущая программа обработки электронных таблиц, представляющая собой достаточно мощное средство разработки информационных систем, которое включает как электронные таблицы (со средствами финансового и статистического анализа, набором стандартных математических функций, доступных в языках программирования высокого уровня, рядом дополнительных функций, встречающихся только в библиотеках инженерных программ), так и средства визуального программирования (Visual Basic for Application). С помощью VBA можно автоматизировать всю работу, начиная от сбора информации, ее обработки до создания итоговой документации как для офисного пользования, так и для размещения на Web-узле.
Электронные таблицы позволяют в различных областях науки (экономика, физика, математика и т.д.) и прикладных областях (техника, электроника, машиностроение и т.д.), следующая:
· решать различные вычислительные задачи (численные решения дифференциальных, интегральных, матричных систем уравнений, решение задач линейного программирования и многое другое);
· решать задачи моделирования (на основе аппарата решения задач линейного программирования, выбирать оптимальный вариант в массе экономических задач и задач планирования, многократно рассчитывать по формулам различные величины и строить зависимости их значений от параметров);
· осуществлять задачи обработки данных: статистического анализа; построения диаграмм, ведение баз данных.
Кроме того, табличные процессоры позволяют, следующее:
· в удобной форме представлять разнообразные сведения (применять различные шрифты, начертания, цвета, эффекты оформления);
· автоматизировать экономическо-финансовую деятельность;
· выполнять простейшие математические операции и вычислять значения математических функций;
· строить различные графики и диаграммы;
· вести коллективную работу различным пользователям через локальные и глобальные сети;
· внедрять элементы изображений, звука, видео;
· автоматизировать выполнение операций и расчетов с помощью макросов и программных вставок.
В результате добросовестного выполнения лабораторных работ, которым должно предшествовать изучение теоретических положений, студент сможет получить необходимые и достаточные пользовательские знания, т.е. он должен
· основные возможности MS Excel;
· основные приемы работы с MS Excel;
· основные термины и понятия, которые используются в MS Excel;
· общую методологию создания диаграмм и построения графиков;
· общую методологию использования баз данных в MS Excel;
· создавать, редактировать и форматировать таблицы;
· производить расчеты с данными в таблице, используя функции;
· создавать диаграммы с целью последующего изучения динамики процессов и результатов;
· работать с базами данными в MS Excel.
Настоящее учебное пособие условно можно подразделить на три части: 1) теоретическую, 2) практическую (для аудиторной работы) и 3) задания для самостоятельного выполнения.
Раздел 1. Основные приемы работы с Ms Excel
1.1. Окно MS Excel
После запуска MS Excel на экране появляется главное окно программы (рис. 1.1), в котором отображается рабочая книга. Основным элементом программы является окно MS Excel. Одновременно в MS Excel можно работать с несколькими рабочими книгами, каждая из которых представлена в своем окне.
Рис. 1.1. Окно MS Excel
Верхняя строка окна MS Excel называется строкой заголовка. В ней располагаются: кнопка вызова системного меню (слева), имя приложения и имя рабочей книги, а также кнопки управления окном (справа): Свернуть, Восстановить и Закрыть.
Ниже строки заголовка находится строка меню, содержащая названия пунктов меню программы. Каждый пункт меню, в свою очередь, включает подменю, состоящее из набора некоторых команд. Приведем назначение основных пунктов меню MS Excel.
Пункт Файл предназначен для работы с файлом рабочей книги.
Пункт Правка предлагает команды редактирования содержания рабочей книги.
Пункт Вид служит для изменения окна MS Excel и активной рабочей книги (например, включение или отключение строки формул, добавление примечания к ячейке и т.д.).
Пункт Вставка позволяет добавлять в рабочую книгу какие-либо объекты, либо элементы рабочей книги.
Пункт Формат предназначен для проведения операций форматирования, т. е. изменения вида представления данных, находящихся в рабочей книге.
Пункт Сервис предлагает некоторые возможности для работы с данными, листами рабочей книги, а также для индивидуальных настроек и запуска редактора VBA.
Пункт Данные содержит находящиеся в рабочей книге команды для обработки различных данных.
Пункт Окно служит для работы с окном (или окнами) рабочей книги (или книг).
Пункт Справка помогает получить справку о программе или какой-либо команде.
Под строкой меню располагаются панели инструментов Стандартная и Форматирование. Как правило, на панели инструментов выносятся кнопки команд пунктов строки меню.
Под панелями инструментов располагается строка формул, предназначенная для обработки содержимого ячеек. Строка формул (рис. 1.2) разделена на три части:
Рис. 1.2. Строка формул
В первой части строки формул (в поле имени) указывается адрес активной ячейки или размер выделяемого диапазона ячеек (например, D15). Если для некоторых диапазонов ячеек заданы имена, то они представлены в поле списка под полем и могут использоваться для быстрого перехода к необходимому диапазону ячеек.
Во второй части располагаются кнопки: удалить и ввод , предназначенные для редактирования содержимого текущей ячейки.
Третья часть отображает содержимое текущей ячейки, которое можно редактировать с помощью кнопок, расположенных во второй части. Редактирование производится либо в самой ячейке, либо в строке формул.
Большую часть экрана занимает окно рабочей книги – рабочая область.
В нижней части окна программы располагается строка перехода по листам рабочей книги и строка состояния. Строка перехода по листам рабочей книги включает в себя кнопки прокрутки ярлычков листа, ярлычки листов рабочей книги, полосу разделения ярлычков листа. В левой части строки состояния отображаются различные текстовые сообщения, а в правой – указан статус некоторых функций клавиатуры и текущий режим ввода [8].
1.2. Основные понятия MS Excel
Любая таблица состоит из столбцов и строк. Строки нумеруются цифрами 1, 2, 3 и т.д. до 65536, а столбцам присваивается соответствие латинскому алфавиту A, B, C и т. д. Всего 256 столбцов. Причем? когда алфавит заканчивается, за Z следует AA, AB, AC и т. д. до IV. Пересечения строк и столбцов образуют ячейки, каждая ячейка имеет адрес, который обозначается именем столбца и номером строки. Например: B5. Ячейки являются минимальными элементами хранения данных. Одна из ячеек всегда является активной и выделяется рамкой. Эта рамка в MS Excel играет роль табличного курсора.
В ячейки заносятся данные. В дальнейшем, при обращении к этим данным, всегда идет ссылка на адрес ячейки, где они расположены [4].
Рабочий лист – это основной тип документа, используемый в Excel для хранения и манипулирования данными. Он состоит из ячеек, организованных в столбцы и строки, и всегда является частью рабочей книги.
Рабочая книга – это файлы MS Excel, которые могут содержать один или несколько рабочих листов. Такие файлы имеют расширение .xls.
Объектами электронной таблицы также являются:
Диапазон ячеек – прямоугольная область ячеек. Адресуется от верхнего левого до нижнего правого угла с разделителем «двоеточие». Например, А1:А9 (столбец), В2:Z2 (строка), С3:Е8 (прямоугольная область).
На рис. 1.3 выделен диапазон В2:С6. Обратите внимание на то, что первым всегда идет адрес верхней левой ячейки, а вторым – адрес нижней правой ячейки диапазона.
· Перечисление – перечисленные отдельные адреса ячеек. Разделитель – «точка с запятой». Например, N22; B2; F11.
На рис. 1.4 выбрано перечисление: B3; D3; A5; C6. Порядок перечисления для большинства функций и операций не важен.
Источник