Как сделать сплошную строку в excel

Обводка и заливка таблиц

Настроить обводку и заливку для таблицы можно несколькими способами. Изменить рамку вокруг таблицы, добавить параметры обводки и заливки для столбцов и строк можно в диалоговом окне «Параметры таблицы». Чтобы изменить обводку и заливку отдельных ячеек или колонтитулов, откройте диалоговое окно «Параметры ячейки» или палитры «Образцы», «Обводка» и «Цвет».

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

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

Изменение рамки вокруг таблицы

Можно изменять границу таблицы при помощи диалогового окна «Настройка таблицы» или палитры «Обводка».

Установив точку ввода в ячейку, выберите меню «Таблица» > «Параметры таблицы» > «Настройка таблицы».

В группе «Рамка вокруг таблицы» настройте толщину, тип, цвет, оттенок и зазоры (см. раздел Параметры обводки и заливки таблиц).

В группе «Последовательность обводки» выберите один из следующих вариантов.
Наилучшие соединения

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

Обводка строк на переднем плане
В этом режиме обводка строк отображается на переднем плане.
Обводка столбцов на переднем плане
В этом режиме обводка столбцов отображается на переднем плане.
Совместимость с InDesign 2.0

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

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

Нажмите кнопку «ОК».
Примечание.

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

Добавление в ячейки обводки и заливки

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

Добавление обводки и заливки в диалоговом окне «Параметры ячейки»

Границы ячеек, к которым будет применяться форматирование обводки и заливки, можно выбрать в области предварительного просмотра. Если нужно изменить внешний вид всех строк или столбцов таблицы, используйте чередование шаблона обводки или узорной заливки, при котором для второго шаблона выбрано значение «0».

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

Выберите команду «Таблица» > «Параметры ячейки» > «Обводка и заливка».

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

Примечание.

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

В группе «Обводка ячейки» задайте толщину, зазоры, тип, цвет и оттенок (см. раздел Параметры обводки и заливки таблиц).

В группе «Заливка ячейки» настройте цвет и оттенок.

При необходимости установите параметры «Наложение обводки» и «Наложение заливки», затем нажмите кнопку «OК».

Добавление обводки ячейки с помощью палитры «Обводка»

Палитра «Обводка» доступна только в InDesign (в InCopy она отсутствует).

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

Чтобы открыть палитру «Обводка», выберите «Окно» > «Обводка».
В области предварительного просмотра укажите границы, для которых будет изменена обводка.

Убедитесь, что в палитре «Инструменты» выбрана кнопка «Объект» . (Если активна кнопка «Текст» , то параметры обводки будут использованы не для ячейки, а для текста.)

Задайте толщину и тип обводки.

Добавление заливки в ячейки при помощи палитры «Образцы»

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

Чтобы открыть палитру «Образцы», выберите «Окно» > «Цвет» > «Образцы».

Убедитесь, что выбрана кнопка «Объект» . (Если активна кнопка «Текст» , то заданный цвет будет использован не для ячейки, а для текста.)

Делим слипшийся текст на части

Выделите ячейки, которые будем делить и выберите в меню Данные — Текст по столбцам (Data — Text to columns) . Появится окно Мастера разбора текстов:

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

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) — необходимо указать какой именно символ является разделителем:

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании «Иванов, Манн и Фарбер») не делился по запятой
внутри названия.

И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:

• общий — оставит данные как есть — подходит в большинстве случаев
• дата — необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
• текстовый — этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:

Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

• в Excel 2003 и старше — меню Сервис — Макрос — Редактор Visual Basic(Tools — Macro — Visual Basic Editor)
• в Excel 2007 и новее — вкладка Разработчик — Редактор Visual Basic (Developer — Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert — Module) и копируем туда текст вот этой пользовательской функции:

Function Substring(Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) If n > 0 And n - 1 
Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:
=SUBSTRING(Txt; Delimeter; n)

  
Txt - адрес ячейки с текстом, который делим
  
Delimeter - символ-разделитель (пробел, запятая и т.д.)
  
n - порядковый номер извлекаемого фрагмента

 
Способ 3. Разделение слипшегося текста без пробелов
Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО "ИвановИванИванович"), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:
Function CutWords(Txt As Range) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len(Txt) If Mid(Txt, i, 1) Like "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then Out = Out & Mid(Txt, i, 1) & " " Else Out = Out & Mid(Txt, i, 1) End If Next i CutWords = Out End Function
Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:
 
Ссылки по теме

Деление текста при помощи готовой функции надстройки PLEX
Что такое макросы, куда вставлять код макроса, как их использовать

Как правильно сделать сквозную нумерацию в Экселе

24.11.22 17:31 
текст: Игорь Голованов 
фото: INNOV.RU 
 5776 

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

В ячейку, с которой будет стартовать нумерация, проставьте цифровое значение. Это необязательно должна быть «1». Нумеровать можно с любого числа.
В соседней ячейке проставьте следующую за ней цифру. Если нужно пронумеровать столбцы, то «2» ставится в ячейку под «1». Если строки – «2» ставится справа от «1».
После этого выделите все заполненные ячейки.
Для проставления нумерации установите курсор на нижний правый угол выделенной области, и протяните крестик в том направлении, в котором вам нужно проставить нумерацию.

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

Впишите в ячейку первоначальную точку отсчета, к примеру, цифру «1».
Перейдите на следующую строку.
После этого перейдите в строку функции и проставьте символ «=».
Укажите стартовую ячейку и сумму. Например, выбираем ячейку А1 и прибавляем к ней единицу.
Нажимаем Enter.

После этого вторая строка заполнится числовым значением больше на «1». Чтобы применить данную функцию к последующим ячейкам, необходимо протянуть формулу вдоль всего диапазона, используя маркер автозаполнения.
Выполнить аналогичные действия можно с использованием команды «СУММ». Для этого нажмите на кнопку выбора функции Fx и введите в строке требуемые параметры.
Благодаря такому нехитрому способу, вы знаете, как пронумеровать в Эксель столбцы и строки, используя простейшую формулу.
 
Нумерация строк с помощью функции СТРОКА
Этот способ предполагает использование ссылки на номер строки. В качестве ссылки в каждом конкретном случае выступает ячейка или числовая область, для которой нужно проставить порядковый номер. Чтобы проставить нумерацию строк, используя функцию «СТРОКА»:

Установите курсор на ячейку, которая станет началом отсчета.
Перейдите в строку функции и введите символ «=».
Введите функцию «=СТРОКА». В скобках укажите ссылку на ячейку, которая станет начало нумерации. Например: «=СТРОКА(A1)».
Убедитесь в том, что она подсветилась синим цветом.
Кликните Enter.
После этого вам остается только протянуть ячейку вниз, используя маркер заполнения. Только в отличие от первого способа маркер выделяет не две ячейки, а одну.

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

Проставьте в первую ячейку цифру, с которой будет начинаться отсчет. Например, «1».
Выделите область ячеек, которую требуется пронумеровать, обязательно захватывая первую ячейку.
Перейдите в раздел «Главная». Она находится в левом верхнем углу.
Выберите блок «Редактирование».
Кликните по вкладке «Заполнить».

 

Выберите пункт «Прогрессия». Параметр «Расположение» указывает на направление нумерации – «по столбцам» или «по строкам». Для выбора подходящего варианта необходимо установить напротив него флажок. Параметр «Тип» указывает формат используемой прогрессии.
Для прославления сквозной нумерации в Эксель применяется арифметическая прогрессия. В поле «Шаг» указывается цифра «1». В поле «Предельное значение» указывается количество строк, которые подлежат нумерации. Если оставить эту ячейку пустой, нумерация не проставится.
После того как все исходные данные будут указаны, нажмите на кнопку «ОК».

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

Выделить стартовую ячейку на странице или в конкретной области, в которой нужно проставить нумерацию.
Указать последовательность с требуемым шагом, например: «1,2,3,4,5» или 5,10,15,20,25».
Выделить ячейки с цифровыми значениями.
Перетащить маркер автозаполнения по диапазону.

Чтобы проставить сквозную нумерацию Excel в возрастающем порядке, маркер перетаскивается вниз или вправо, в убывающем — вверх или влево. Стоит учесть, что при изменении строк числовые значения обновляться не будут. Для обновления данных выберите два числа и протяните маркер в конец выделенной области.
Как пронумеровать строки с объединенными ячейками
Иногда в процессе работы с Excel приходится нумеровать объединенные ячейки. Для решения этой задачи ни один из предыдущих способов не подходит. На все ваши попытки пронумеровать ячейки подобного формата программа будет отвечать отказом.
Однако все же есть способ, как сделать сквозную нумерацию в Экселе, при условии наличия объединенных ячеек. На курсах Excel https://kemerovo.videoforme.ru/computer-programming-school/excel-courses объясняют, что для этого требуется проставить функцию, написание которой зависит от количества используемых строк. Предположим, нумерация начинается с ячейки А2. При этом А3 и А4 – это объединенные ячейки. Тогда, а А3 проставляется следующая формула - =МАКС(А$1:А2)+1. После этого необходимо выделить все ячейки, и вписать в строку формулы символ «$». После этого нужно одновременно нажать клавиши Ctrl и Enter.
 
Отображение или скрытие маркера заполнения
Маркер заполнения отражается во всех версиях Microsoft Excel по умолчанию. При этом его можно включить или отключить. Для этого необходимо:

Перейти в раздел «Файл», и выбрать команду «Параметры Excel».

 

Перейти во вкладку «Дополнительно». В открывшемся окне выбрать раздел «Параметры правки».
Чтобы маркер заполнения стал видимым, установите флажок в поле «Разрешить маркеры заполнения ……». Чтобы скрыть его, достаточно просто убрать флажок.

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

Укажите в ячейке цифру, с которой начнется отсчет.
В следующую за ней ячейку введите формулу - =МАКС($A$1:A1)+1, где $A$1 – начало области, в которой нужно проставить нумерацию.

 
Если нужно пропустить конкретные ячейки, просто удалите из них формулу, и нумерация автоматически обновится. Кроме того, формулу можно применять в любом другом месте. Это отличный способ для тех, кто не знает, как проставить нумерацию в Excel, и сделать так, чтобы она самостоятельно обновлялась.
Способ 6 —нумерация с использованием функций =СЧЁТЗ() и =ЕСЛИ()
Этот способ позволяет максимально автоматизировать работу с ячейками и свести к минимуму ваши усилия в процессе проставления сквозной нумерации. Для этого следует воспользоваться такими комбинациями функций, как: СЧЁТЗ и ЕСЛИ. Функция СЧЁТЗ определяет число заполненных ячеек в рамках выделенной области.
Для того, чтобы проставить адаптивную нумерацию, нужно вставить в ячейку формулу =ЕСЛИ(B2=»»;»»;СЧЁТЗ($B$2:B2)) и протащить вниз. Программа автоматически пропустит пустые ячейки.
Это отличный способ, как поставить нумерацию в Excel, при условии, если вам приходится работать с большими массивами разноформатных данных.
Повторяющиеся кавычки указывают на то, что ячейки не должны содержать никаких значений. Таким образом, программа сможет просчитать количество заполненных ячеек и проставить в них нумерацию.
Как пронумеровать строки в Excel с помощью автозаполнения
Простейший способ, который идеально подойдет новичкам, которые только начали осваивать тонкости работы Экселя, и еще не владеют большим объемом практических навыков. Если вы не знаете, что такое сквозная нумерация в Эксель как сделать так, чтобы порядковые номера проставлялись автоматически, воспользуйтесь данным способом. Для этого:

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

