Типы и форматы данных в Microsoft Excel

Размещено на http://www.allbest.ru/

Содержание

Задание №1

1.1 Типы данных в электронных таблицах Excel

1.2 Форматы представления данных

1.3 Расчеты с датой и временем

1.4 Использование встроенных функций

Список литературы

Задание №1

тип формат данный программирование

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

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

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

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

1.1 Типы данных в электронных таблицах Excel

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

Таблица 1. Типы данных, используемые в VBA

1.2 Форматы представления данных

В комплект поставки Excel 5.0 входит программа обработки внешних баз данных MS Query.

Программа Query (Запрос) может работать с текстовыми файлами и с данными, которые были подготовлены следующими программами управления базами данных: Ms Access, dBASE, FoxPro, Oracle, Paradox, SQL Server. Набор данных, хранящийся на диске в формате, одной из перечисленных выше баз данных, будем называть внешней базой данных.

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

Рассмотрим основные типы форматов (табл. 2, 3)

Таблица 2. Форматы данных

Таблица 3. Примеры применения форматов Дата и Время.

1.3 Расчеты с датой и временем

Даты

Пример 1.

Элементарные операции с датами.

1. Для того чтобы понять, как хранятся даты в ячейках Excel, введем в ячейку А1 дату 1 января 1900 года. В Excel 5.0/7.0 нужно ввести 1/1/00, в Excel 97/2000 нужно ввести 1/1/1900 (дата 1/1/00 будет воспринята как 1/1/2000 - фирма Microsoft позаботилась о грядущем переходе в новое тысячелетие). Далее расположим в А2:А10 даты 2/1/1900, 3/1/1900 ... (для этого воспользуйтесь пунктом меню "Правка/Заполнить/Прогрессия"), Скопируем блок А1:А10 в В1:В10. А теперь наложим на блок В1:В10 формат "Основной". Это можно сделать так: Ctrl+Shift+~ либо выбрать в меню "Формат/Ячейки/Число/Основной". В В1:В10 хранится арифметическая прогрессия 1,2, ..., 10. Итак, дата в Excel - количество дней, прошедших от 1 января 1900 г. Удалим блок А1:В10 (выделим его и нажмем клавишу Del).

2. Введем в ячейку А1 формулу =СЕГОДНЯ(). (Найдем эту функцию в Мастере функций в категории "Дата и время"). Отображается сегодняшнее число. Наложим на ячейку А1 формат "Общий". Мы увидим количество дней, прошедших от начала XX века до сегодняшнего дня. Вернем ячейке прежний формат.

3. Наложим на А1 по очереди различные форматы даты, доступные в списке "Формат/Ячейки/Число/Дата и время".

4. Испытаем некоторые функции работы с датами. Введите в А2 формулу =ГОД(А1), в A3 формулу =МЕСЯЦ(А1), в А4 формулу =ДЕНЬ(А1). Однако, к своему удивлению, мы увидим какие-то странные даты. Например, если текущий год 1998, то в А2 отобразится 20/06/05. Дело в том, что, когда мы удалили содержимое ячеек А1:А10, остался автоматически наложенный на эти ячейки формат даты. Исправим положение. Выделите А2:А4 и наложите на них формат "Основной". Блок надо было очищать иначе: нужно было после выделения блока выбрать в меню пункт "Правка/Очистить/Все". Тогда в ячейках исчезнет и наложенный на них формат.

5. Иногда требуется получить порядковый номер дня недели. Для этого в ячейку А5 введите формулу =ДЕНЬНЕД(А1,2) (выясним в Справке назначение второго аргумента этой функции).

6. А как получить название дня недели или название месяца? Скопируйте А1 в В1:Е1 (протянем маркер заполнения). Для каждой из ячеек введите пользовательский формат. Например, для В1 выберите в меню "Формат/Ячейки/Число/(все форматы)" и в поле ввода "Тип:" введите ДДД. Нажмите "ОК". Отобразится сокращенное название дня недели. Далее сделайте для С1 формат ДДДД, для D1 формат МММ, для Е1 формат ММММ.

Такой способ не всегда удобен. Иногда хотелось бы получить в ячейке текстовую строку с названием дня или месяца. Для этого используется функция ТЕКСТ(значение, форматная строка). Получим в блоке В2:В5 те же результаты, что и в В1:Е1. Введем в ячейку В2 формулу =ТЕКСТ(А1,"ДДД"). В ячейке будет отображено сокращенное название дня недели, причем выровненное по левому краю. Теперь продемонстрируем метод копирования формул, когда по каким-либо причинам мы не хотим использовать абсолютную адресацию. Превратим формулу в ячейке В2 в текстовую строку: для этого нажмем функциональную клавишу F2 и предварим формулу одиночной кавычкой: `=ТЕКСТ(А1,"ДДД"). Скопируем эту строку в блок ВЗ:В5 (протяните маркер заполнения). Исправьте в формулах форматные строки на "ДДДД", "МММ", "ММММ". Заодно удаляйте кавычку перед знаком равенства - текст превратится в формулу.

7. Две последние цифры года можно получить по формуле =ТЕКСТ(А1,"ГГ"). Но при этом получится текстовая строка. Чтобы получить число, нужно набрать формулу =ЗНАЧЕН(ТЕКСТ(А1,"ГГ")). Проверьте.

Пример 2. Получение рядов дат. Мы неоднократно использовали команду меню "Правка/Заполнить/Прогрессия". Эту команду можно применять для получения рядов дат.

Дополнительные функции для работы с датами

Некоторые полезные функции доступны, если установить надстройку "Пакет анализа", а затем подключить эту надстройку с помощью команды меню "Сервис/Надстройки".

Наиболее полезной из новых функций является функция КОНМЕСЯЦА(начальная_дата, число_месяцев).

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

Пример 3.

Допустим, в А4 записана дата 3 апреля 2000 г. Тогда функция КОНМЕСЯЦА (А4, -2) вернет число 36585. Наложим на него формат даты: Ctrl+1/Число/Дата/, выберем образец 16/04/97 (в Excel 2000 образец другой: 14/03/99), получим 29/02/00.

Функция ЧИСТРАБДНИ(начальная дата, конечная дата, праздники) возвращает количество рабочих дней между начальной и конечной датами включительно. Рабочими считаются все дни, за исключением выходных и праздничных дней.

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

Проще всего этой функцией пользоваться, если ее аргументы - имена ячеек, последний аргумент - имя блока. Если же надо в качестве аргумента ввести дату как строковую константу, то преобразуйте ее функцией ДАТАЗНАЧ (например, ДАТАЗНАЧ("23/06/99") - эта функция вернет порядковый номер даты).

Время

Пример 4.

1. Время вводится в ячейку в формате чч:мм:сс. Например, 15.56:48, т.е. 15 ч. 56 мин. 48 с. Секунды можно и не вводить (15:24). Можно не вводить и минуты, но двоеточие должно присутствовать обязательно (18:).

2. Теперь изучим внутренний формат представления времени. Введем в ячейку 12:, т.е. 12 ч. Отобразится 12:00. Наложим общий формат (клавиши Ctrl+Shift+~). В ячейке - число 0.5, т.е. половина суток. Введите 9:, 18: и наложите общий формат. Ясно, что внутреннее представление времени - это доля суток.

В ячейку можно ввести дату и время. Например, введите 12/02 15:. В ячейке отобразится 12/02/99 15:00 (если текущий год 1999). Наложите общий формат. Вы получите число 36203.625, т.е. количество дней от 01/01/1900 и доля суток.

Пример 5.

Сколько часов прошло с 6 ч. 14 апреля до 15 ч. 16 апреля? Введем в ячейку D1 дату и время 14/4 6:, а в ячейку D2 - 16/4 15:. В D3 поместим формулу = D2 - D1 и наложим формат [ч].

Ответ: 57 ч. Можно было выбрать в списке форматов [ч]:м:с. Ответ был бы выведен в форме 57:00:00. Аналогичный прием (заключение форматного символа в квадратные скобки) подходит для минут и секунд между двумя моментами времени.

1.4 Использование встроенных функций

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

VISUAL BASIC

Начиная с версии 5.0 в программу Excel включен специальный язык программирования, который получил название Visual Basic for Applications (VBA).

Введение достаточно мощного языка программирования в Excel делает эту программную платформу весьма привлекательной для профессионалов, которые занимаются разработкой специализированных прикладных систем. Разработка языка программирования VBA, встраиваемого в прикладные системы, является одним из стратегических направлений компании Microsoft. Этот язык уже интегрирован в такие программы, как Word for Windows, Power Point и ряд других.

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

Программирование табличных функций

Чтобы создать отдельный рабочий лист для программного модуля, щелкните по пиктограмме Insert Module из пиктографического меню Visual Basic (1-я пиктограмма) или вызовите директиву Module из меню Insert Macro. После этого появится новый рабочий лист "Modele1". В программном модуле нужно описать функцию на языке VBA. В окне программного модуля можно работать, как в окне небольшого текстового редактора, но при этом необходимо помнить, что Вы пишите текст программы. Описание функции должно начинаться оператором Function, за которым через пробел следуют название функции и ее аргументы, заключенные в скобки и разделенные запятыми. Затем идет собственно текст программного кода функции, а заканчиваться описание должно оператором End Function.

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

Function NDS(Value)

NDS=Value*0.15

End Function

Применение функций

Перейдите на рабочий лист, где будет расположена таблица. Переместите указатель ячеек в ячейку, в которой будет находиться формула, и введите в нее знак равенства. Затем щелкните по 14-й пиктограмме Конструктора функций на основной пиктографической панели. Появится диалоговое окно Конструктора функций. На первом шаге выберите категорию Financial и в правом поле найдите свою функцию NDS. Щелкните по названию этой функции, после чего перейдите к следующему шагу, щелкнув по командной кнопке Next. Откроется второе диалоговое окно Конструктора функций. Здесь можно будет увидеть Ваш комментарий к функции, который был введен ранее в окне макроопций. Укажите единственный аргумент для этой функции Value и закройте диалоговое окно Конструктора щелчком по кнопке Finish.

В таблице появится значение, составляющее 15% величины аргумента. В таблице с этой функцией можно работать как с обычной функцией программы Excel.

Список используемой литературы

Конюховский Т.В., Колесов Д.Н. «Экономическая информатика». С.-П., 2000.

Лавренов С.М. «Excel. Сборник примеров и задач». М., 2003.

Гарнаев А. «Excel 2002. Разработка приложений». С.-П., 2003.

Размещено на Аllbest.ru