Какими характеристиками обладает список данных в excel
База данных (БД)– это совокупность данных, организованных по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования данными. Базы данных предназначены для хранения и обработки всевозможной информации и создаются во всех областях деятельности человека, например, библиотечные каталоги, сведения о сотрудниках какого-либо учреждения, телефонные справочники и т.д. База данных, созданная на персональном компьютере, сохраняется на диске и обрабатывается средствамиСУБД.
Система управления базами данных (СУБД)– это комплекс программ и языковых средств, предназначенных для создания, ведения и использования баз данных. Системы управления базами данных на персональных компьютерах поддерживают в основномреляционную модель данных.
Реляционная модель данных– это множество таблиц-отношений (relation– отношение), к которым применимы операцииреляционной алгебры(алгебры отношений).Реляционная алгебра– это математический аппарат для выполнения действий над таблицами-отношениями, включающий операции объединения, пересечения, сложения, вычитания и другие.Реляционная база данных– это база, в которой реализована реляционная модель данных. Применение реляционной алгебры в СУБД позволяет создавать базы данных из нескольких взаимосвязанных таблиц, выполнять различные операции над ними, организовывать связи между таблицами.
Файловая модель данных – это совокупность не связанных между собой (независимых) файлов, состоящих из однотипных записей с линейной (одноуровневой) структурой. Такая модель базы данных разрабатывается для конкретной предметной области и представляется в видесписка. Примером такой модели является база данных вExcel.
База данных в Excel – этосписок, хранимый в одной таблице и представляющий собой непрерывный диапазон ячеек (строк и столбцов).
Таким образом, отличие реляционной БД от списка Excelзаключается в следующем:
в реляционной БД основным объектом действий является таблица, а в спискеExcelосновной структурной единицей являетсязапись;
в реляционной БД информация хранится и обрабатывается в нескольких взаимосвязанных таблицах, в то время как данные списка помещаются и обрабатываются в одной таблице.
13.2. Основные понятия базы данных (списка) в Excel
В Excelлюбая база данных представлена в виде таблицы, состоящей из одного или более столбцов. «Академическим» примером списка, с которым приходилось сталкиваться многим из вас, является библиотечная картотека.
Список (база данных)– это таблица на рабочем листе, состоящая из строк данных и организованная по принципу базы данных. Простейший списокExcelсостоит иззаписейоб объектах (аналогом записи в библиотеке является карточка, содержащая информацию о конкретной книге).
Запись – это строка базы данных, включающая совокупность полей, соответствующая логически связанным реквизитам. Каждая запись (строка) содержит информацию оботдельном объектебазы данных. Запись, в свою очередь, делится на поля. Одни и те же поля для различных записей предназначаются для данных одного типа.
Поле – это столбец таблицы, элементами которого являются однотипные данные (реквизиты). Таким образом, каждый столбец базы данных является полем. Столбцам присваиваются уникальныеимена полей, которые заносятся в первую строку списка. Эта строка называетсястрокой заголовков.
Имя поля – это уникальный заголовок поля столбца базы данных. Каждое имя поля должно помещаться вотдельной ячейке. Все имена полей должны находиться в ячейкаходной и той же строки над данными списка.
Диапазон базы данных – это диапазон ячеек, занимающий часть рабочего листа и содержащий записи базы данных. Этот диапазон ячеек включаетстроку заголовкаистроки записей.
Диапазон критериев – это область на рабочем листе, в которой задаются критерии поиска информации. Здесь должны быть указаныимена полейи отведена область (диапазон ячеек) для записиусловий отбора.
Диапазон вывода –это область рабочего листа, в которуюExcelвыводит (копирует) выбранные из списка данные. Этот диапазон должен быть расположен на том же листе, что и список.
Лучше и удобнее всегда именоватьдиапазоны. Если каким-либо диапазонам на листе присваиваютсяимена(например,Критерии,База данных,Вывод), то по умолчаниюExcelсчитает их задающим некоторый список.
Основные правила создания списка
Перед построением списка продумайте, как расположить его относительной других данных на рабочем листе, относительно других списков в рабочей книге и т.п.
На отдельном рабочем листе можно создать только одну базу данных (список).
Не вставляйте в базу данных (список) пустые строки. Если пустая строка вставлена междустрокой заголовков(именами полей) иданными списка, тоExcelне определит имена полей. Пустые строкимежду записямивоспринимаютсяExcelкакконец базы данных.
При вводе данных любого типа (текст, число и т.д.) не допускайте пробелов ни в начале, ни в любой другой позиции вводимых данных.
Над списком всегда располагайте строку заголовков с уникальным именем для каждого поля и вводите ее в ячейки только одной(первой) строки базы данных. Имена, занимающие несколько строк, не распознаютсяExcel.
Имена полей должны удовлетворять следующим требованиям:
- их длина не должна превышать 255 символов;
- в качестве имен полей используются только названия непосредственно над данными;
- для правильной работы фильтра, имена полей должны быть уникальными, т.е. не должны совпадать друг с другом;
- имена полей должны отличаться от других данных списка форматом символов, размером или начертанием (полужирным, курсивом и т.д.);
- в качестве имен полей нельзя использовать даты, формулы или пустые ячейки.
Полезно располагать базу данных (список) так, чтобы слева и справабыл хотя бы одинпустой столбец, асверху (над заголовками) и снизу–пустая строка. В этом случае, по одной выделенной ячейкеExcelможет автоматически определить весь список.
Необходимо предусмотреть на рабочем листе достаточное пространство для расширения списка, т.е. возможности ввода новых записей, полей и элементов базы данных, т.к. в противном случае Excelне позволит сделать это.
Теоретически размер базы данных в Excelограничен размерами рабочего листа (65536 строк и 256 столбцов), но практическиExcelне обработает такой объем информации. В этом случае воспользуйтесь средствами других программ, например,MSAccess.
Основные правила подготовки информации для создания базы данных
Для решения конкретных задач список Excelпредставляет собой удобную и эффективную форму работы с данными. Пользователь может создать вExcelбазу данных, отвечающую его интересам, и организовать профессиональную работу со своей информацией. Но прежде чем вводить информацию в компьютер, необходимо хорошо продумать и спланировать будущую базу данных, соблюдая следующие правила.
- Обдумайте содержание вашей базы данных, ее назначение, название, размеры, расположение на рабочем листе.
- Определите содержание и все необходимые элементы (реквизиты) записей, для того чтобы запись наиболее полно отражала содержание БД.
- Определите, число полей, количество записей в БД, ширину поля для каждого элемента записи.
- Имена полей должны быть точными, короткими и понятными. Для удобства использования БД определите, в каком порядке должны располагаться имена полей в строке заголовков.
- Не вводите непонятные или не принятые сокращения слов.
- Спланируйте внешний вид базы данных так, чтобы ее легко читать. Не применяйте слишком много шрифтов и расцветок.
Основные операции при работе с базой данных (списком)
Для управления информацией, хранящейся в списке, и ее анализа Excelсодержит специальные встроенные средства. Создав базу данных, можно выполнять такие операции по ее обработке, каксортировка,фильтрация,подведение промежуточных итогови др. Все операции по обработке базы данных (списка) вExcelвыполняются через менюДанные.
Сортировка базы данных (списка). Сортировка списка– это организация данных, при которой данные списка располагаются в алфавитном, числовом или хронологическом порядке. При этом порядок сортировки задаетсяпо возрастаниюилипо убыванию. Сортировка может быть простой – по одному полю, или сложной – по нескольким полям. За один раз сортировку можно провести не более чем по трем полям. Кроме того, пользователь может задать особый порядок сортировки – не по возрастанию или убыванию, а в соответствии с собственным списком.Excelможет сортировать строки списков и баз данных, а также столбцы рабочих листов.
Для выполнения сортировки базы данных (списка)необходимо выполнить следующие действия:
Выделить диапазон ячеек, который необходимо отсортировать. Если необходимо отсортировать список целиком, а он со всех сторон ограничен пустыми ячейками, то достаточно выделить любую ячейку списка.
Выполнить команду Данные → Сортировка…Откроется диалоговое окноСортировка диапазона. В этом окне можно задатьключи сортировкиипорядок сортировки.
Убедитесь, что в поле Идентифицироватьпоактивизирована опцияподписям (первая строка диапазона).
В диалоговом окне Сортировкадиапазонаоткрыть спускающийся список в полеСортировать пои выбрать в нем имя поля, по которому будете выполнять сортировку.
В этом же поле выбрать порядок сортировки, активизируя опцию по возрастаниюилипо убыванию.
Щелкнуть на кнопке ОК.
Записи в списке будут упорядочены в соответствии с заданными параметрами. При этом содержание записей не изменится.
Поле Сортировать поназывается первым ключом сортировки, полеЗатем по– вторым ключом и полеВ последнюю очередь, по- третьим ключом сортировки. Второе поле сортировки используется, если возникает повторение значения первого поля, а третье поле – если повторяются значения и первого, и второго поля. Для выполнения сортировки по второму и третьему ключу необходимо повторить пункты 4 и 5 в описанной выше последовательности действий.
Сортировку следует выполнять осторожно. Если выделить несколько ячеек, то будут отсортированы данные только этих ячеек, поэтому при сортировке выделяйте только одну ячейку нужного поля.
Если необходимо упорядочить данные только по одному полю, то можно воспользоваться кнопками наСтандартной панели инструментов. Для этого необходимо:
Выделить ячейку в столбце, который следует использовать в качестве ключа сортировки.
Нажать либо кнопку Сортировка по возрастанию (АЯ↓), либоСортировка по убыванию (ЯА↓).
Чтобы отменитьрезультат сортировки списка, необходимо выполнить командуПравка → Отменитьили щелкнуть на кнопкеОтменитьнаСтандартной панели инструментов.
Фильтрация базы данных (списка).Фильтрацияданных в списке – это отображение записей базы данных, соответствующих определеннымкритериям.КритерийвExcel– это ссылка на диапазон ячеек, задающий условия отбора или поиска данных. Для выполнения фильтрации данных вExcelиспользуютсяАвтофильтриРасширенный фильтр.
Автофильтр. Операция Автофильтра позволяет производить отбор записей непосредственно в рабочем листе. С помощью элементарных действий мышью можно быстро отфильтровать данные, оставив на экране только то, что необходимо видеть или распечатать.Автофильтрвыводит информацию на рабочем листе, при этом записи, не удовлетворяющие заданному критерию, скрыты.Автофильтрпомещает в имена полей раскрывающиеся списки, из которых можно выбрать значения полей или задать пользовательский критерий.
Для выполнения операции Автофильтрнеобходимо выполнить следующие действия:
Выделить любую ячейку в базе данных (списке) или выделить базу данных целиком.
Выполнить команду Данные → Фильтр → Автофильтр. В правой нижней части ячеекстроки заголовков(с именами полей) появятсякнопки-стрелки, щелкнув на которых открываются списки с элементами соответствующего поля.
Раскрыть список, соответствующий полю, которое следует включить в критерий. В качестве критерия можно использовать любой элемент списка, т.е. содержимое любой ячейки, кроме заголовков столбцов.
Выбрать щелчком мыши нужный элемент-критерийиз списка. РезультатыАвтофильтрабудут моментально отображены. Не соответствующие критерию записи будут скрыты.
Чтобы восстановитьвсе записи списка щелкните накнопке-стрелкев строке заголовков и в раскрывшемся списке выберите командуВсе.
При использовании операции Автофильтраможно задаватьпользовательские критериидля фильтрации данных.Пользовательский автофильтр предоставляет возможность задавать комплексные критерии, объединяя их с помощьюлогических условийИиИЛИ. Для выполнения пользовательского Автофильтра необходимо в раскрывающемся списке выбрать элементУсловие…, а затем в открывшемся диалоговом окнеПользовательский Автофильтрзадать пользовательские критерии.
Для отменырежимаАвтофильтранеобходимо выполнить командуДанные → Фильтр → Автофильтр(т.е. снять галочку), при этом список в рабочем листе примет обычный вид, т.е. исчезнут кнопки со стрелками в строке заголовков и отобразятся все записи списка.
Расширенный фильтр. Расширенный фильтртребует большей работы, чемАвтофильтр, однако возможности поиска и фильтрации шире. Кроме того, отфильтрованные данные могут быть скопированы в отдельный заранее обозначенныйдиапазон вывода.
Для выполнения Расширенного фильтранеобходимо создатьдиапазон критериев, где задаются условия поиска данных, идиапазон вывода, в который выводятся результаты. Верхняя строка этих диапазонов должна содержать заголовки полей, которыев точностиповторяют по написанию заголовки полей в базе данных. Поэтому при создании диапазоновкритериевивыводаточность совпадения имен полей можно обеспечить путемкопированиясоответствующих заголовков полей из базы данных (списка). Не требуется включать все имеющиеся заголовки и сохранять их порядок. Диапазон критериев и сами критерии, а также диапазон вывода задаются пользователем перед выполнениемРасширенного фильтра.
Для выполнения Расширенного фильтранеобходимо:
Создать диапазон критериев.
Создать диапазон вывода.
Выделить ячейку внутри списка или выделить список целиком, если он соприкасается с другими данными.
Выполнить команду Данные → Фильтр → Расширенный фильтр…Откроется диалоговое окноРасширенный фильтр.
В поле Обработкавыбрать опциюскопировать результат в другое место, тогда список останется нетронутым, а отобранные записи будут помещены в указанный диапазон вывода.
Щелкнуть кнопку свертывания окна в поле Исходный диапазон:и выделитьдиапазон списка(или убедиться в том, что диапазон выделен правильно).
Щелкнуть кнопку свертывания окна в поле Диапазон условий:и выделитьдиапазонкритериев.
Щелкнуть кнопку свертывания окна в поле Поместить результат в диапазон:и выделитьдиапазонвыводарезультатов.
Щелкнуть кнопку ОК. В результате работыРасширенного фильтрав диапазон вывода будут помещены записи, отвечающие условию, заданному в диапазоне критериев.
Применяя Расширенный фильтр, в диапазоне критериев можно ввести несколько условий, как в строке, так и в столбце. Для этого в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, как объект математических вычислений. Это информация в первую очередь для пользователя, а не для программы. Текстом могут являться любые символы, включая цифры, если они соответствующим образом отформатированы. В языке DAX этот вид данных относится к строчным значениям. Максимальная длина текста составляет 268435456 символов в одной ячейке.
Для ввода символьного выражения нужно выделить ячейку текстового или общего формата, в которой оно будет храниться, и набрать текст с клавиатуры. Если длина текстового выражения выходит за визуальные границы ячейки, то оно накладывается поверх соседних, хотя физически продолжает храниться в исходной ячейке.
Числовые данные
Для непосредственных вычислений используются числовые данные. Именно с ними Excel предпринимает различные математические операции (сложение, вычитание, умножение, деление, возведение в степень, извлечение корня и т.д.). Этот тип данных предназначен исключительно для записи чисел, но может содержать и вспомогательные символы (%, $ и др.). В отношении его можно использовать несколько видов форматов:
- Собственно числовой;
- Процентный;
- Денежный;
- Финансовый;
- Дробный;
- Экспоненциальный.
Кроме того, в Excel имеется возможность разбивать числа на разряды, и определять количество цифр после запятой (в дробных числах).
Ввод числовых данных производится таким же способом, как и текстовых значений, о которых мы говорили выше.
Дата и время
Ещё одним типом данных является формат времени и даты. Это как раз тот случай, когда типы данных и форматы совпадают. Он характеризуется тем, что с его помощью можно указывать на листе и проводить расчеты с датами и временем. Примечательно, что при вычислениях этот тип данных принимает сутки за единицу. Причем это касается не только дат, но и времени. Например, 12:30 рассматривается программой, как 0,52083 суток, а уже потом выводится в ячейку в привычном для пользователя виде.
Существует несколько видов форматирования для времени:
- ч:мм:сс;
- ч:мм;
- ч:мм:сс AM/PM;
- ч:мм AM/PM и др.
Аналогичная ситуация обстоит и с датами:
Есть и комбинированные форматы даты и времени, например ДД:ММ:ГГГГ ч:мм.
Также нужно учесть, что программа отображает как даты только значения, начиная с 01.01.1900.
Логические данные
Довольно интересным является тип логических данных. Он оперирует всего двумя значениями: «ИСТИНА» и «ЛОЖЬ». Если утрировать, то это означает «событие настало» и «событие не настало». Функции, обрабатывая содержимое ячеек, которые содержат логические данные, производят те или иные вычисления.
Ошибочные значения
Отдельным типом данных являются ошибочные значения. В большинстве случаев они появляются, когда производится некорректная операция. Например, к таким некорректным операциям относится деление на ноль или введение функции без соблюдения её синтаксиса. Среди ошибочных значений выделяют следующие:
Формулы
Отдельной большой группой видов данных являются формулы. В отличие от констант, они, чаще всего, сами не видны в ячейках, а только выводят результат, который может меняться, в зависимости от изменения аргументов. В частности, формулы применяются для различных математических вычислений. Саму формулу можно увидеть в строке формул, выделив ту ячейку, в которой она содержится.
Обязательным условием, чтобы программа воспринимала выражение, как формулу, является наличие перед ним знака равно (=).
Формулы могут содержать в себе ссылки на другие ячейки, но это не обязательное условие.
Отдельным видом формул являются функции. Это своеобразные подпрограммы, которые содержат установленный набор аргументов и обрабатывают их по определенному алгоритму. Функции можно вводить вручную в ячейку, поставив в ней предварительно знак «=», а можно использовать для этих целей специальную графическую оболочку Мастер функций, который содержит весь перечень доступных в программе операторов, разбитых на категории.
С помощью Мастера функций можно совершить переход к окну аргумента конкретного оператора. В его поля вводятся данные или ссылки на ячейки, в которых эти данные содержатся. После нажатия на кнопку «OK» происходит выполнение заданной операции.
Как видим, в программе Excel существует две основные группы типов данных: константы и формулы. Они, в свою очередь делятся, на множество других видов. Каждый тип данных имеет свои свойства, с учетом которых программа обрабатывает их. Овладение умением распознавать и правильно работать с различными типами данных – это первоочередная задача любого пользователя, который желает научиться эффективно использовать Эксель по назначению.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Выпадающий список в Excel это, пожалуй, один из самых удобных способов работы с данными. Использовать их вы можете как при заполнении форм, так и создавая дашборды и объемные таблицы. Выпадающие списки часто используют в приложениях на смартфонах, веб-сайтах. Они интуитивно понятны рядовому пользователю.
Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:
Видеоурок
Как создать выпадающий список в Экселе на основе данных из перечня
Представим, что у нас есть перечень фруктов:
Для создания выпадающего списка нам потребуется сделать следующие шаги:
Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2 ), а не относительными (например, A2 или A$2 или $A2 ).
Как сделать выпадающий список в Excel используя ручной ввод данных
На примере выше, мы вводили список данных для выпадающего списка путем выделения диапазона ячеек. Помимо этого способа, вы можете вводить данные для создания выпадающего списка вручную (необязательно их хранить в каких-либо ячейках).
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ
Наряду со способами описанными выше, вы также можете использовать формулу СМЕЩ для создания выпадающих списков.
Например, у нас есть список с перечнем фруктов:
Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:
Система создаст выпадающий список с перечнем фруктов.
Как эта формула работает?
На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).
Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.
Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)
Если вы используете для создания списка формулу СМЕЩ на примере выше, то вы создаете список данных, зафиксированный в определенном диапазоне ячеек. Если вы захотите добавить какое-либо значение в качестве элемента списка, вам придется корректировать формулу вручную. Ниже вы узнаете, как делать динамический выпадающий список, в который будут автоматически загружаться новые данные для отображения.
Для создания списка потребуется:
Примечание: для корректной работы формулы, важно, чтобы в списке данных для отображения в выпадающем меню не было пустых строк.
Как создать выпадающий список в Excel с автоматической подстановкой данных
Для того чтобы в созданный вами выпадающий список автоматически подгружались новые данные, нужно проделать следующие действия:
- Создаем список данных для отображения в выпадающем списке. В нашем случае это список цветов. Выделяем перечень левой кнопкой мыши:
- Из раскрывающегося меню выбираем стиль оформления таблицы:
Таблица с данными готова, теперь можем создавать выпадающий список. Для этого необходимо:
- Готово! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:
- Таблица автоматически расширит свой диапазон данных. Выпадающий список соответственно пополнится новым значением из таблицы:
Как скопировать выпадающий список в Excel
В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6 .
Для того чтобы скопировать выпадающий список с текущим форматированием:
- нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
- нажмите сочетание клавиш на клавиатуре CTRL+C ;
- выделите ячейки в диапазоне А2:А6 , в которые вы хотите вставить выпадающий список;
- нажмите сочетание клавиш на клавиатуре CTRL+V .
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Как выделить все ячейки, содержащие выпадающий список в Экселе
Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:
Как сделать зависимые выпадающие списки в Excel
Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
Предположим, что у нас есть списки городов двух стран Россия и США:
Для создания зависимого выпадающего списка нам потребуется:
Теперь, для создания зависимого выпадающего списка:
- В разделе “Источник” укажите ссылку: =INDIRECT(D2) или =ДВССЫЛ(D2);
Читайте также: