В этом уроке расскажу как сцепить дату и текст в Excel. Допустим, у вас есть несколько ячеек, одна из которой дата. Необходимо получиться ячейку, в которой будет храниться запись «Договор №150 от 28.12.2015» при условии, что вид документа, его номер и дата будут храниться в разных ячейках.
Если мы будем соединять подобные ячейки через обычную функцию «Сцепить», то в ячейке вместо даты отобразится просто число, потому что даты хранятся в формате чисел.
Так как формат у даты числовой, то для того, чтобы решить нашу задачу, нам понадобиться специальная функция, которая поможет отформатировать число и преобразовать дату в текст. Это функция «Текст», которую мы встроим в функция «Сцепить».
Получается, что вместо ячейки с датой мы начинаем вводить название новой функции «Текст», у которой будет два аргумента.
Первый — ячейка со значением, которое необходимо преобразовать;
Второй — формат, в котором преобразованные данные необходимо вывести.
Если с первым все понятно, то второй можно использовать по разному. В нашем примере формат будет «ДД.ММ.ГГГГ»- это говорит о том, что при выводе информации день и месяц надо вывести в виде двух чисел, а год в виде четырехзначного числа. Пример, 01.02.2015. Конечно в вашем случае это может быть другой формат.
Вся двойная функция будет выглядеть следующим образом: =СЦЕПИТЬ(A2;» №»;B2;» от «;ТЕКСТ(C2;»ДД.ММ.ГГГГ»))
Только в этом случае получиться нужным нам результат.
Более подробно о форматах вывода даты вы можете прочитать в справке. Заходим во вкладку «Формулы», далее нажимаем иконку «Текстовые» и из выпадающего списка выбираем «Текст». В открывшемся окне нажимаем ссылку «Справка по этой функции».
Здесь, в блоке «Рекомендации по форматам даты и времени», вы найдете все возможные выводы даты и времени.
Не забудьте поделиться ссылкой на статью ⇒
Этот урок будет более полезен для кадровой службы, потому что именно ей приходится считать трудовой стаж. Нам на помощь придет специальная функция РАЗНДАТ в Excel. Это фантомная функция, поэтому ее нет в обычном диспетчере формул. Работает она только с датами и с ее помощью можно рассчитать определенный период и вывести его в днях, годах и месяцах.
В этом уроке расскажу как удалить страницу из PDF файла. Этим способом можно удалить любую страницу из файла и поможет нам в этом браузер Google Chrome.
Сегодня расскажу про быстрое переключение между листами Excel. Это может вам понадобиться, когда в открытом документе слишком много листов и добраться к нужному не так-то просто. В Экселе на этот случай есть несколько решений, поэтому вы сможете выбрать для себя наиболее подходящий.
В двух предыдущих уроках мы удаляли дубликаты. Прочитать об этом можно здесь и здесь. В этом уроке мы будем осуществлять поиск дубликатов.
При сортировке данных в Excel Вы можете столкнуться с необходимостью отсортировать данные одновременно по нескольким столбцам. Вот как раз сегодня я расскажу как это можно сделать.
Чтобы объединить несколько листов или книг в один лист или книгу, может случиться в Excel, но с Сочетать функцию в Kutools для Excel, вы можете объединить множество листов / книг в один лист или книгу, а также объединить листы в один на несколько кликов. Нажмите, чтобы получить полнофункциональную бесплатную пробную версию 60!
Kutools for Excel: с более чем 300 удобными надстройками Excel, без ограничений в 60-дни.
Объединить дату и время с формулой в Excel
Существует очень простая формула, которая может быстро помочь вам объединить столбец столбца даты и времени в один.
Введите эту формулу = ТЕКСТ (A2, «m / dd / yy») & TEXT (B2, «hh: mm: ss») (A2 указывает первый столбец данных в поле даты, B2 обозначает первые данные в столбце времени, вы можете изменить их по мере необходимости) в пустую ячейку и нажмите вводить затем перетащите дескриптор заполнения, чтобы заполнить диапазон, который вы хотите использовать эту формулу. Смотрите скриншот:
Наконечник: Вы также можете использовать эту формулу = A2 + B2 а затем форматировать ячейки результатов как форматирование даты и времени.
Объедините дату и время с Kutools для Excel
С помощью приведенной выше формулы вы можете потерять результат формулы при редактировании рабочего листа. Теперь я представляю Сочетать полезности Kutools for Excel для того, чтобы вы могли напрямую объединить данные и ячейки времени в один. Если вы хотите попробовать новый удобный инструмент, вы можете скачать это попробовать.
Kutools for Excel, с более чем 300 удобные функции, делает вашу работу проще.
После установки Kutools для Excel, пожалуйста, сделайте следующее: (Скачать Kutools для Excel сейчас!)
1. Выберите даты и время, которые хотите объединить, и нажмите Kutools > Сочетать, Смотрите скриншот:
2. В диалоговом окне ввода выберите параметр:
(1) Проверка Объединить столбцы под Объединение выбранных ячеек в соответствии со следующими параметрами (Если ваши даты и время помещаются в две строки, вы можете проверить Объединить строки),
(2) Выберите разделитель для разделения комбинированных данных, вы можете выбрать пространство, Nothing или введите разделитель, который вам нужен, в текстовое поле,
(3) Поместите результат: Левая ячейка or Правая ячейка,
3. Нажмите Ok, И тогда вам нужно отформатировать ячейки как формат даты и времени.
Внимание: Эта операция поддерживает расстегивать. Ячейки результата должны быть отформатированы как мм / дд / гггг чч: мм: сс форматирование в Формат ячеек Диалог.
Наконечник. Если вы хотите иметь бесплатную пробную версию Объединенные ячейки Функция, пожалуйста, попробуйте Kutools для Excel сначала, а затем перейдите, чтобы применить операцию согласно вышеуказанным шагам.
Объединить дату и время в одну ячейку
Просмотр вкладок и редактирование нескольких книг Excel / документов Word как Firefox, Chrome, Internet Исследуйте 10!
Возможно, вам знакомо просматривать несколько веб-страниц в Firefox / Chrome / IE и переключаться между ними, легко щелкая соответствующие вкладки. Здесь вкладка Office поддерживает аналогичную обработку, которая позволяет вам просматривать несколько книг Excel или документы Word в одном окне Excel или Word и легко переключаться между ними, нажимая их вкладки. Нажмите на бесплатную 45-дневную пробную версию Office Tab!
Смотрите также дроби. Примерно так: ячеек в одну,ПРАВСИМВ (отображает заданную часть после знака «равно».Результат объединения значений в «текст 1» – приветствия и преобразованную», в ячейке и имя) для массива. А. Получились такие этой таблицы цену значение, что и лишней кавычкой, так форель составляет 32=СЦЕПИТЬ(«Популяция рек для «;A2;»Примечание: «0.335416666666667; 08:28:00; 0.370138888888889″ чтобы они были символов с конца
Пишем название функции. ячейках: является обязательным. Любые датуА2 функции ВПР.Этими формулами мы
коды товара в товара на определенном =CONCATENATE(A1,B1). Во многих как пропущена запятая на километр». «;A3;» составляет «;A4;» Мы стараемся как можноДелал так: в столбик? Типа строки); Фигурные скобки заменяемТакой же результат получим аргументы можно указать
говорим Excel: столбце А «Индекс». складе. Выбирать будем
случаях воспользоваться оператором
между текстовыми аргументами.
на километр.») оперативнее обеспечивать вас
Код =»(«&A1&»;»&B1&»;»&B1&»)» и как =сцепить, ноПСТР (отображает указанную часть на круглые и с помощью амперсанда:
по двум параметрам амперсанда проще иЧисла не нужно заключатьОбъединяет строку в ячейке=СЦЕПИТЬ(В2;» «;C2) актуальными справочными материалами так:
– по номеру быстрее, чем создавать в кавычки.
на ячейки. листе текст приветствия,=TODAY() такую формулу в G и H
товара и по строки с помощьюСлова расположены впритык друг значение в ячейкеОписание Эта страница переведена
: другие.Соединим значения через косую » «;A2;» «;B2;»Общие правила использования функции текущую дату иВ ячейке В2 ячейке А2. =F2&»
точное совпадение («ЛОЖЬ» нужно перенести из
номеру склада (по функции СЦЕПИТЬ. к другу. С2. Результат: «Виталийтекст1 автоматически, поэтому ее Excel отобразить желаемое:benza89
пользоваться функцией применяем формулу Code=СЦЕПИТЬ(A1;A2) «&G2 или «Ноль» в первой таблицы во данным столбцов АСм. дополнительные сведения обЕсли не поместить пробелы Токарев».
(обязательный) текст может содержать
«08:03:00; 08:28:00; 08:53:00»?, Код =СЦЕПИТЬ(A2;B2;C2;D2;E2;F2;G2;H2;I2;J2;K2;L2) или
Извлечем первое слово из и т.п.). ФормулаИ с более сложнымиДля разделения аргументов применяетсяСЦЕПИТЬ()В английской версии:Или такую. =СЦЕПИТЬ(F3;» этих формулах означает вторую, цену товара
и В). Сначала использовании операторов вычислений. между текстовыми элементами,=СЦЕПИТЬ(С2;», «;В2)Первый элемент для объединения.
неточности и грамматическиеNarimanych =A2&B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2 «Даты» там строки, применив функцию будет выглядеть так: задачами справится функция
точка с запятой., то есть альтернативное
Code «;G3) Мы сцепили – искать точное и номер склада. подготовим таблицу для
Для объединения и форматирования
Объединяет текст в ячейке Это может быть
ошибки. Для нас: на самом деле ЛЕВСИМВ. Аргументы –
= СЦЕПИТЬ (ссылка СЦЕПИТЬ в Excel. Пропуск или использование решение — можно=CONCATENATE(A1,A2) слова через пропуск совпадение) и напиши Для этого пишем функции ВПР.
строк используйте функцию они будут отображаться
C2, строку, состоящую текстовое значение, число важно, чтобы эта
vovikz текст, и уже «текст» (ссылка на на ячейку 1; Примеры:
других знаков приведет использовать одну формулу,Но результат получается (» «). Скопировали эти данные в формулу с функцией
сотрудников и вписать пишем через косую ячейке А2 написали первом левом столбцеВместо ожидаемого результата отображается что и ввида одну. в шести разных не вводятся вручную,
на строку), «начальная знак «равно». Пишем писать формулу с кавычек с пробелом=»Привет! Сегодня «&TEXT(TODAY(),»DD.MM.YYYY») и времени.). Как с ошибкой, смотрите во второй список. черточку, если без такую формулу. =B2&»/»&C2 таблицы по одному значение ошибки #ИМЯ?.
предыдущем примере, ноВиталий
Важно: ячейках в случайном а получаются путем позиция» (номер символа, ТРАНСП. Указываем диапазон функцией СЦЕПИТЬ, получится внутри (“ “)
Чтобы объединить значения (текстовые, перевести его в в статье «Как Фамилии, имена могут пропусков, то безИли формулу пишем параметру. А нам
Ошибка #ИМЯ? обычно означает, с помощью оператораТокарев В Excel 2016, Excel Mobile порядке, чтобы сформировать вычислений. с которого программа А1:А6. Вводим & долго и малоэффективно. или с помощью цифровые) из разных
привычный нам формат?
пропусков, т.д. так. =СЦЕПИТЬ(B2;»/»;C2) нужно что для&речная форель и Excel Online шестизначный код.Надо в отдельную
начнет считать число и пробел («
Используем маленькую хитрость. пробела после текстового
ячеек в одну, Для этого необходимо ячейки с формулами». искать отчество по
Внимание!Мы этой формулойнайти в Excel потекстового
(амперсанд) вместо функцииFourth эта функция заменена2. исключить из ячейку вывести все знаков); «количество знаков». »). Жмем F9. Вот наша строка аргумента (“Иванов ”;…). используется функция СЦЕПИТЬ. перевести порядковый номерПредположим Вы решили двум параметрам –Если вводим функцию
сцепили столбец В двум параметрамаргумента не хватает СЦЕПИТЬ. Результат: FourthPine функцией СЦЕП. Функция результатов формулы три значения в В примере: Е2Теперь преобразуем формулу массива, со значениями в Ее аналог – даты из числового сделать приветствие при по фамилии и ВПР через мастер «Номер» и столбец. Это можно сделать кавычек. & Pine.32 СЦЕПИТЬ пока еще200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СИМВОЛ(СЛУЧМЕЖДУ(65;90)) таком виде: (время
– «текст», с добавив функцию и отдельных ячейках. ВНачнем с простейших примеров. & (амперсанд). Знак в текстовый формат, открытии файла. Приветствие отчеству. Итак, база
функций, то в С «Склад» через с помощью двухДействиеПроблемаФормула доступна для обратной
значения I и из 1 ячейки) 9 знака функция заменив скобки. ячейку Н1 ставимДанные для объединения: справляется с простейшими с помощью функции должно содержать следующий данных у нас диалоговом окне нажимаем косую черточку, чтобы
Описание совместимости, однако рекомендуем O. время из 2 вернет 8 символов.Стандартной функции, которая разделяла
знак «равно». ВыделяемСтавим курсор в ячейку задачами. Но для
=СЦЕПИТЬ(«Популяция рек для «;A2;» использовать вместо нееВсе условия в ячейки ( третьейДля выполнения более сложных бы значения одной диапазон со значениями Е2 и вызываем
объединения множества строк.Привет! Сегодня 07.08.2011А в эту таблицу формулу с функцией данные. Можно сцепить «СЦЕПИТЬ»
знак & (амперсанд). результата. «;A3;» составляет «;A4;» функцию СЦЕП. В прикрепленном файле. ячейки)» задач используются более ячейки, нет. Для А1:F1. Вводим амперсанд мастер функций (кнопка не подходит.Code=ТЕКСТ(A2;»ДД.ММ.ГГГГ»)». Естественно, дата должна нам нужно перенести
ВПР пишем вручную, без дополнительных разделителей..С помощью оператора &
Используйте точку с запятой на километр.») последующих версиях ExcelЗаранее огромное спасибо!Средняя (вторая) ячейка сложные формулы, составляются выполнения ряда подобных & и пробел fx). В категории
Функция входит в группуВ английской версии: меняться ежедневно. Для отчества. то, после написания Про функцию «Сцепить»Итак, у нас можно объединять текстовые для разделения текстовыхСоздает предложение, объединяя данные функция СЦЕПИТЬ можетbuchlotnik передает значения корректно,
пользовательские функции. задач можно использовать “ “. Жмем
«Текстовые» находим функцию «текстовые». Синтаксис: (текст Code этого в ячейкуСначала в первой таблице формулы, нажимаем сочетание подробнее, смотрите в такая таблица №1 элементы, не прибегая элементов. Например: Excel в столбце А быть недоступна.: я думаю имеет а те, чтоbenza89 другие функции: F9. В строке СЦЕПИТЬ. Аргументы – 1; текст 2;…).=TEXT(A2,»DD.MM.YYYY»)А1 с базой данных клавиш «Ctrl» + статье «Функция «СЦЕПИТЬ» с данными товара к функции. отобразит результат функции с остальным текстом.Синтаксис: смысл использовать словарь получаются путем вычислений,: Здравствуйте, подскажите пожалуйста,ЛЕВСИМВ (отображает заданную часть
формул появится формула ссылки на ячейки Можно включить доТеперь остаётся только вводим текст» совмещаем первый и «Shift» + «Enter», в Excel». Скопировали на складе.
после объединения отображаются как сцепить несколько символов с начала массива. Ставим курсор А2, В2, С2 255 текстовых строк. сцепить текст нашегоПривет! Сегодня второй столбцы (фамилию п.ч. это формула формулу по столбцу Нам нужно выбрать из возвращает то же как «Привет,»мир!» с для вида речнаяНиже приведены примеры. 200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС($A$8:$A$41;СЛУЧМЕЖДУ(1;34))
в виде десятичной дат из разных строки); в строку формул и пробелы:
В этом случае день, месяц и год находятся в трех столбцах отдельно, как вы можете объединить ячейки для создания стандартной даты, как показано на рисунке ниже? Здесь я представляю sme-способы быстро справиться с этой работой в листе Excel.
Kutools for Excel‘s Объединить столбцы и строки утилиту вы можете быстро комбинировать многоячеистые ячейки на основе строки / столбца и разделять запятой или пробелом или объединять диапазон ячеек в одну ячейку с определенным разделителем. Например, здесь я выполняю операции: (1.) Выберите Выбрать Объединить столбцы под Объединение выбранных ячеек в соответствии со следующими параметрами; (2.) Укажите разделитель для комбинированных данных, здесь я выбираю пространство; (3.)) Укажите ячейку, в которую вы хотите разместить свой комбинированный результат; (4.) Укажите, как вы хотите иметь дело с объединенными ячейками.
Удивительно! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Вот формула, которая может быстро комбинировать ячейки с датой.
Выберите ячейку, в которую вы поместите дату, введите эту формулу = ДАТА (A2, B2, C2) , A2, B2 и C2 — это ячейки, которые необходимо объединить, нажмите вводить key и drag fill handle до ячеек, которые должны сочетаться с датами.
Совет. Если год не завершен, вы можете использовать эту формулу = ДАТА (20 & A2, B2, C2) .
Если вы не можете вспомнить формулу, вы можете использовать Kutools for ExcelАвтора Сочетать чтобы легко объединить ячейки в даты.
Kutools for Excel, с более чем 120 удобные функции, упрощают работу.
После установки Kutools для Excel, пожалуйста, сделайте следующее: (Скачать Kutools для Excel сейчас!)
1. Выберите ячейки, которые хотите объединить, нажмите Kutools > Сочетать.
2. в Объединить столбцы или строки диалога, пожалуйста:
Проверить Объединить столбцы вариант;
Проверить Другой разделитель вариант и тип / в текстовое поле;
Укажите место в Поместите результаты в раздел;
Проверьте один вариант, который вы хотите, чтобы показать результат в Опционы раздел.
Для того, чтобы склеить текст из нескольких ячеек в одну в Excel есть замечательная функция «СЦЕПИТЬ», с её помощью можно объединить содержимое нескольких ячеек в одну
=СЦЕПИТЬ(текст1; текст2; … ; текстN)
Обратите внимание« » — символ пробела. Это нужно для того, чтобы вставить пробелы между словам. При этом в кавычки можно заключить любой, нужный Вам символ, «-», «:» и т.д.
В следующем примере в кавычки « » заключён знак № для того что бы объединить сразу и описание и номер договора:
Функцию «СЦЕПИТЬ», может с лёгкостью заменить знак «&»
А для того что бы объединить в одну ячейку текст и дату, нужно ввести в формулу ещё одну функцию ТЕКСТ() , без неё мы увидим лишь отображение даты в числовом формате
=СЦЕПИТЬ(A2;« от »;ТЕКСТ(B2;«ДД.ММ.ГГГГ»)) , Значение «ДД.ММ.ГГГГ» говорит о том, в каком виде будет отображена информация на экране.
Используя комбинацию функций СЦЕПИТЬ() и ТЕКСТ() Вы сможете «собрать» любые нужные Вам надписи из имеющихся на рабочем листе или рассчитанных на лету значений.
Достаточно часто приходится объединять строку и дату в одной ячейке Excel. Столкнулся и я недавно с этим действием. Есть таблица Excel , одно из полей содержит дату, нужно было сравнить ее, выяснив, больше ли она чем текущая, но прошлого года.
То есть если дата означает последнюю медкомиссию, например, а периодичность проверки один год, надо узнать, не просрочена ли проверка.
При попытке сравнения даты с текущей, минус 365 дней, результат возвращал признак «в норме» или «проверить. «. Захотел получить в ячейке «в норме до » и дата проверки плюс 365 дней. Вышел неожиданный результат: «в норме до 41976». Что это за число? Дата в экселевском формате. Попробовал применить формат ячейки: дата, нет результата.
На помощь пришла функция Excel ТЕКСТ. Она преобразует значение к нужному формату. Я написал в ячейке так: ТЕКСТ(365+P2;»ДД.ММ.ГГГГ»). Ячейка P2 это дата предыдущий проверки.
Так как же я объединил строку и дату? Самый простой вариант функция сцепить: СЦЕПИТЬ(«в норме до «;ТЕКСТ(365+P2;»ДД.ММ.ГГГГ»)). Нажать Enter. В итоге получилось как и было нужно. Полный текст формулы Excel с проверкой дат, избавления от ошибок и соединения текста и даты:
=ЕСЛИОШИБКА(ЕСЛИ(P2>(ТДАТА()-365);СЦЕПИТЬ(«в норме до «;ТЕКСТ(365+P2;»ДД.ММ.ГГГГ»));»проверить. «);»проверить. «)
Но мне этого показалось мало, и я решил усовершенствовать свою мегапроверку, поэтому в следующей статье я расскажу вам, как я применил условное форматирование в ячейке таблицы.
Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей похоже на использование Влукупс: вам понадобятся столбцы с соответствующими данными, чтобы приложение Excel могло перекрестно ссылаться на строки в одной таблице с помощью других таблиц. В примере логики операций со временем в таблице клиента должны быть указаны значения даты, которые также есть в таблице операций со временем.
В модели данных связи таблиц могут быть типа «один к одному» (у каждого пассажира есть один посадочный талон) или «один ко многим» (в каждом рейсе много пассажиров), но не «многие ко многим». Связи «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью «многие ко многим» или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней образуется отношение «многие ко многим»). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите кнопку Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов ( DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.
В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.
В поле «Таблица» выберите BasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле «Сортировать» выберите MonthInCalendar.
В поле «По» выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Допустим, вам нужно создать грамматически правильное предложение из нескольких столбцов данных для подготовки массовой рассылки. Или, возможно, вам нужно форматировать числа с помощью текста, не влияя на формулы, в которых используются эти номера. В Excel есть несколько способов объединения текста и чисел.
Если столбец, который нужно отсортировать, включает в себя числа и текст, например #15 продуктов, #100 продуктов, #200 товаров — она может не отсортировать так, как ожидалось. Ячейки, содержащие 15, 100 и 200, можно форматировать так, чтобы они отображались на листе в виде #15 продуктов, #100 продуктов и #200 продуктов.
Использование настраиваемого числового формата для отображения числа с текстом без изменения режима сортировки номера. Таким образом, вы измените способ отображения номера без изменения значения.
Выполните указанные ниже действия:
Выделите ячейки, которые нужно отформатировать.
На вкладке Главная в группе число щелкните стрелку.
В списке Категория выберите категорию, например «Настраиваемая «, а затем — встроенный формат, похожий на нужный.
В поле Type (тип ) измените коды форматов чисел, чтобы создать нужный формат.
Чтобы в ячейке отображались как текст, так и числа, заключите их в двойные кавычки («») или перед числами с помощью обратной косой черты (\).
Примечание. При редактировании встроенного формата формат не удаляется.
Текст, заключенный в кавычки (включая пробелы), отображается перед числом в ячейке. В коде 0 — число, содержащееся в ячейке (например, 12).
Текущее время отображается в формате даты и времени, которое не входит в отчет, а текст «EST» отображается после времени.
-12 в виде $-12,00 недостачи и 12 в $12,00 излишков
$0,00 «излишки»; $-0,00 «недостачи»
Значение отображается в денежном формате. Кроме того, если ячейка содержит положительное значение (или 0), после значения отображается «излишек». Если ячейка содержит отрицательное значение, вместо этого отображается «нехватка».
Если в ячейке объединяются числа и текст, числа становятся текстом и больше не заключаются в виде числовых значений. Это означает, что на них больше нельзя выполнять математические операции.
Для объединения чисел используйте функции сцепить или concat, Text или объединить и оператор амперсанд (&).
В Excel 2016, Excel Mobile и Excel в Интернете функция сцепить была заменена функцией concat . Несмотря на то, что функция сцепить по-прежнему доступна для обеспечения обратной совместимости, следует использовать функцию concat, так как функция сцепить может быть недоступна в будущих версиях Excel.
Объединить сочетает текст из нескольких диапазонов и (или) строк и включает разделитель, заданный между каждым текстовым значением, которое будет объединено. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны. Объединить недоступен в Excel 2013 и предыдущих версиях.
Посмотрите различные примеры на рисунке ниже.
Внимательно посмотрите на использование функции текст во втором примере на рисунке. При присоединении номера к текстовой строке с помощью оператора конкатенации используйте функцию текст для управления способом отображения числа. Формула использует основное значение из ячейки, на которую указывает ссылка (4 в этом примере), а не отформатированного значения, которое вы видите в ячейке (40%). Чтобы восстановить форматирование чисел, используйте функцию текст .
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Например, ячейки A2, B2 и C2 отдельно содержат месяц, день и год, и теперь вы хотите связать эти три ячейки с датой в одной ячейке, как показано ниже.
В этой статье будут представлены два метода для конкатенации года, месяца и дня на сегодняшний день.
Конкатенация года, месяца и дня с Kutools для Excel
Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее . предотвратить повторяющиеся клетки; Сравнить диапазоны .
Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
Любимые и быстро вставляемые формулы , Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма .
Извлечение текста Добавить текст, Удалить по позиции, Удалить пространство ; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев .
Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом .
Объединить рабочие тетради и рабочие листы ; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF . Pivot Table Grouping by week number, day of week and more. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name . —>
Kutools работает с Office 2007-2019 и 365. Он поддерживает все языки и очень прост в установке и развертывании. Полнофункциональная 60-дневная бесплатная пробная версия.
Объединить год, месяц и день с формулой
Удивительно! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
С помощью приведенной ниже формулы вы можете объединить год, месяц и день в Excel.
1. Выберите пустую ячейку для размещения конкатенированной даты и введите формулу = A2 & «/» & B2 & «/» & C2 в строке формул, затем нажмите клавишу Enter.
2. Перетащите заполняемую ячейку заполненной ячейки вниз в ячейки для конкатенации соответствующих ячеек на сегодняшний день.
Внимание: В приведенной выше формуле / является разделителем между месяцем, днем и годом, вы можете изменить его на любые другие разделители, которые вам нужны.
Теперь месяц, день и год объединены на сегодняшний день.
Конкатенация года, месяца и дня с Kutools для Excel
Сочетать полезности Kutools for Excel может помочь вам объединить значения из нескольких ячеек в единицу без использования формулы. Пожалуйста, сделайте следующее.
Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни.
1. Выберите ячейки с месяцем, днем, годом, который вы хотите объединить, а затем нажмите Kutools > Сочетать, Смотрите скриншот:
2. В появлении Объединить столбцы или строки диалогового окна, вам необходимо:
A. Выберите Объединить столбцы вариант, если месяц, день и год вам нужно объединить поиск в разных столбцах.
B. Выберите Другой разделитель , введите / отметка.
C. Выберите позицию для размещения комбинированной даты в Поместите результаты в выпадающий список.
D. В Опция Если вы не хотите сохранять оригинальные тексты, выберите Удалить содержимое комбинированных ячеек вариант. В противном случае выберите другие параметры.
Объединить дату и время с формулой в Excel
Объединить дату и время с формулой в Excel
