Exel - все о таблицах

Содержание:

Введение   2

Историческая справка           3                                Создание сводной таблицы          7

Сводные таблицы          7    Реорганизация сводной таблицы        11 Цикл по элементам на оси страниц         11 Вывод итогов для поля страницы         11 Вывод итогов для поля страницы         11 Изменение расположения кнопок полей страниц      11 Обновление сводной таблицы         12

Выделение элементов сводной таблицы                      13  

Форматирование сводной таблицы        14

Добавление и удаление полей        15   

Сортировка элементов          15         

Группировка данных          17       

Использование общих и промежуточных  итогов     17              

Изменение вычислений в сводных  таблицах      19       

Ссылки на данные в сводной таблице       19     Использование сводной таблицы для консолидации диапазонов   20                 Заключение           21              

ЛИТЕРАТУРА            22    

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение                 

         Что можно сказать о современных программах обработки электронных таблиц? Сегодня это средства, позволяющие не только выполнять вычисления, но и управлять списками и создавать диаграммы. Если же говорить об EXCEL, которая является одной из наиболее известных программ обработки электронных таблиц, то без преувеличения можно утверждать, что ее возможности практически неисчерпаемы. Лично я считаю, что такие программы на сегодняшний день представляют собой один из наиболее мощных и гибких инструментов, созданных для компьютера.                 

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

          За  всю историю табличных расчетов  с применением персональных компьютеров требования пользователей к подобным программам существенно изменились. В начале основной акцент в такой программе, как, например , Visi Calc, ставился на счетные функции. Сегодня, положение другое. Наряду с инженерными и бухгалтерскими расчетами организация и графическое изображение данных приобретают все возрастающее значение. Кроме того, многообразие функций, предлагаемое такой расчетной и графической программой, не должно осложнять работу пользователя. Программы для Windows создают для этого идеальные предпосылки.

            В последнее время многие как  раз перешли на использование  Windows в качестве своей пользовательской среды. Как следствие, многие фирмы, создающие программное обеспечение, начали предлагать большое количество программ для Windows.

              

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Историческая справка

Microsoft Excel (также иногда называется Microsoft Office Excel) — программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS. Она предоставляет возможности расчетов, графические инструменты, и, за исключением Excel 2008 под Mac OS X, язык макро-программирования VBA (Visual Basic для приложений). Фактически это самый популярный табличный процессор, доступный на этих платформах, и является таковым с версии 5 от 1993 года, входя в состав Microsoft Office. На сегодняшний день Excel является одним из наиболее популярных приложений в мире.

В 1982 году Microsoft запустила на рынок первый электронный табличный процессор Multiplan, который был очень популярен на СР/М системах, но на MS-DOS системах он уступал Lotus 1-2-3. Первая версия Excel предназначалась для Мае и была выпущена в 1985 году, а первая версия для Windows была выпущена в ноябре 1987 года. Lotus не торопилась выпускать 1-2-3 под Windows, и Excel с 1988 года начала обходить по продажам 1-2-3, что в конечном итоге помогло Microsoft достичь позиций ведущего разработчика программного обеспечения. Microsoft укрепляла свое преимущество с выпуском каждой новой версии, что имело место примерно каждые два года. Текущая версия для платформы Windows - Excel 12, также известная как Microsoft Office Excel 2007. Текущая версия для платформы Mac OS X - Microsoft Excel 2008.

В начале своего пути Excel стал причиной иска о товарном знаке от другой компании, уже продававшей пакет программ под названием "Excel". В результате спора Microsoft был обязана использовать название "Microsoft Excel" во всех своих официальных пресс-релизах и юридических документах. Однако со временем эта практика была позабыта, и Microsoft окончательно устранила проблему, приобретя товарный знак другой программы. Microsoft также решила использовать буквы XL как сокращенное название программы: иконка Windows-программы состоит из стилизованного изображения этих двух букв, а расширение файлов по умолчанию в Excel - .xls. В сравнении с первыми табличными процессорами Excel представляет множество новых функций пользовательского интерфейса, но суть остается прежней: как и в программе-родоначальнике VisiCalc ячейки расставляются в строки и столбцы и могут содержать данные или формулы с относительными или абсолютными ссылками на другие клетки.

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

