Excel как база данных лекция
2.1. Общие положения.
2.2. Списки Excel как база данных.
2.3. Проверка данных при вводе.
2.4. Сортировка данных.
2.5. Промежуточные итоги в базе данных.
2.6. Автофильтр.
2.7. Расширенный фильтр.
3. Порядок выполнения работы.
4. Контрольные вопросы.
5. Список рекомендуемой литературы.
1. ЦЕЛЬ РАБОТЫ
Цели:
- повышение уровня понимания темы "Базы данных в приложении Microsoft Excel";
- овладение специальными техническими умениями конструирования и использования реляционной базы данных на уровне их свободного использования;
- развитие навыков самостоятельной работы и способности применить полученные знания на практике при разработке собственной базы данных.
2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
2.1. Общие положения
Базы данных как способ хранения и обработки различной информации играют в настоящее время огромную роль. В базах данных хранят сведения о клиентах, заказах, справочники адресов и телефонов, различного рода информацию о туристических агентствах и предлагаемых услугах и т. д.
Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.
В предложенном пособии рассматриваются основные возможности, предоставляемые Excel, для работы с базами данных на примере списка "Сотрудники", который содержит информацию о работниках некоторого условного предприятия. Такая база данных удобна по следующим причинам:
- она обеспечивает удобный метод поиска информации о работнике (фамилия, имя, отчество, место проживания, телефон, должность, дата рождения и т. д.);
- с ее помощью можно выполнять различного рода анализ, например, определять структуру кадров;
- пользуясь хранящейся в ней информацией, можно быстро сформировать отчет.
2.2. Списки Excel как база данных
Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служить список.
Список - это группа строк таблицы, содержащая связанные данные.
Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень фамилий, дату рождения и т. д. (рис. 1).
Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки - записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию. Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, должны отличаться от параметров, назначенных для строк данных. В списке не должно быть пустых строк и столбцов.
2.3. Проверка данных при вводе
Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода. Для этого надо:
2.4. Сортировка данных
Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов. Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по дням недели).
Чтобы отсортировать список надо:
- Установить курсор в ячейку списка.
- Выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр.
- В диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка; тип сортировки (по значению, цвет ячейки, цвет шрифта, значок ячейки) и порядок (по возрастанию, убыванию, настраиваемый).
Примечание. Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки (рис. 2) выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки (например, АОП, ФЭО, ИВЦ, ИТО, МПО), после чего последовательно выбрать кнопки Добавить и ОК.
2.5. Промежуточные итоги в БД
Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:
- упорядочить список посредством группировки записей с выводом промежуточных итогов, средних значений или другой вспомогательной информации;
- выводить итоговую сумму;
- отображать список в виде структуры, что позволяет разворачивать и сворачивать разделы с помощью щелчка мыши.
Перед вызовом команды Итоги список обязательно надо отсортировать по полю, которое будет использоваться для группировки.
Режим структуры, в котором оказывается список после выполнения команды Итоги, позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рис. 3).
Рис. 3. Просмотр списка в режиме структуры
Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками "+" и "-" предназначены для свертывания \ развертывания отельных групп.
Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.
2.6. Автофильтр
Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию.
Чтобы установить или убрать автофильтр надо на ленте Данные в группе Сортировка и фильтр выбрать команду Фильтр. После этого нажать кнопку со стрелкой возле названия какого-либо поля, чтобы раскрыть список его элементов и выбрать отображаемые значения или задать условие отбора. На экране появятся только те записи, которые отвечают заданному условию. В случае необходимости можно продолжить фильтрацию, нажимая кнопки со стрелками на других полях.
Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….
Показать все записи по всем полям, не убирая фильтр, команда Очистить.
Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.
Если выделить какое-то числовое поле (например, Возраст), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рис. 4), которые позволяют:
- задать критерий в виде неравенства – критерии равно, не равно, больше, больше илиравно, меньше, меньшеилиравно, между;
- вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;
- определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;
- самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.
Рис. 4. Дополнительные числовые фильтры
Настраиваемый фильтр позволяет задать критерии из одного или двух условий.
Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения.
Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ.
При написании значений в условиях сравнения в фильтрах можно использовать подстановочные знаки (Таблица 1).
В предыдущих главах, рассказывающих об Excel , все действие разворачивалось на рабочих страницах книг Excel . Как я уже говорил, рабочая страница удивительным образом инкапсулирует данные и функции, их обрабатывающие. Вычисление по формулам позволяет создать новые данные, изменение данных, в свою очередь , приводит к инициации новых вычислений. Эти возможности Excel в сочетании с возможностью графического отображения данных делают Excel незаменимым при решении самого широкого класса задач, начиная от обработки офисных документов и кончая специальными задачами в той или иной области деятельности. И все-таки, все-таки главным назначением Excel все в большей мере становится обработка данных внешних источников. Excel становится тем терминальным приложением, которое ведет анализ и обработку данных, хранящихся в самых разнообразных хранилищах данных - структурированных и неструктурированных. К структурированным источникам относят, обычно, различные базы данных . Но в последнее время возрастает интерес и к неструктурированным источникам данных.
Естественно, теме хранения данных и их обработке посвящена не одна фундаментальная книга. Цель этой главы - начать разговор об Excel и базах данных, рассмотреть локальные базы данных в Excel , поговорить о связях между базами данных Excel и Access. К этой большой теме я буду постоянно обращаться и в последующих главах. Для того чтобы разговор о базах данных был предметным, нам понадобится некоторая тестовая база данных .
База данных офиса "Родная Речь"
В качестве тестовой базы данных я рассмотрю базу данных гипотетического офиса "Родная Речь", который в дальнейшем буду называть офисом РР. Офис РР занимается издательской деятельностью и имеет отделы (группы), которые непосредственно готовят и издают книги, занимаются маркетингом и рекламой, распространением и сбытом книг. С издательством сотрудничают авторы, переводчики, книготоргующие организации.
Создание в приложении Access базы данных офиса "РР"
Для пользователей Microsoft Office 2000 создание базы данных именно в Access самая естественная вещь. Этот параграф может служить предварительным знакомством с Access для тех, кто действительно не знаком с этим замечательным приложением. Создание базы данных в Access помимо прочего обладает двумя несомненными достоинствами:
- Допускается создание и ведение базы данных простыми и интуитивно понятными средствами в визуальном стиле. Даже человек, далекий от этой весьма специфической области программирования, способен после предварительного непродолжительного знакомства начать создавать свою собственную базу данных.
- В любом из приложений Office 2000 (Word, Excel, PowerPoint) легко получить доступ к БД Access.
В первом приближении БД Access можно рассматривать как совокупность взаимосвязанных таблиц с данными, запросов к ним, форм и отчетов, облегчающих ввод данных в таблицы и вывод информации из них в удобном для конечного пользователя виде, а также программных компонентов, выполняющих различные операции над данными.
Построение БД офиса РР начну с введения минимально необходимых средств. По мере необходимости база будет расширяться.
Построение таблиц "Заказчики" и "Книги"
Access относится к реляционным базам данных, в которых основной формой представления данных являются таблицы. Таблицы связываются между собой за счет общих полей. Поскольку таблица еще и общечеловеческая форма представления данных, то ее создание интуитивно понятно всем. Запись (строку) таблицы можно рассматривать, как совокупность полей разного типа. Каждый столбец таблицы хранит значения одного поля. Так что каждая таблица задает некоторый набор записей, и каждая запись представляется одной строкой таблицы. Число полей записи однозначно определяет число столбцов таблицы. Access предоставляет различные средства, облегчающие задание структуры таблицы. Полагаю, что даже человек, не имеющий опыта работы с Access, работая в Конструкторе таблиц Access, без труда определит собственную таблицу. Отмечу лишь, что для каждого поля задается имя, выбирается его тип, и задаются другие свойства поля, например, описание. Свойства поля зависят не только от его типа, они включают такие характеристики, как указание на то, является ли поле индексируемым, имеет ли уникальное значение, должно ли обязательно присутствовать в записи. Они определяют также формат и маску, которой должно удовлетворять значение поля, и другие характеристики.
Я не буду сейчас останавливаться на средствах Access, позволяющих создавать таблицы визуально, они достаточно просты. Для нас сейчас важнее понимать структуру таблиц, входящих в состав базы данных. Начну с определения таблицы "Заказчики", хранящей информацию о заказчиках офиса РР. Каждая ее запись включает следующие поля: Код заказчика, Название, Адрес, Город, Телефон, Директор, Прочее, Email. Вот как выглядит определение таблицы в конструкторе Access, где для каждого поля задаются имя, тип, описание и другие характеристики:
Обратите внимание, обязательным условием определения таблицы базы данных является задание ключа. Ключом может быть одно поле или совокупность полей, требуется лишь, чтобы значение ключа было уникальным для каждой записи. Это требование позволяет однозначно найти запись в таблице, зная ее ключ. Требование существования ключа записей всегда выполнимо, поскольку при необходимости есть возможность добавить к исходным полям записи поле счетчика, которое может выступать в роли ключа, автоматически давая записи новый номер при добавлении ее в таблицу. На рисунке 4.1 можно видеть (ключевые поля отмечены соответствующим значком -), что в таблице "Заказчики" в роли первичного ключа выступает поле "Название". Заметьте, ключ можно определить далеко не единственным способом, например, поле "Код заказчика" также могло играть роль первичного ключа.
Определим теперь структуру таблицы "Книги". Эта таблица содержит информацию о книгах, выпускаемых издательством "РР". Достаточно взглянуть на рисунок, чтобы увидеть, какие поля в нее включены:
Две введенные таблицы "Заказчики" и "Книги" пока не связаны между собой, - они не имеют общих полей. Такая связь возникнет позже - при расширении базы данных. Она появится, когда заказчики начнут заказывать книги. Позже я добавлю в базу данных таблицу "Заказы", которая будет иметь общие поля, как с таблицей "Книги", так и с таблицей "Заказчики".
Обучающие: дать общее представление об интерфейсе окна MS Excel и настройке параметров; обеспечить усвоение навыков настройки параметров окна MS Excel; сформировать умения работать в MS Excel.
Развивающие: Развивать познавательный и профессиональный интерес к предмету информатика. Развивать умения и навыки работать с программой Excel. Умения и навыки работы в группе
Воспитательные: воспитывать инициативу и самостоятельность в трудовой деятельности; воспитывать у учащихся чувство ответственности за порученное дело, исполнительности, аккуратности, добросовестности, чувство долга.
Тип урока: Урок изучения нового материала. ЛПЗ
Форма проведения: лекция, ПК, интерактивная доска, проектор
Организационный этап
Вводный этап
Актуализация опорных знаний
Практическое изучение материала
Закрепление изученного материала
Заключительный этап
Разбор типичных ошибок
ОРГАНИЗАЦИОННЫЙ ЭТАП
Выявление отсутствующих учащихся
Проверка готовности учащихся к уроку.
Инструктаж по технике безопасности.
Инструктаж по технике безопасности
Ребята, перед тем, как начать наш урок необходимо вспомнить некоторые правила техники безопасности при работе с компьютером.
Нельзя самостоятельно без разрешения преподавателя, включать и выключать компьютеры;
Нельзя касаться тыльной стороны компьютера и проводов;
Нельзя работать мокрыми руками и во влажной одежде;
Нельзя нажимать клавиши ручкой или карандашом;
В случае неисправности компьютера или при обнаружении запаха гари – предупредить преподавателя.
ВВОДНЫЙ ЭТАП
Тема урока: «Таблица MS Excel 2013, как основное понятие базы данных.» (записать в тетрадь)
Актуализация опорных знаний
Тестирование на компьютере в программе «MyTestXProSetup»
Практическое изучение материала
База данных.
База данных размещает огромные объемы информации в связанном виде. Все БД можно разделить на плоские и реляционные. Реляционная БД состоит из нескольких таблиц, связанных между собой ключевыми полями. Лист в Excel является примером плоской БД , т.е. базы, состоящей из единственной таблицы.
Каждая строка в БД содержит полную информацию о каком-либо объекте. Эти данные, записанные в строках БД, называют записями. Каждая запись состоит из полей.
База данных позволяет хранить необработанные данные, предназначенные для анализа средствами электронной таблицы Excel включая:
формы данных для упрощения добавления новых записей;
автоматический подсчет сумм по категориям;
фильтрацию для просмотра подмножеств данных;
сводные таблицы для обобщения информации;
Создание базы данных.
Создание и работа с БД упрощается, если использовать встроенную форму данных для того, чтобы вводить, удалять или редактировать записи в БД.
Чтобы создать форму данных для новой базы следует ввести сначала строку с именами полей (заголовками столбцов) и поместить в следующей строке запись – образец заполнения базы.
Цена покупки
После этого надо выделить обе строки и выбрать команду Данные-Форма. На экране появится форма данных, в которой слева перечислены все имена полей, справа в окнах – элементы первой записи.
После создания формы данных можно ее использовать для ввода в БД последующих записей, щелкнув на кнопке Добавить. В окне появится чистая форма данных, которую необходимо заполнить очередной записью. Перемещение по полям формы производится клавишей TAB. По завершении ввода записей щелкнуть по кнопке Закрыть.
После того как БД сформирована, можно с помощью формы данных найти нужную запись по заданному критерию, отредактировать ее поля или удалить.
Поиск записей.
Для того чтобы найти нужную запись в БД, надо воспользоваться кнопкой Критерии в форме данных.
В очищенные поля формы записываются критерии поиска. При их задании допускается использование операций сравнения = = . Задав критерии, нажать кнопку Далее. В полях основной формы выведутся данные из первой найденной записи, отвечающей заданным условиям. Снова нажимая кнопку Далее, можно просматривать каждую следующую запись, попадающую под условия отбора. Вернуться в основную форму можно, нажав кнопку Правка.
Сортировка записей.
Каждая база данных, которую создает пользователь, должна иметь некоторый предпочтительный порядок просмотра записей. В зависимости от целей можно, например, расположить записи по алфавиту фамилий клиентов, или в порядке возрастания их кодов, или в порядке убывания сумм их покупок и т. д.
Для сортировки записей необходимо в меню Данные выбрать команду Сортировка. При этом открывается диалоговое окно, в котором следует указать ключи сортировки (названия полей, определяющие новый порядок) и тип упорядочения (возрастание или убывание).
Если есть повторяющиеся значения в поле ключа, то можно определить более одного ключа сортировки (до трех полей). Такая сортировка называется многоуровневой.
Фильтры – средства, позволяющие просматривать только данные, которые удовлетворяют определенным условиям. Различают:
а) автофильтр;
б) усиленный фильтр.
Перед активизацией фильтра надо отметить область рабочего листа с данными, включая заголовки полей.
Автофильтр вызывается из меню Данные командой Фильтр-Автофильтр. При этом к каждой клетке с именем поля в строке заголовков добавляется раскрывающийся список. В списке строка Условие открывает диалог «Пользовательский автофильтр», в котором можно указать соединение нескольких критериев поиска.
Например:
Расширенный фильтр.
Для использования расширенного фильтра необходимо заранее определить область критериев поиска: для этого достаточно скопировать заголовки столбцов в ту часть рабочего листа, которая не содержит данных для поиска. Затем выбрать в меню Данные команду Фильтр-Расширенный фильтр, при этом открывается диалог, в котором можно задать:
Здесь же в группе Обработка следует указать, будет ли выполнена фильтрация на листе или выходные данные должны будут помещены в другую область рабочего листа.
Область критериев поиска может иметь вид:
Область критериев может занимать несколько столбцов или строк.
Создание сводной таблицы.
Сводная таблица – это таблица рабочего листа, позволяющая быстро суммировать большие объемы данных с применением выбранного формата и методов вычисления. При изменении исходных данных можно легко обновить сводную таблицу.
Изучение и первичное закрепление знаний;
Актуализация ведущих знаний;
Ввести понятия «табличный процессор» и «электронная таблица»;
Сформировать понятия: ячейка, строка, столбец, адрес ячейки, диапазон (блок ячеек), рабочий лист, книга.
Конспект урока:
Современные технологии обработки информации часто приводят к тому, что возникает необходимость представления данных в виде таблиц. В языках программирования для такого представления служат двухмерные массивы. Для табличных расчетов характерны относительно простые формулы, по которым производятся вычисления, и большие объемы исходных данных. Такого рода расчеты принято относить к разряду рутинных работ, для их выполнения следует использовать компьютер. Для этих целей созданы электронные таблицы (табличные процессоры) — прикладное программное обеспечение общего назначения, предназначенное для обработки различных данных, представимых в табличной форме.
Электронная таблица (ЭТ) позволяет хранить в табличной форме большое количество исходных данных, результатов, а также связей (алгебраических или логических соотношений) между ними. При изменении исходных данных все результаты автоматически пересчитываются и заносятся в таблицу. Электронные таблицы не только автоматизируют расчеты, но и являются эффективным средством моделирования различных вариантов и ситуаций. Меняя значения исходных данных, можно следить за изменением получаемых результатов и из множества вариантов решения задачи выбрать наиболее приемлемый.
При работе с табличными процессорами создаются документы, которые также называют электронными таблицами. Такие таблицы можно просматривать, изменять, записывать на носители внешней памяти для хранения, распечатывать на принтере.
Таблица – форма организации данных по строкам и столбцам.
Электронная таблица – компьютерный эквивалент обычной таблицы.
Табличный процессор – комплекс программ, предназначенных для создания и обработки электронных таблиц.
Электронная таблица – самая распространенная и мощная технология для профессиональной работы с данными. В ячейках таблицы могут быть записаны данные различных типов: текст, даты, числа, формулы и др. Главное достоинство электронной таблицы – возможность мгновенного автоматического пересчета всех данных, связанных формульными зависимостями, при изменении значения любого компонента таблицы.
Табличный процессор MS Excel позволяет:
Решать математические задачи: выполнять разнообразные табличные вычисления, вычислять значения функций, строить графики и диаграммы и т.п.;
Осуществлять численное исследование (Что будет, если? Как сделать, чтобы?);
Проводить статистический анализ;
Реализовать функции базы данных – ввод, поиск, сортировку, фильтрацию (отбор) и анализ данных;
Устанавливать защиту на отдельные фрагменты таблицы, делать их невидимыми;
Наглядно представлять данные в виде диаграмм и графиков;
Вводить и редактировать тексты;
Осуществлять обмен данными с другими программами, например, вставлять текст, рисунки, таблицы, подготовленные в других приложениях;
Осуществлять многотабличные связи.
Основные объекты табличного процессора MS Excel:
Ячейка – минимальный объект табличного процессора;
Строка – горизонтальный набор ячеек, заголовки столбцов – A, B, C,…,IV;
Столбец – вертикальны набор ячеек, заголовки строк – 1, 2, 3,…65536;
Адрес ячейки – определяется пересечением столбца и строки (A1, F123, AC72);
Указатель ячейки – рамка;
Активная ячейка – выделенная рамкой, с ней можно производить какие-либо операции;
Смежные ячейки – ячейки расположенные последовательно;
Диапазон (блок) ячеек – выделенные смежные ячейки, образующие прямоугольный участок таблицы;
Адрес диапазона (блока) ячеек - определяется адресом верхней левой и нижней правой ячейки, разделенных двоеточием ( : ), B2 : C7 → B2, B3, B4, B5, B6, B7, C2, C3, C4, C5, C6, C7.
Книга – документ электронной таблицы, состоящий из листов, объединенных одним именем и являющихся файлом;
Лист – рабочее поле, состоящее из ячеек.
При работе с табличными процессорами создаются документы, которые можно просматривать, изменять, записывать на носители внешней памяти для хранения, распечатывать на принтере. Режим формирования электронных таблиц предполагает заполнение и редактирование документа. При этом используются команды, изменяющие содержимое клеток (очистить, редактировать, копировать), и команды, изменяющие структуру таблицы (удалить, вставить, переместить).
Режим управления вычислениями. Все вычисления начинаются с ячейки, расположенной на пересечении первой строки и первого столбца электронной таблицы. Вычисления проводятся в естественном порядке, т.е. если в очередной ячейке находится формула, включающая адрес еще не вычисленной ячейки, то вычисления по этой формуле откладываются до тех пор, пока значение в ячейке, от которого зависит формула, не будет определено. При каждом вводе нового значения в ячейку документ пересчитывается заново, — выполняется автоматический пересчет. В большинстве табличных процессоров существует возможность установки ручного пересчета, т.е. таблица пересчитывается заново только при подаче специальной команды.
Режим отображения формул задает индикацию содержимого клеток на экране. Обычно этот режим выключен, и на экране отображаются значения, вычисленные на основании содержимого клеток.
Графический режим дает возможность отображать числовую информацию в графическом виде: диаграммы и графики. Это позволяет считать электронные таблицы полезным инструментом автоматизации инженерной, административной и научной деятельности.
В современных табличных процессорах, например, в Microsoft Excel, в качестве базы данных можно использовать список (набор строк таблицы, содержащий связанные данные). При выполнении обычных операций с данными, например, при поиске, сортировке или обработке данных, списки автоматически распознаются как базы данных. Перечисленные ниже элементы списков учитываются при организации данных:
• столбцы списков становятся полями базы данных;
• заголовки столбцов становятся именами полей базы данных;
• каждая строка списка преобразуется в запись данных.
Ячейки рабочего листа электронной таблицы могут содержать:
исходные или первичные данные – константы;
производные данные, которые рассчитываются с помощью формул или функций.
Данные в ячейках таблицы могут относиться к одному из следующих типов: текст, числа, даты, формулы и функции.
Текст – последовательность букв, иногда цифр или некоторых специальных символов.
Числа могут включать цифры и различные символы: знак процента, знак мантиссы, круглые скобки, денежные обозначения, разделители и др. Например: 5; 3,14.
Дата и время вводятся в ячейки электронной таблицы как числа и выравниваются по правому краю.
Формулой в электронной таблице называют алгебраические и логические выражения. Формулы всегда начинаются со знака равенства (=) и вводятся в латинском регистре. Например: =А5*2/В1
Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов. Функции могут вводиться в таблицу в составе формул либо отдельно. Например, функция суммирования имеет вид =СУММ(А1:А2)
Аргументами функции могут быть: числа; ссылки на ячейки и диапазоны ячеек; имена; текст; другие функции; логические значения и др.
MS Excel содержит более 400 встроенных функций. Имена функций можно набирать в любом регистре – верхнем или нижнем. Для облегчения работы с встроенными функциями используется Мастер функций.
В формулах используются следующие знаки арифметических операций:
возведение в степень ^
Для изменения порядка арифметических действий используются круглые скобки.
Форматированием называется изменение внешнего оформления таблиц и данных в них.
Важно! Excel различает форматирование всей ячейки и форматирование содержимого ячейки.
К форматированию ячеек относится: изменение шрифта содержимого ячеек, выравнивание данных в ячейках, представление чисел в разных форматах, оформление границ ячеек, и т.д. Для того чтобы изменить формат ячейки необходимо щелкнуть на ней и выполнить команду Формат—Ячейки . Появившееся диалоговое окно Формат Ячеек, позволит изменить формат всей ячейки.
Для выравнивания данных следует:
щелкнуть на форматируемой ячейке или выделить диапазон ячеек, содержащих выравниваемый текст;
выполнить команду Формат—Ячейки. На закладке Выравнивание установить опцию Переносить по словам , которая позволяет располагать текст одной ячейки в несколько строк.
Для оформления предварительно выделенных ячеек с помощью рамок следует выполнить команду Формат—Ячейки. На закладке Граница следует выбрать тип линии для рамки, а затем щелкнуть на кнопке с нужным видом рамки. (Возможно поочередное назначение нескольких видов рамок).
Для назначения цветного фона предварительно выделенным ячейкам необходимо выполнить команду Формат—Ячейки. На закладке Вид выбрать цвет фона ячеек и нажать кнопку ОК.
Для изменения шрифта предварительно выделенного текста или числа, находящихся в нескольких ячейках, необходимо выполнить команду Формат—Ячейки. На закладке Шрифт следует указать тип шрифта (Arial, Times New Roman и т. д.), начертание (жирный, курсив, подчеркнутый), размер шрифта, цвет и т.д.
Для изменения формата чисел, находящихся в выделенном диапазоне ячеек необходимо выполнить команду Формат—Ячейки. С помощью закладки Число можно установить один из следующих форматов чисел: денежный, процентный, дата, текстовый и т.д.
Представление числа 100 в разных форматах
Без использования специальных средств. Текст выравнивается по левому краю, число по правому.
Числовой с двумя знаками после запятой
Числовой формат позволяет отображать любое количество знаков после запятой с соблюдением правил округления
Денежный (в рублях)
Знак денежной единицы («р.» и «) отображается только в ячейке, в строке редактирования (формул) он отсутствует.
Денежный (в долларах)
MS Excel хранит даты в виде последовательных чисел. По умолчанию дате 1 января 1900 года соответствует порядковый номер 1, а 1 января 2008 года – 39448.
В процентном формате число умножается на 100%
E+02 означает 10 во 2-ой степени
Текстовый формат используется для ввода чисел начинающихся с 0.
При проведении расчетов в электронной таблице Microsoft Excel иногда в ячейках вместо ожидаемых значений выводятся различные «непонятные» результаты. Сегодня мы попытаемся сделать их для нас понятными.
ширина столбца недостаточна для размещения численного значения, для которого установлен формат Числовой, Денежный или Финансовый. Такая ситуация встречается, например, в ячейке, в которую скопирована формула из другой ячейки, для которой был установлен один из перечисленных форматов. Для исправления ошибки необходимо либо расширить ячейку, либо изменить формат представления данных в ней;
ячейка содержит формулу, возвращающую некорректную дату или время. Excel, например, не поддерживает даты до 1900 года или отрицательные значения времени. Увеличение ширины ячейки данную ошибку не исправляет.
О том, что делить на ноль нельзя, знают даже учащиеся начальных классов. Но тем не менее такая ситуация в Excel встречается довольно часто. Например, на листе, подготовленном для решения линейного уравнения вида ax + b = 0:
Как известно, в Excel можно использовать в формулах не только адреса отдельных ячеек или диапазонов, но и их имена. Это облегчает понимание формул. Например, вы согласитесь, что формула нахождения корня линейного уравнения, оформленная в виде:
=ЕСЛИ(a=0; ««; –b/a)
— где a и b — имена ячеек, в которых задаются значения коэффициентов уравнения a и b соответственно, выглядит гораздо понятнее приведенного ранее варианта.
Ошибка Имя? возникает в следующих случаях:
формула содержит неопределенное имя ячейки или диапазона. Здесь же следует отметить одну особенность Excel. Если удалить имя какой-то ячейки или какого-то диапазона, используемое в формуле, то в самой формуле оно останется (казалось бы, Excel мог бы преобразовать имена в ссылки на соответствующие ячейки, однако этого почему-то не происходит);
формула содержит текст, который Excel интерпретирует как неопределенное имя. Например, ошибка в написании имени функции приводит к отображению ошибки Имя?.
Ошибка весьма распространена и встречается, как правило, в следующих ситуациях.
Использование функции СУММ позволит найти сумму числовых значений в диапазоне, в котором имеются и текстовые значения: =СУММ(D3:D6). Это означает, что в ячейке B7 можно записать формулу =СУММ(B3:B6) и распространить (скопировать) ее на ячейки C7 и D7.
В качестве аргументов функции использованы данные несоответствующего типа (например, в функции ЕСЛИ вместо условия использовано число или текст).
В качестве аргумента функции используется диапазон, тогда как аргументом должна быть отдельная ячейка. Пример ошибочного оформления: =КОРЕНЬ (A3:A6).
1) для функции, использующей числовой аргумент, задан аргумент другого типа. Пример ошибочного оформления: =КОРЕНЬ(И3);
2) формула содержит слишком большое или слишком маленькое значение. Excel поддерживает величины в пределах от 1Е-307 до 1Е307 (от 10-307 до 10307).
Содержимое ячейки с формулой было вырезано (командой Правка — Вырезать) и затем вставлено в ячейку, на которую ссылается формула.
01.12.2008
Макарова Н.В. Программа по информатике (системно-информационная концепция). К комплекту учебников по информатике 5-11 класс. Санкт-Петербург: Питер.2000г.
Информатика. 5-11 класс. /Под ред. Н.В. Макаровой. – СПб.: Питер, 2001
Коляда М.Г. Окно в удивительный мир информатики. ИКФ «Сталкер»,1997
Шафрин Ю.А. Основы компьютерной технологии. Учебное пособие для 7 – 11 классов по курсу «Информатика и вычислительная техника» - Москва: ABF,1996
Ефимова О.В., Моисеева М.В., Ю.А. Шафрин Практикум по компьютерной технологии. Примеры и упражнения. Пособие по курсу «Информатика и вычислительная техника» - Москва: ABF,1997
Горячев А., Шафрин Ю. Практикум по информационным технологиям. М.: Лаборатория базовых знаний, 2001
Семакин И.Г., Шеина Т.Ю. Преподавание курса информатики в средней школе. М.: Лаборатория базовых знаний, 2002
Симонович С.В., Евсеев Г.А. Практическая информатика. Учебное пособие для средней школы. Универсальный курс. – Москва: АСТ-ПРЕСС: Информ-Пресс, 1998
Симонович С.В. Компьютер в вашей школе. М.: АСТ-ПРЕСС: Информком-Пресс, 2001
Симонович С.В., Евсеев Г.А. Занимательный компьютер. Книга для детей, учителей и родителей. Москва: АСТ-ПРЕСС: Информком-Пресс, 2002
Работа с таблицей как с базой данных Подготовила преподаватель Монгуш М.Л.
Цели занятия Познакомиться с базами данных в Excel Научиться создавать базу данных в программе Excel Научиться обрабатывать базу данных
План Основные понятия Правила ввода данных в базу данных в среде Excel Способы ввода данных в списки Работа со списками
Основные понятия База данных – это средство хранения, упорядочения и поиска информации . В базе данных ячейка – это поле . Ячейки одной строки представляют собой запись . Заголовки столбцов называются Именами полей . Базу данных в электронной таблице называют списком .
Правила ввода информации в базы данных в Excel Имена полей : вводить имена полей необходимо в первую строку. При этом не должно быть пустых строк между строкой заголовков и первой записью. имя поля (заголовок столбца) может состоять из нескольких слов, но обязательно размещенных в одной ячейке Записи : каждая запись вводится в отдельную строку. Между записями не должно быть незаполненных строк. Один тип : информация в ячейках одного столбца должна быть однотипна.
Способы ввода данных Списки можно вводить как в режиме таблицы , так и при помощи формы . Форма - это окно диалога, предназначенное для удобного ввода, удаления и поиска данных в списках. Для создания полей в форме Excel использует заголовки списка. Для отображения формы на экране необходимо выбрать Данные→Форма .
Ф ункции при работе со списком фильтрация , автофильтрация сортировка . В первом случае можно вывести строки, удовлетворяющие некоторому критерию. Сортировка – это упорядочение списка по значениям одного или нескольких полей . (По алфавиту, например).
Выполнение сортировки : выделить любую ячейку списка; выполнить команду Данные – Сортировка ; указать ключи и порядок сортировки в ДО; нажать ОК.
Фильтрация записей: выделить любую ячейку списка; выполнить команду Данные – Фильтр
Эталон к ПР 10.2 (сверяем с выписанными ответами в тетрадях) 2. Германия, Норвегия, Швеция, Испания, Франция 3. Албания, Латвия, Македония, Словения, Эстония 6. Германия, Италия, Великобритания, Франция Критерии оценивания: 1 . ПР 10.1, № ПР №10.2 – правильно выполнены пункты 1-2 – «оценка 3», 2. ПР 10.1, № ПР №10.2 – правильно выполнены пункты 1-4 – «оценка 4» 3. ПР 10.1, № ПР №10.2 – правильно выполнены пункты 1-5 и дополнительно пункт 6 – «оценка 5»
Закончите предложения - я сегодня узнал … - я сегодня научился … - мне на уроке было интересно… - мне было сложно …
Читайте также: