Заполнение разъединенных ячеек

Подготовила А.А. Коробецкая

Содержание

Организация базы данных в Excel 3

Заполнение разъединенных ячеек. 3

Пример 1. Зарплата. 4

Пример 2. Поквартальные финансовые результаты.. 4

Пример 3. Музыкальные альбомы.. 6

Имена диапазонов ячеек. 8

Выделение зависимостей. 10

Вспомогательные таблицы.. 10

Пример 4. Поставки товаров. 11

Проверка и форматы данных. 14

Список с выбором значения. 17

Проверка типа данных. 18

Проверка уникальности значения в столбце. 20

Функции баз данных. 21

Критерии. 21

Функции. 21

Пример 4 (продолжение) 22

Защита листа. 25

Задание. 28

Задание 1. Организация таблиц. 28

Задание 2. Проверка данных. 28

Задание 3. Запросы к базе данных. 28

Задание 4. Защита листов. 28

Задание 5. Дополнительное. 29

Варианты заданий. 30

Вариант 1. Успеваемость студентов. 30

Вариант 2. Управление проектами. 32

Вариант 3. Продажи товаров. 35

Вариант 4. Аренда торговых площадей. 39

Вариант 5. Статистика продаж автомобилей. 42

Вариант 6. Успеваемость студентов. 42

Вариант 7. Управление проектами. 44

Вариант 8. Продажи товаров. 48

Вариант 9. Аренда торговых площадей. 52

Вариант 10. Статистика продаж автомобилей. 54

Организация базы данных в Excel

Хотя Excel не является СУБД, в нем можно организовать хранение и обработку, близкие к базам данных. У такого подхода есть как преимущества, так и недостатки.

Преимущества	Недостатки
+ проще создавать и заполнять таблицы + нагляднее, результат видно сразу + более гибкий подход, легко вносить изменения	− сложно объединять большое количество таблиц − сложно организовать одновременную работу нескольких пользователей − работает медленнее, чем СУБД, особенно на больших таблицах − автоматизация работы только через макросы, а это небезопасно − меньше средств автозаполнения и контроля данных

 

Вывод: Excel подойдет для создания небольших локальных баз данных.

Таблицы для организации БД должны отвечать определенным требованиям:

1. У каждого столбца в таблице должен быть уникальный заголовок. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.

2. Таблица должна быть непрерывной, т.е. в ней не должно быть пустых строк и столбцов, а также объединенных ячеек.

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

4. В каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа (суммы, кол-во) – значит, должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить прочерк. Вместо этого необходимо ставить 0. При необходимости, используйте поиск и замену неверных значений. Помните, по умолчанию текст выравнивается по левому краю, а числа – по правому.

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

Если у вас уже есть готовые таблицы, которые нужно организовать в формате базы данных, то нужно преобразовать их по этим правилам.

Заполнение разъединенных ячеек

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

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

Чаще всего объединенные ячейки встречаются в заголовках столбцов.

Пример 1. Зарплата

Дана таблица с расчетом заработной платы сотрудников.

После разъединения ячеек таблица примет вид:

В данном примере заголовки во второй строке достаточно информативны. Заголовок «Заработная плата» можно просто удалить, а «Табельный номер» и «ФИО сотрудника» перенести во вторую строку.

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

Пример 2. Поквартальные финансовые результаты

Финансовые результаты компании по кварталам записаны в строку, а заголовками столбцов являются годы и кварталы. У первого столбца заголовка нет совсем.

В данной таблице заголовки столбцов нужно сформировать из обеих строк, например, «2015-1», «2015-2» и т.д.

Для этого можно использовать функцию СЦЕПИТЬ. Но напрямую ее применение не даст желаемого результата:

Дело в том, что в объединенной ячейке только первая считается заполненной, а остальные – пустыми. Т.е. 2015 год записан в ячейке B1, а в C1:E1 ничего нет.

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

В формуле ячейке B3 зафиксируем ячейку B1 с 2015 годом и заполним ее до столбца E включительно.

Теперь уберем фиксацию из этих ячеек. Быстрее всего это сделать через поиск и замену.

Не снимая выделения со всех 4 ячеек, выполните автозаполнение вправо.

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

Таким образом, мы выполнили все требования: все заголовки столбцов разные, находятся в одной строке и нет объединенных ячеек.

Пример 3. Музыкальные альбомы

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

Иногда объединенные ячейки встречаются и в самой таблице. Это крайне неудобно, мешает использовать формулы, выполнять сортировку и фильтрацию.

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

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

Шаг 1. Выделить всю таблицу (Ctrl+A) и отменить объединение ячеек.

Шаг 2. Выделяем пустые ячейки: нажать F5 или Ctrl+G – кнопка «Выделить…» – выбрать «Пустые ячейки» – «ОК».

Шаг 3. Вводим формулу, чтобы заполнить их значениями сверху: нажимаем "=" для начала ввода, стрелка вверх – вставит в формулу ячейку сверху, Ctrl+Enter – заполнить формулой все выделенные ячейки.

Шаг 4. Преобразовать формулы в значения: опять выделить всю таблицу, скопировать и вставить как значения.