Будучи впервые объединенными в Microsoft Office в 1993 году, Microsoft Word и Microsoft PowerPoint получили новый графический интерфейс для соответствия Excel, главного стимула модернизации ПК в то время.

Начиная с 1993 года в состав Excel входит Visual Basic для приложений (VBA), язык программирования, основанный на Visual Basic, позволяющий автоматизировать задачи Excel. VBA является мощным дополнением к приложению, которое в более поздних версиях стало включать полную интегрированную среду разработки. Можно создать VBA-код, повторяющий действия пользователя, позволяя таким образом автоматизировать простые задачи. VBA позволяет создавать формы для общения с пользователем. Язык поддерживает использование (но не создание) DLL от ActiveX; более поздние версии позволяют использовать основы объектно-ориентированного программирования.

Функциональность VBA сделала Excel мишенью для макровирусов. Это было серьезной проблемой до тех пор, пока антивирусные продукты не стали их обнаруживать. Microsoft с опозданием приняла меры для уменьшения риска, добавив возможность полностью отключить макросы, включить макросы при открытии документа или доверять всем макросам, подписанных с использованием достоверных сертификатов.

Версии Excel от 5.0 до 9.0 содержат различные «пасхальные яйца», хотя, начиная с версии 10 Microsoft начала принимать меры для ликвидации таких недокументированных возможностей своих продуктов.

Версии:

Версии для Windows и Windows NT

'Excel 97' (8.0) на Windows XP Microsoft Office Excel 2003

*  1988 — Excel 2.0 для Windows

*  1990 —Excel 3.0

*  1992 —Excel 4.0

*  1993 — Excel 5.0 (Office 4.2 и 4.3, также есть 32-битная версия только для Windows NT)

*  1995 — Excel 7 для Windows 95 (включён в пакет Microsoft Office 95)

*  1997 — Excel 97 (включён в пакет Microsoft Office 97)

*  1999 — Excel 2000 (9) — Microsoft Office 2000

* 2001 — Excel 2002 (10) — Microsoft Office XP

* 2003 — Excel 2003 (11) — Microsoft Office 2003

* 2007 — Excel 2007 (12) — Microsoft Office 2007

* Замечание: Версия 1.0 не использовалась, чтобы избежать путаницы с  продуктами Apple

* Замечание: Excel 6.0 не существует, так как версия Windows 95 поставлялась с Word 7. Все Office 95 и Office 4.x использовали OLE 2.0 - для автоматического перемещения данных из различных программ, - и Excel 7 должен был показывать, что он соответствует Word 7.

Версии для Macintosh