Маркер автозаполнения продолжит заполнять ячейки данными с учетом заданной последовательности.
Заключение
Как видите, существует множество способов, как поставить номер в Экселе. Выбор подходящего варианта зависит от объема обрабатываемых данных и навыков работы с функциями и формулами. Самый быстрый и простой способ – использовать маркер автозаполнения. Другие варианты также не менее эффективны, но они пользуются популярностью только у продвинутых пользователей. Да и времени они занимают больше, чем использование маркера заполнения. Несмотря на это, каждый способ имеет свою практическую ценность.
Excel 2010: как объединить ячейки
Задача объединения ячеек на практике возникает довольно часто. Оформление заголовков (шапок) таблиц, подготовка реестров, списков рассылки — примеры могут быть самые разные. Но проблема остается одна и та же: взять данные из нескольких ячеек, объединить их и поместить в одну ячейку рабочего листа. Хочу обратить ваше внимание. В данном случае речь идет не о параметре форматирования, который называется « Объединение ячеек ». Наша задача заключается в объединении данных, причем данные эти могут быть разного типа. И это (в некотором смысле) усложняет задачу. Хотя на самом деле, ничего сложного здесь нет. Все, что нужно вспомнить, — это работа со встроенными функциями преобразования данных и специальные операции MS Excel. Этим мы сейчас и займемся. Но для начала определимся с таблицей для нашего примера.
Я остановил свой выбор на базе данных, фрагмент которой показан на рис. 1. Это реестр сотрудников, который я получил из программы « 1С », преобразовал в формат MS Excel и которым собираюсь воспользоваться в качестве списка рассылки. Изначально в исходной базе были такие поля. В колонках « A:C » под заголовками « Фамилия », « Имя » и « Отчество » записаны соответствующие данные для конкретного сотрудника. Далее в колонках « E:H » расположены серия, номер паспорта, кем и когда он выдан. Эти сведения тоже хранятся в отдельных столбцах рабочего листа. Начиная с колонки « J » идут другие данные о сотруднике (сумма договора, адрес, ИНН, дата рождения и т. п.). Эта информация для нас уже не принципиальна. Итак, список рассылки у меня готов. И теперь я решил проанализировать документ, в который нужно внедрить поля слияния из имеющегося реестра. Оказалось, что будет удобно сформировать две дополнительные колонки, куда записать фамилию, имя и отчество сотрудника (одной строкой) и сведения о паспортных данных (тоже в виде одной строки). Иными словами, мы должны в отдельной колонке объединить данные из столбцов « A:C », а потом выполнить такое же объединение данных для колонок « E:H ». Посмотрим, какие инструменты для решения этой задачи нам предложит Excel 2010.
Объединение ячеек через функцию «СЦЕПИТЬ()»
Самый простой способ объединить данные из нескольких ячеек — воспользоваться функцией « СЦЕПИТЬ() ». Эта функция находится в категории « Тестовые » и может содержать до 255 параметров. Каждый параметр представляет собой текстовую строку или ссылку на ячейку, где записан текст. Функция объединит данные из всех своих параметров и вернет в ячейку результат в виде одной текстовой строки. Применим функцию « СЦЕПИТЬ() » для объединения сведений о фамилии, имени и отчестве в нашей таблице. Делаем так.
1. Открываем базу данных, как на рис. 1. Добавляем колонку для будущего результата. В нашем примере — это колонка « D », назовем ее « ФИО ».
2. Становимся на ячейку « D2 », щелкаем на значке вызова мастера функций (иконка « fx » в строке формул). Откроется окно Мастера функций, как на рис. 2.
3. В списке « Категория: » выбираем вариант « Текстовые ».
4. В списке « Выберите функцию: » находим строку « СЦЕПИТЬ() » и нажимаем « ОК ». Откроется окно с параметрами функции, как на рис. 3.
5. Оставаясь в поле для параметра « Текст1 », щелкаем левой кнопкой мышки по ячейке « A2 ».
6. Переходим в окно параметра « Текст2 », вводим символ « » (пробел) — он нужен для того, чтобы отделить фамилию от имени сотрудника. В окне с параметрами функции появится дополнительной окошко с названием « Текст3 ».
7. Переходим в окно для параметра « Текст3 », щелкаем левой кнопкой на ячейке « B2 ». В окне с параметрами функции появится дополнительной окошко с названием « Текст4 ».
8. Переходим в окошко « Текст4 », вводим символ « » (пробел) — отделяем имя сотрудника от его отчества.
9. Переходим в окошко « Текст5 », щелкаем левой кнопкой на ячейке « С2 ». В результате окно с параметрами должно выглядеть, как показано на рис. 3.
10. В окне « Аргументы функции » нажимаем « ОК ».
В результате наших действий в ячейке « D2 » появится формула « =СЦЕПИТЬ(A2;" ";B2;" ";C2) », а текст в ячейке « D2 » будет выглядеть так: « Григорьева Нина Михайловна ». Остается скопировать формулу на всю высоту таблицы, и реестр в первом приближении готов.
Прежде чем сделать выводы относительно способов объединения ячеек, предлагаю посмотреть на другие методы решения этой задачи.
Объединение данных операцией «&»
Альтернативным вариантом объединения данных в ячейках является операция « & » (на большинстве клавиатур знак « & » находится на цифре « 7 »). Правила использования операции « & » точно такое же, как и при выполнении арифметических действий. То есть при написании формулы символ « & » нужно ставить в каждой «точке соединения» текстовых строк.
Важно! Если в формуле с операцией « & » используется текст, его нужно обязательно заключить в двойные кавычки.
Поясню сказанное на примере. Предположим, я хочу написать формулу, с помощью которой объединить три строки: « Бухгалтер », « & », « Компьютер ». В Excel эта формула будет выглядеть так: « ="Бухгалтер"&" & "& "Компьютер" ». Обратите внимание, что в ней первый и третий симоволы « & » — это знак операции, а второй символ « & » (выделен полужирным начертанием) — текстовая строка (операнд). Посмотрим, как применить операцию « & » для нашего примера. Делаем так.
1. Открываем базу данных, как на рис. 1.
2. Становимся на ячейку « D2 », нажимаем « = ».
3. Щелкаем на ячейке « A2 » (в строке формул должно получиться « =A2 »).
4. Печатаем символ « & » (в строке формул будет выражение « =A2& »).
5. С клавиатуры вводим текст « " " » (двойная кавычка, пробел, еще одна двойная кавычка).
6. Снова вводим символ « & ».
7. Щелкаем на ячейке « B2 ».
8. Вводим « & » и разделитель « " " » (пробел).
9. Щелкаем на « С2 » и нажимаем « Enter ». В результате должна получиться формула: « =A2&" "&B2&" "&C2 ». Копируем ее на всю высоту таблицы.
Как объединить данные разного типа
При объединении данных (функцией « СЦЕПИТЬ() » или с операцией « & ») бывают ситуации, когда исходные данные представлены в разных форматах: числа, даты, логические выражения и т. п. В этом случае нужно помнить, что при таком объединении Excel преобразует все данные в текстовый формат. В определенных ситуациях такое преобразование будет некорректным, поэтому его лучше сделать самому при помощи встроенной функции « ТЕКСТ() ».
В качестве примера я предлагаю сформировать строку из серии, номера паспорта сотрудника и даты его выдачи, воспользовавшись операцией « & ». Делаем так.
1. Открываем базу данных, как на рис. 1.
2. Становимся на любую свободную ячейку внутри базы (например, на « K2 »).
3. Вводим формулу « ="паспорт сер. "&E2&", N "&F2&", выдан "&G2& ", "& H2 ».
4. Нажимаем « Enter ». В ячейке « K2 » появится текст: « паспорт сер. ММ, N 676757, выдан Киевским РО ХГУ УМВД Украины в Харьк. обл., 36511 ».
В целом все правильно, за исключением загадочного текста « 36511 » в конце итоговой строки. Такой результат — следствие преобразования даты « 17/12/1999 » в текстовый формат.
Чтобы устранить проблему, нужно в формуле заменить ссылку на ячейку « H2 » функцией « ТЕКСТ() », в которой четко определить шаблон преобразования данных. И тогда формула будет выглядеть так: « ="паспорт сер. "&E2&", N "&F2&", выдан "&G2& ", "& ТЕКСТ(H2;"ДД/ММ/ГГГГ") », а в результате мы получим строку « паспорт сер. ММ, N 676757, выдан Киевским РО ХГУ УМВД Украины в Харьк. обл., 17/12/1999 ».
Функцию « ТЕКСТ() » применяют в большинстве случаев, когда к строке нужно добавить числовое значение. Элементарный пример. Предположим, что в ячейке « A1 » записан текст « Процентная ставка ». Само значение этой ставки равно « 0,2 » и записано оно в ячейку « A2 ». Причем « A2 » отформатирована с двумя знаками после запятой. То есть на рабочем листе в « A2 » мы видим результат « 0,20 », и это именно то, что нам нужно. Если ввести формулу « =A1&": "&A2 », мы получим текст « Процентная ставка: 0,2 », что не совсем верно. Правильной будет формула « =A1& ": "& ТЕКСТ(A2;"0,00 ") », которая вернет значение « Процентная ставка: 0,20 ».
И последний момент по работе с функциями объединения текста. Иногда нужно сделать так, чтобы в определенном месте результирующего текста происходил переход на новую строку. Такая ситуация характерна, например, для оформления шапок таблицы с переносом по словам. Чтобы добиться такого эффекта в формуле объединения можно воспользоваться функцией « СИМВОЛ() ». Эта функция позволяет вставить в текст любой знак из таблицы символов системы Windows. Чтобы ввести такой символ, в параметре функции нужно указать его цифровой код. Например, код « 0151 » соответствует знаку «тире», код « 013 » означает «перевод каретки» и т. д. Для принудительного разрыва строки нам понадобится специальный символ с кодом « 010 ». И тогда формула для формирования паспортных данных может выглядеть так: « ="паспорт сер. "&E2&", N "&F2&", выдан "&СИМВОЛ(10)&G2& ", "&СИМВОЛ(10)&ТЕКСТ(H2;"ДД/ММ/ГГГГ") ». В таком варианте в первой строке будет напечатан текст « паспорт сер. ММ, N 676757, выдан », под ним — текст « Киевским РО ХГУ УМВД Украины в Харьк. обл., », и только в последней строке — дата « 17/12/1999 ».
Важно! Перенос текста при использовании функции « СИМВОЛ(10) » будет работать только в том случае, если для ячейки указан параметр форматирования « Переносить по словам ».
Чтобы установить этот параметр, сделайте так.
1. Щелкните левой кнопкой мышки на ячейке с формулой, чтобы сделать ее активной.
2. Перейдите в меню « Главная ».
3. В группе « Выравнивание » щелкните на иконке « Перенос текста » (рис. 4).
Объединение ячеек без потери текста
Такая проблема периодически возникает при форматировании документов. Особенно, если в них есть шапки со сложной, многоуровневой структурой. В общих чертах задача выглядит так. Есть несколько ячеек, в каждой из которых записан текст. Нужно выделить эти ячейки и объединить их в одну. При этом в результирующую ячейку должен попасть весь текст из исходных ячеек (до их объединения). В качестве примера я предлагаю воспользоваться таблицей, изображенной на рис. 5. Это фрагмент бланка «Налоговая накладная», а точнее — надпись в правом верхнем углу этого документа. В ней фигурируют три строки: « ЗАТВЕРДЖЕНО », « Наказ Міністерства фінансів України » и « 01.11.2011 N 1379 ». Сейчас эти строки расположены в отдельных ячейках рабочего листа (в « A1 », « A2 » и « A3 » соответственно). Мне нужно создать одну объединенную ячейку « A1:A3 » и перенести в нее весь текст « ЗАТВЕРДЖЕНО Наказ Міністерства фінансів України 01.11.2011 N 1379 », а затем оформить эту часть с переносом слов и поставить на нужное место на бланке документа.
На первый взгляд, в программе Excel 2010 есть инструмент для решения такой задачи — кнопка « Объединить и поместить в центре » (она расположена на ленте « Выравнивание », рис. 4). Попробуем воспользоваться этой возможностью. Делаем так.
1. Открываем файл с таблицей, как на рис. 5.
2. Выделяем блок ячеек « A1:A3 ».
3. Вызываем меню « Главная ».
4. В группе « Выравнивание » щелкаем на иконке « Объединить и поместить в центре ». На экране появится окно с предупреждением, что часть данных при объединении будет потеряна (рис. 6).
5. В этом окне нажимаем « ОК », результат преобразований показан на рис. 7.
Excel объединил ячейки. Но большую часть текста он при этом потерял. Сохранилось только содержимое верхней левой ячейки блока « A1:A3 ». Нас это, конечно же, не устраивает. Проблему нужно как-то решать, и стандартными средствами Excel здесь не обойтись — придется написать небольшой макрос на языке VBA (Visual Basic for Application). Ничего сложного в этом нет. Тем более что с VBA мы уже работали, причем неоднократно. Да и текст макроса, я бы сказал, получится миниатюрный. Делаем так.
1. Открываем документ, как на рис. 6, переходим в меню « Разработчик ».
Важно! Если вкладка « Разработчик » в вашей версии Excel недоступна, вызовите меню « Файл », затем « Параметры ». Перейдите в раздел « Настройка ленты ». В правой части окна найдите список « Основные вкладки » и включите галочку возле строки « Разработчик ».
2. Щелкаем на иконке « Visual Basic » (рис. 8). Откроется окно, изображенное на рис. 9.
3. Вызываем меню « Insert → Module ». В открывшееся окно вводим такой текст:
Sub MrgToOne ()
Const sDLM As String = " "
Dim rCell As Range
Dim sMrgStr As String
If TypeName(Selection) <> "Range" Then Exit Sub
For Each rCell In .Cells
sMrgStr = sMrgStr & sDLM & rCell.Text
.Item(1).Value = Mid(sMrgStr, 1 + Len(sDLM))
4. Сохраняем файл и закрываем редактор « Visual Basic ».
5. Возвращается к документу, как на рис. 6. Выделяем ячейки « A1:A3 ».
6. В меню « Разработчик » щелкаем на иконке « Макросы » (рис. 8). Откроется окно, как на рис. 10.
7. В этом окне выбираем элемент « MrgToOne » (в нашем файле это единственный макрос) и нажимаем « Выполнить ».
8. Форматируем объединенную ячейку с переносом текста по словам, результат показан на рис. 11.
В данном случае Excel объединил фрагмент рабочего листа и сохранил в нем содержимое всех ячеек исходного блока « A1:A3 ».
Для быстрого обращения к макросу « MrgToOne » можно закрепить его вызов за графическим элементов, или же создать специальную комбинацию горячих клавиш. Я советую использовать второй способ. Для этого делаем так.
1. Вызываем меню « Разработчик », щелкаем на иконке « Макросы » (рис. 8). Откроется окно, как на рис. 10.
2. Выделяем макрос « MrgToOne ».
3. Нажимаем кнопку « Параметры… ». Откроется окно « Параметры макроса », как на рис. 12.
4. В поле « Сочетание клавиш: » вводим любой символ. Главное, чтобы он не пересекался с устоявшимися комбинациями горячих клавиш MS Excel. В примере на рис. 12 это символ « m ».
5. В окне « Параметры макроса » нажимаем « ОК ». Теперь для вызова программы « MrgToOne » нужно выделить блок и нажать « Ctrl+m ».
Кстати, если слегка изменить текст макроса, он будет собирать данные в первой ячейке блока без последующего объединения ячеек:
Const sDLM As String = " "
Dim rCell As Range
Dim sMrgStr As String
If TypeName(Selection) <> "Range" Then Exit Sub
For Each rCell In .Cells
sMrgStr = sMrgStr & sDLM & rCell.Text
.Item(1).Value = Mid(sMrgStr, 1 + Len(sDLM))
При оформлении сложных таблиц такой макрос существенно сэкономит ваше время и силы. Текст макросов вы можете скачать по адресу bk@id.factor.ua .
Успешной работы! Жду ваших писем, предложений и замечаний на bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции.


Похожие публикации:

Как восстановить поврежденную презентацию powerpoint
Как вставить один и тот же текст во все ячейки в excel
Как подключить переключатель света на уаз 469
Почему не работает airdrop на mac