Как создать отношения между несколькими таблицами с помощью модели данных в excel

Excel 2010: как объединить ячейки

Для данной версии этой программы существуют следующие пять приёмов.

Приём №1

Первый приём работает при помощи контекстного меню. Опытные пользователи программы считают его самым быстрым и простым. Выполняется он так:

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

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

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

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

Приём № 2

Для объединения можно использовать панель инструментов. Так, в Excel 2010, а также в версиях 2007 и 2013 есть иконка, которая называется «Объединить и поместить в центре». Она расположена в меню в разделе «Главная». Если нажать на эту иконку, то появится выпадающее меню, в котором есть несколько вариантов объединения. После того как будут выделены необходимые ячейки, нужно просто нажать на эту иконку и выбрать наиболее подходящий вариант их объединения. Кстати, нажав на эту иконку повторно, их можно опять разгруппировать.

Приём № 3

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

• выделяем В1. Во вкладке «Главная» наводим курсор на кнопку «Вставить», а затем кликаем на стрелочку, которая находится возле неё. Появится выпадающее меню, в котором нужно кликнуть на пункт «Вставить столбцы на лист»;
• между столбцами А и В появится новый столбец. Все данные из столбца В переместятся в С. В появившийся пустой столбец вписывается формула =СЦЕПИТЬ(А1;»,»;С1) и нажимается Enter. В этой формуле в кавычках заключён разделительный знак. Это необязательно должна быть запятая. Для разделения информации можно использовать такие знаки, как: «;», «:»;
• далее выделяется ячейка с формулой. После этого наводится курсор на правый нижний угол и зажимается левая кнопка мыши. Выделяется весь столбец. Таким образом, формула скопируется во все ячейки и столбцы объединятся;
• теперь выделите столбец В и нажмите Ctrl+C. После кликните один раз по столбцу правой кнопкой мыши и в появившемся меню сначала выберите пункт «Специальная вставка». В выпавшем меню выберите в пункте «Вставить значение» иконку с цифрами 123. Все эти действия необходимы чтобы выделенные ячейки столбца не были связаны с соседними ячейками формулой;
• далее удаляются столбцы А и С. Для этого курсор ставится в столбце А. Затем во вкладке «Главная» необходимо нажать на кнопку «Удалить». В выпавшем списке выбрать «Удалить столбцы с листа». Аналогичным образом удалить и столбец С. В итоге в таблице останется лишь один столбец со всеми объединёнными данными.

Приём № 4

Объединить их, не потеряв информацию, можно и другим способом. Алгоритм действий такой же, как и в третьем приёме, только для их склеивания используется формула =А2&С2.

Приём № 5

Объединять ячейки в Excel можно и при помощи горячих клавиш. Для этого нужно скопировать уже соединённую ячейку при помощи сочетания горячих клавиш Ctrl+C, а затем вставить её в нужном месте, используя горячие клавиши Ctrl+V. Стоит отметить, что применение горячих клавиш позволит значительно ускорить процесс создания таблицы с объединёнными ячейками.

Создание связи между таблицами Excel

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

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

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

Создание связей между рабочими книгами

1. Открываем обе рабочие книги в Excel
2. В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
3. Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
4. В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

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

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

Прежде чем создавать связи между таблицами

Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

Избегайте циклические ссылки. Циклические связи – когда две рабочие книги содержат ссылки друг на друга – могут быть причиной медленного открытия и работы файла.

Обновление связей

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

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

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.

Как объединить ячейки при помощи формул

1. Самый простой способ объединить данные в ячейках – использование знака конкатенации & (амперсанд):

Для добавления & в формулу нажмите Shift+7, на ENG раскладке клавиатуры.

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

Например:

• пробел » «;
• запятая и пробел «, » ;
• точка с запятой «;» и т.д.

2. Следующий вариант объединить строки – функция СЦЕПИТЬ (CONCATENATE), одна из текстовых функций, созданная для объединения двух или нескольких текстовых строк в одну.

Начиная с Excel 2016 является пока ещё рабочим атавизмом и используется в рамках совместимости версий.

Синтаксис функции:

СЦЕПИТЬ(текст1;;…), где текст1, ссылка на первую ячейку с данными или текстовую константу, соответственно ссылка на следующую ячейку или текстовую константу и т.д. Объединять можно текстовое значение, число (дату) или ссылки на ячейки.

Формула СЦЕПИТЬ может содержать до 255 элементов и до 8 192 символов.

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

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

Например, требуется объединить текст и число в ячейке с формулой, округленное до целого числа или добавить дату.

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

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

Недостаток объединения данных при помощи оператора & и функции СЦЕПИТЬ, состоит в том, что все части текста нужно указывать отдельными аргументами, поэтому соединение большого числа ячеек является весьма утомительным делом.

Решение появляется начиная с Excel 2016, в арсенал которого вошли две новых функции СЦЕП (CONCAT) и ОБЪЕДИНИТЬ (TEXTJOIN), речь о которых пойдет ниже.

3. Функция СЦЕП, полностью выполняющая функционал предшественника, но работает и с диапазонами данных.

Синтаксис функции:

СЦЕП(текст1; ; …), где текст – элемент, который нужно присоединить: строка или массив строк, например диапазон ячеек.

Формула СЦЕП может содержать до 253 аргументов.

Если объединенная строка содержит свыше 32767 символов (ограничение на ввод данных для ячейки), возникнет ошибка ЗНАЧ!.

Работает без проблем, но если требуется соединить диапазон с разделителем, СЦЕП не поможет.

4. Лучший вариант для объединения – функция ОБЪЕДИНИТЬ, объединяет текст из нескольких диапазонов и/или строк, вставляя между текстовыми значениями указанный пользователем разделитель.

Синтаксис функции:

ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; ; …), где

• Разделитель – символ, который будет вставлен между фрагментами, указывается в кавычках;
• игнорировать_пустые – если 0, то пустые ячейки включаются, если 1 – игнорируются;
• Текст… — диапазон ячеек, содержимое которых нужно склеить.

Формула ОБЪЕДЕНИТЬ может содержать до 252 аргументов.

Если объединенная строка содержит свыше 32767 символов (ограничение на ввод данных для ячейки), возникнет ошибка ЗНАЧ!.

Результат выполнения функции ОБЪЕДИНИТЬ

Спасибо, что дочитали до конца!

Еще интересное по теме Excel:

Формулы Excel

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

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

Для получения «Итого», к примеру, достаточно сложить данные по каждому наименованию за первую и вторую неделю. Вариант несложный, можно переписать несколько значений вручную. А как быть с полем «Сумма»? Тоже все руками? Как же систематизировать данные?

Изображение № 4. Работа с формулами

Все логично и невероятно удобно. Выбор поле для подсчета. Далее значок Fx (Изображение № 4, Пункт 2), определение подходящей для совершения арифметического действия формулы. (Все подписано и совершенно наглядно). После выбора СУММ выделение диапазона значений. Подтверждение действия кнопкой ВВОД. Лучший способ разобраться во всех функциях и набить руку – попрактиковаться. Любые табличные данные прекрасно подойдут для сложения, вычитания и выведения среднего значения.

Плюсы сводных таблиц

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

1. Таблица составляется по практически любому объему данных.
2. Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
3. Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
4. По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
5. Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются

Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2

они входят в интервал B2

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

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Операторы сравнения чисел и строк

Операторы сравнения чисел и строк представлены операторами, состоящими из одного или двух математических знаков равенства и неравенства:

• < – меньше;
• <= – меньше или равно;
• > – больше;
• >= – больше или равно;
• = – равно;
• <> – не равно.

Синтаксис:

1	Результат=Выражение1ОператорВыражение2

• Результат – любая числовая переменная;
• Выражение – выражение, возвращающее число или строку;
• Оператор – любой оператор сравнения чисел и строк.

Если переменная Результат будет объявлена как Boolean (или Variant), она будет возвращать значения False и True. Числовые переменные других типов будут возвращать значения 0 (False) и -1 (True).

Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом, VBA Excel сгенерирует ошибку Type Mismatch (несоответствие типов данных):

1 2 3 4 5 6 7 8 9 10

SubPrimer1() On ErrorGoToInstr DimmyRes AsBoolean ‘Сравниваем строку с числом myRes=“пять”>3 Instr IfErr.Description<>“”Then MsgBox“Произошла ошибка: “&Err.Description EndIf EndSub

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

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

1 2 3

myRes=“семь”>“восемь”‘myRes = True myRes=“Семь”>“восемь”‘myRes = False myRes=Len(“семь”)>Len(“восемь”)‘myRes = False

Фильтрация полей отчета

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

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

Щелкните на кнопке фильтрации по Дате, в раскрывающемся списке выберите «1 января», а затем щелкните на кнопке ОК. В результате отобразятся расходы и доходы только за 1 января.

Аналогичным образом можно выбирать несколько дат. Для этого установите галочку напротив пункта Выделить несколько элементов.

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

Изменение набора полей сводной таблицы

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

1. Щелкните на произвольно выбранной ячейке сводной таблицы.

Excel добавит на ленту набор контекстных вкладок Работа со сводными таблицами с собственными контекстными вкладками Анализ и Конструктор.

2. Щелкните на контекстной вкладке Анализ, чтобы отобразить на ленте ее кнопки.
3. Щелкните на кнопке Список полей, находящейся в группе Показать.

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

После открытия панели списка полей можно выполнить следующие изменения.

Чтобы удалить поле, перетащите его имя из области, в которой оно находится в текущий момент (ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ или ЗНАЧЕНИЯ), в любое другое место. Как только указатель мыши примет вид крестика, отпустите кнопку мыши или просто снимите флажок около этого поля в списке полей.

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

Многооконный режим Excel 2010

Как настроить Excel 2010, чтобы можно было по-умолчанию открывать 2-3-4. и более документов Excel в отдельных окнах и без . этого самого, как его. Совет, как открыть 2 документа в разных окнах одноразово — такой совет я в этих ваших интернетах нашел уже. Но геморой каждый раз не устраивает. Люди на фирме открывают документы сотнями каждый день.

P.S. Суть работы заключается в обработке данных разбитых на 3-4 книги Excel, в каждой из которых до нескольких десятоков страниц, некоторые представлены сводными таблицами. Это комплект документов на определенную дату. Дней в году — 365. Представляете масштаб проблемы? Открой так сначала Excel, а из него документ. И сопоставь данные в них, если они так по-скотски открываются в одном окне.

Связанный список

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

1. Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
2. Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
3. Задаем ему имя, например, “Питание”.
4. Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
5. После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
6. В качестве источника указываем “=Питание” (согласно нашему названию).
7. Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
8. Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Уловки для создания раскрывающихся списков, позволяющих выбрать несколько элементов

Наш приглашенный блоггер Дебра Далглиш — консультант, специализирующийся на программировании и разработке Microsoft Office.Каждую неделю она делится своими знаниями об Excel в блоге Contextures и ежегодно получает награду Excel MVP с 2001 года.

На Фабрике мистической магии * одно из преимуществ — то, что офисные сотрудники могут исчезать в полдень по большинству пятниц. Однако офис остается открытым, поэтому по крайней мере два человека дежурят, чтобы отвечать на телефонные звонки и приветствовать посетителей.

Перед волшебством

Чтобы отслеживать, кто будет оставаться в офисе каждую неделю, Ди, менеджер офиса, создал файл Excel.Этот файл содержит список имен сотрудников и список недель. Ди создал раскрывающийся список имен сотрудников на каждую неделю.

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

Добавьте волшебство

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

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

За занавеской

Чтобы увидеть код для рабочего листа, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код . Это откроет Visual Basic Explorer (VBE).

В списке проектов слева указана рабочая книга FridayStaffName, а лист Friday_Staff выделен. Справа мы видим код события Worksheet_Change для этого рабочего листа.Этот код запускается автоматически, если Ди вносит изменения в лист.

Скопируйте код в свой файл

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

В коде Target относится к ячейке, которую вы изменили. На снимке экрана ниже код проверяет, в каком столбце находится эта целевая ячейка.Затем, если это был столбец 2 (B), остальная часть кода будет выполнена.

Вместо использования столбца 2 вы можете изменить его число или использовать несколько столбцов, например: Case 2, 5, 6

Пусть волшебство работает на вас

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

Вы можете загрузить образец файла, чтобы увидеть, как он работает, а также добавить этот удивительный трюк в свои рабочие книги: Загрузите файл образца имен сотрудников Friday

* Mystical Magic Factory — вымышленная компания, но эта тема блога основана на реальной проблеме клиента Contextures.

Дополнительная информация в раскрывающихся списках Excel:

Как создать раскрывающийся список ячеек в Excel 2010