* 1985-	-Excel 1.0
* 1988-	-Excel 1.5
* 1989-	- Excel 2.2
* 1990-	-Excel 3.0
* 1992-	-Excel 4.0
* 1993 -	-Excel 5.0
* 1998-	-Excel 8.0 (Office'98)
* 2000 -	-Excel 9.0 (Office 2001)
* 2001 -	-Excel 10.0 (Office v.X)
* 2004 -	-Excel 11.0 (Office 2004)
* 2008 -	- Excel 12.0 (Office 2008)
Версии для OS/2
* 1989-	— Excel 2.2
* 1990-	-Excel 2.3
* 1991 -	-Excel 3.0

Версии Excel до 7.0 были ограничены в возможностях хранения данных, вмещая до 16К (2 ^Л 14) строк. Версии 8.0 - 11.0 могли справиться с 64К (2 ^А 16) строк и 256 колонками (2 ^А 8). Версия 12.0 справляется с 1М (2 ^Л 20 = 1048576) строк и 163 84 (2 ^А 14) колонок.

Форматы файлов:

Microsoft Excel вплоть до 2007 версии использовал свой собственный бинарный формат файлов (BIFF) в качестве основного. Excel 2007 использует Microsoft Office Open XML в качестве своего основного формата.

Несмотря на то, что Excel 2007 поддерживает и направлен на использование новых XML-форматов в качестве основных, он по-прежнему совместим с традиционными бинарными форматами. Кроме того, большинство версий Microsoft Excel могут читать CSV, DBF, SYLK, DIF и другие форматы.

Office Open XML:

Microsoft Excel 2007, наряду с другими продуктами Microsoft Office 2007, вводит множество новых форматов файлов. Они являются частью Office Open XML (OOXML) спецификации.

Экспорт и перемещение  таблиц

API позволяет открывать таблицы Excel в ряде других приложений. Это включает в себя открытие документов Excel на веб-страницах с помощью ActiveX или таких плагинов, как Adobe Flash Player. Проект Apache POI представляет Java-библиотеки для чтения и записи электронных таблиц Excel. Так же предпринимались попытки копировать таблицы Excel в веб-приложения с использованием разделенных запятыми значений.

Программирование:

Ценной возможностью Excel является возможность писать код на основе Visual Basic для приложений (VBA). Этот код пишется с использованием отдельного от таблиц редактора. Записи таблицы управляются с помощью объектов. С помощью этого кода любые функции или подпрограммы, которые могут быть созданы в семействах языков Basic или Fortran, могут запущены на основе входных данных таблиц, а результаты будут мгновенно записываться кодом в электронные таблицы или будут отображены на диаграммах (графиках). Таблица становится интерфейсом кода, позволяя легко работать с кодом и его расчетами.

Критика:

Общая критика табличных процессоров  применима и к Excel. Специфичные предметы критики в Excel - это точность, проблемы с датами и ошибки отображения Excel 2007.

Точность

Из-за того, что Excel работает на основе расчетов с плавающей запятой, статистическая точность Excel подвергается критике. Сторонники Excel заявляют в ответ, эти ошибки проявляются лишь в экстремальных условиях и затрагивают относительно небольшое число пользователей. Excel также имеет проблемы с работой модулей. Если результат слишком велик, Excel вернет ошибку #NUM!.

Проблемы с датой

Excel ошибочно полагает, что 1900 год был високосным годом. Ошибка возникла в Lotus 1-2-3, и была намеренно внесена в Excel для совместимости. Позже ошибка была перенесена и в формат Office Open XML файл формата.

Ошибки отображения Excel

22 сентября 2007 года было сообщено, что в определенных ситуациях  Excel 2007 будет показывать неправильные результаты. В частности, для тех пар чисел, чье произведение равно 65535 (например, 850 и 77,1), Excel отобразит в качестве итога 100000. Это происходит с примерно 14,5% таких пар. Кроме того, если к результату добавить единицу, Excel выведет итог 100001. Однако если вычесть из итога единицу, на дисплее отобразится правильный результат 65534. (Также, если итог умножить или разделить на 2, будут отображены 131070 и 32767,5 соответственно.)

Microsoft сообщила в блоке Microsoft Excel, что проблема существует в отображении шести конкретных значений с плавающей запятой между 65534,99999999995 и 65535 и шесть значений между 65535,99999999995 и 65536 (не включая границ). Любые расчеты, результат которых равен одному из двенадцати значений, будут отображаться неправильно. Фактические данные, хранящиеся и передающиеся в другие ячейки, верны, неверно лишь отображение значения. Ошибка появилась в Excel 2007 и не существует в предыдущих версиях. 9 октября 2007 года Microsoft выпустила патч, исправляющий проблему. Он вошел и в состав исправлений Service Pack l.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

СВОДНЫЕ ТАБЛИЦЫ

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

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

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

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

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

 

Создание сводной таблицы

              При создании сводной таблицы  можно использовать один из четырех типов источников данных:

• список Excel;
• внешний источник данных, доступный через Microsoft Query;
• несколько диапазонов консолидации (отдельных списков Excel);
• другую сводную таблицу.

 

 

 

 

 

 

 

 

  Запуск мастера сводных таблиц

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

                                                

1. Задание типа источника данных.
2. Указание местонахождения исходных данных или получение их из внешнего источника с помощью Microsoft Query.
3. Задание макета таблицы и выбор итоговой функции.
4. Указание места для размещения таблицы.

 

 

 

 

Шаг 1: задание типа источника данных

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

 

                                

Шаг  2: указание местонахождения  исходных данных

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

 

Шаг 3: задание макета таблицы

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

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

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

По умолчанию мастер сводных таблиц применяет функцию Сумма

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

      СУММ ( )                                    - сумма;

      СЧЕТ ( )                                      - количество значений;

      СРЗНАЧ ( )                                 - среднее;

      МАКС ( )                                     - максимум 

      МИН ( )                                       - минимум;

      ПРОИЗВЕД ( )                            - произведение;

      СТАНДОТКЛОН ( )                  - несмещенное отклонение;

      СТАНДОТКЛОНП ( )               - смещенное отклонение;

      ДИСП ( )                                      - несмещенная дисперсия;

      ДИСПР ( )                                   - смещенная дисперсия.

 

Шаг 4: указание места для размещения таблицы

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

          Чтобы  поместить таблицу на новом  рабочем листе, установим переключатель Новый лист. В противном случае установим переключатель Существующий лист и введем имя диапазона на ссылку.

После нажатия кнопки Готово Excel почти без промедления создает сводную таблицу. Если таблица основана на внешних данных, Excel сначала выполнит запрос, а затем построит сводную таблицу.

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

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

 

Сводная ведомость зарплаты за полугодие

 

 

Реорганизация сводной  таблицы

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

   Размещение полей на оси страниц

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

Цикл по элементам  на оси страниц

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

Вывод итогов для поля страницы

            Внизу каждого раскрывающегося  списка поля страницы виден  элемент (Все). Выбор этого элемента  списка позволяет вывести на экран итоговые значения для каждого поля на оси страниц.

Перемещение элементов поля страницы на отдельные листы книги

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

Изменение расположения кнопок полей

страниц

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

1. Выделить в сводной таблице любую ячейку.
2. Выбрать команду Параметры в меню Сводная таблица на панели инструментов Сводные таблицы.
3. Используя раскрывшийся список Макет страницы и поле Число полей в столбце окна диалога Параметры сводной таблицы задаем нужное расположение кнопок.

 

 

 

Обновление сводной таблицы

 

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

Обновление таблицы  при открытии

                 файла

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

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

       Обновление связанных сводных

                        таблиц

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

Обновление сводной  таблицы, связанной с внешними данными

              Если сводная таблица построена  на основе внешнего источника  данных, то Excel может выполнить запрос к нему в фоновом режиме, что позволяет работать с таблицей во время ее обновления. Чтобы включить этот  режим обновления, нужно выделить в таблице любую ячейку и выбрать команду Параметры в меню Сводная таблица на панели инструментов Сводные таблицы. Затем в окне диалога Параметры сводной таблицы установить флажок Фоновый запрос. В том случае, когда в сводной таблице используются поля страниц , можно установить такой режим выполнения запроса, при котором Excel запрашивает только данные, необходимые для текущей просматриваемой страницы. Этот режим предъявляет более скромные требования к ресурсам компьютера, поскольку при выполнении запроса расходуется меньше оперативной памяти, что может оказаться немаловажным при работе с большой внешней базой данных. Кроме того,  можно предпочесть этот режим при редком переключении страниц.

Чтобы запрашивать внешний источник данных только для одной страницы, нужно дважды щелкнуть на кнопки любого поля страницы. В окне диалога, которое  появится, следует нажать кнопку Далее. Затем в окне диалога Дополнительные параметры поля сводной таблицы установить переключатель Обновлять внешние данные при выборе каждого элемента.

 

 

 

 

Выделение элементов сводной таблицы

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

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

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

Включение и выключение   структурного выделения

         Чтобы включить или выключить структурное выделение , выделим любую часть сводной таблицы. Затем выберем команду Выделить в меню Сводная таблица на панели инструментов Сводные таблицы.

Когда структурное выделение включено, кнопка слева от команды Разрешить  выделение находится в нажатом состоянии, и три верхние команды этого подчиненного меню становятся доступными.

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

Выделение только заголовков или только данных

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

       Выделение всей сводной таблицы

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