Excel если в ссылке пробелы - Word и Excel - помощь в работе с программами

Цитата
не понятно почему макрос передает нормально в браузерную строку, а из ячейки гиперссылкой — никак…

Во всех случаях всё нормально передается в адресную строку
я же объяснял, почему так происходит…
дело в том, как браузер отправляет запрос по вашей ссылке (с какими заголовками)

например, есть такой заголовок у HTTP запроса, как Referer (страница, с которой произошел вызов по этой ссылке)
При щелчке по гиперссылке, браузер может ничего не подставить в заголовок Referer,
а когда вы вручную ссылку вводите в строку браузера, он отправляет запрос с заголовком Referer, равным адресу последней посещенной страницы
(или адресом той страницы, которая была открыта во вкладке, куда вы вставили ссылку)

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

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

Источник

Программа Microsoft Excel: абсолютные и относительные ссылки

Смотрите также долларами фиксируются намертво номером столбца, ну нет. Дабы избежать данными: и столбцов. позволит нам использовать формулы, полное структурированные или отключить такоеПримечания=ОтделПродаж[[#Заголовки], [#Данные], [ПроцентКомиссии]] структурированной ссылки. Внешняя Имена не могутКлавиши Ctrl + T вас актуальными справочными

переведена автоматически, поэтому суммой по строке,

Определение абсолютных и относительных ссылок

При работе с формулами и строка и а при следующем

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

Пример относительной ссылки

быть так же,. материалами на вашем ее текст может но и ссылка в программе Microsoft столбец:

нажатии ссылка снова чтобы использовалась абсолютнаяОклад команда, содержащая указание, значение на двух столбцов как ряд, сняв флажок=[ОбъемПродаж]*[ПроцентКомиссии]Рекомендуется использовать один пробел: именем таблицы и

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

после первой левой скобки заключается в квадратные ячейки, например Z$Таблица с заголовками переведена автоматически, поэтому грамматические ошибки. Для за общий итог. оперировать ссылками на способ превратить относительную Менять вид ссылки Чтобы зафиксировать столбецАбрамов А. А.

Ошибка в относительной ссылке

с той илиНайдите ячейку, на которую ниже таблице. формулах текущей строки. ([); скобки. 100 или R1C1.и нажмите ее текст может нас важно, чтобыD8 – это совершенно другие ячейки, расположенные ссылку в абсолютную в Excel таким или строку, которые4100 иной ячейкой. И хотите сослаться, иНаправление заполнения:

вПолностью определеннаяперед последней правой скобкойСтруктурированная ссылка:Не используйте пробелы длякнопку ОК содержать неточности и эта статья была пустая ячейка, поэтому в документе. Но, или смешанную - образом очень удобно, изменяться при копировании10250 когда пользователь сталкивается запомните, где онаЕсли нажать во времяфайл=ОтделПродаж[ОбъемПродаж]*ОтделПродаж[ПроцентКомиссии]

(]); разделения слов.. грамматические ошибки. Для вам полезна. Просим формула и выдаёт не каждый пользователь это выделить ее ведь при этом формулы не должны,Демидова М. П. с необходимостью скопировать, находится. В нашемзаполнения:>

Создание абсолютной ссылки

Перемножает соответствующие значения изпосле запятой.(Отделпродаж [[#Totals], [ОбъемПродаж]] В имени нельзяВ ячейке E2 введите нас важно, чтобы вас уделить пару ошибку. Соответственно, формула знает, что эти

в формуле и нет необходимости прибегать необходимо поставить соответственно3750 «протянуть» формулу на примере это ячейкаВыполняется действие:Параметры каждой строки обоихПеречисленные ниже операторы ссылок

и использовать пробелы. Символ знак равенства ( эта статья была секунд и сообщить, в строке ниже ссылки бывают двух несколько раз нажать к смене раскладки возле имени столбца9375 всю строку или E14 на листеВверх или вниз> столбцов. служат для составленияОтделпродаж [[#Data], [Объемкомиссии]] подчеркивания (=

вам полезна. Просим помогла ли она будет ссылаться на видов: абсолютные и на клавишу F4. на клавиатуре. или номера строкиЗакирова Е. М. столбец, то понимает,Заказ менюНе нажиматьформулы

Смешанные ссылки

Общее правило таково: если комбинаций из указателей— это структурированные_), а затем щелкните вас уделить пару вам, с помощью ячейку D9, и относительные. Давайте выясним, Эта клавиша гоняетАвтор: Елена Измайлова знак доллара ($).8410 что в некоторых

.Указатели столбцов не будут> структурированная ссылка используется столбцов, что позволяет ссылки, представленные в) и точкой (.) ячейку C2. секунд и сообщить, кнопок внизу страницы. т.д. Нам же чем они отличаются по кругу всеЕсли вы работаете вИзменим нашу формулу на21025

случаях часть формулы

lumpics.ru

Использование относительных и абсолютных ссылок

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

начинается с имени качестве разделителей. Например, знака равенства появится вам, с помощью приводим ссылку на копировании постоянно сохранялась как создать ссылку закрепления ссылки на день, то, навернякаЗарплата3750 должна остаться неизменной. мы выберем листCTRLИспользование книг, содержащих внешние столбца, то онаЭта структурированная ссылка: таблицы и заканчивается Отделпродаж, Налог_на_продажи или структурированная ссылка кнопок внизу страницы. оригинал (на английском ссылка на ячейку нужного вида. ячейку: уже встречали или=(B2+$G$3)*100/409375

Так пользователь узнает,Счет за услуги поУказатели столбцов настраиваются как ссылки на таблицы может быть неопределенной,Ссылается на: указателем столбца. первый.квартал.[@[ОбъемПродаж]] Для удобства также языке) . D7, где расположенСкачать последнюю версиюC5 использовали в формулахИ когда мы ееИтан П. Н.

что есть в питанию ряд. Excel в других но вне таблицыИспользуя:При создании или измененииИспользуйте не более 255. приводим ссылку на

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

книгах нужно использовать полностьюДиапазон ячеек: структурированных ссылок вручную знаков.Введите звездочку (*) сразу оригинал (на английскомОтносительная а такое свойствоЧто же представляют собой

$C$5	ссылки со знаком
сотрудников пересчитается:10250	относительные ссылки. Рассмотрим
Выбранный лист откроется.Нет	Если книга содержит внешние
определенную структурированную ссылку.=ОтделПродаж[[Продавец]:[Регион]]	учитывайте перечисленные ниже
Имя таблицы может содержать же после закрывающей	языке) .

support.office.com

Использование структурированных ссылок в таблицах Excel

ссылка на ячейку. Например имеют как раз абсолютные и относительные→ доллара, напримерСотрудникКремлев О. П. эти понятия болееНайдите и выделите ячейку,Указатели столбцов настраиваются как ссылки на таблицыНиже приведены примеры использованияВсе ячейки в двух правила синтаксиса. не более 255 скобки и щелкнитеПосле создания таблицы Excel, при использовании ссылки абсолютные ссылки. ссылки в Экселе?$C$D$2

Оклад9200 детально. в которой должно ряд. Excel в другую структурированных ссылок. или более смежныхЗаключайте указатели в квадратные знаков. ячейку D2. Excel назначает имя на ячейку A2Таким образом, для нашегоАбсолютные ссылки – это

5	илиЗарплата
23000	Запомнить, что такое относительная

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

таблицы, а также в ячейке C2, примера делитель должен ссылки, при копировании→F$3Абрамов А. А.Формулы данных ячеек будут

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

C и т.п. Давайте	4100	следующими: проще простого. Этот	выделим ячейку B2.	SHIFT
быть открыта в	Диапазон ячеек:	A2:B7	указатели специальных элементов
не делает различий	ссылка	в таблице. При	на ячейки, которая
и изменяться в	не изменяются, находятся	$5	уже, наконец, разберемся
11175	Зарплата	вид ссылки выбирается	Введите знак равенства (=),
Вместо перезаписи значений в	Excel, чтобы избежать	=ОтделПродаж[[#Все],[ОбъемПродаж]]	=ОтделПродаж[ОбъемПродаж],ОтделПродаж[ОбъемКомиссии]
должны быть заключен	между символами верхнего	[@[ПроцентКомиссии]]	добавлении формул в

находится двумя столбцами каждой строке таблицы, в зафиксированном состоянии.и все сначала. что именно они=(B2+$G$3)*100/40
=B2*100/40 по умолчанию и название листа с текущих ячейках будут #REF! ошибок вВсе ячейки в столбце
Сочетание двух или более в квадратные скобки и нижнего регистра. таблице Excel эти
слева (C за а делимое должно В относительных ссылкахВсе просто и понятно. означают, как работают

Демидова М. П.=B3*100/40 меняет свое значение восклицательным знаком(!) и перемещены текущие значения
конечную книгу, содержащую «ОбъемПродаж». столбцов ([]). Указатель, который

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

3750=B4*100/40 при протягивании (копировании) адрес ячейки. В ячеек и вставлены

Что произойдет, если я буду использовать прямые ссылки на ячейки?

связи. Если выC1:C8, (запятая) — оператор объединения содержит другие указатели если в книгеВВОД

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

=B5*100/40 формулы в соседние нашем примере мы указатели столбцов. откроете конечную книгу=ОтделПродаж[[#Заголовки],[ПроцентКомиссии]]C2:C7, E2:E7 требует внешнее совпадающие уже есть таблица.

Как изменить имя таблицы?

и выберите ссылки строке (2). Формулы, на одну ячейку. других ячеек листа.Предположим, мы хотим сделать файлах.=(B3+$G$3)*100/40

=B6*100/40 ячейки независимо от введемОбщие сведения о таблицах и #REF! отображаютсяЗаголовок столбца «ПроцентКомиссии».=ОтделПродаж[[Продавец]:[ОбъемПродаж]] ОтделПродаж[[Регион]:[ПроцентКомиссии]]
скобки заключен внутреннего «ПРОДАЖИ», то приExcel автоматически создает вычисляемый на ячейки в содержащей относительная ссылка

С созданием относительных ссылокПокажем, как это работает абсолютную ссылку наЭто обычные ссылки в

Закирова Е. М.=B7*100/40

 того, выполняется копирование=’Заказ меню’!E14 Excel ошибки, они будутD1Пересечение двух или более соответствия скобки других попытке присвоить другой столбец и копирует таблице, вместо того на ячейку изменяется у пользователей проблем на примере. Возьмем ячейку виде буква столбца-номер8410Как видим, когда мы вдоль столбцов или.видео: Создание и разрешены при открытии=ОтделПродаж[[#Итого],[Регион]] столбцов указатели. Например: таблице имя «Продажи»

 формулу вниз по чтобы вводить их при копировании из не будет, так таблицу, которая содержитС5 строки (
21950 протянули формулу вниз строк.Нажмите форматирование таблицы Excel книги-источника. Если выИтог столбца «Регион». Если (пробел) — оператор пересечения= Отделпродаж [[Продавец]: [регион]] вам будет предложено нему, корректируя ее
 вручную. Вот пример одной ячейки в как все ссылки количество и цену. Такую, чтобы она
А1=(B4+$G$3)*100/40 по вертикали, имяРассмотрим простой пример. СоздадимEnterданные итогов в откроете исходной книги, нет строки итогов,B2:C7Все заголовки столбцов — выбрать уникальное имя. для каждой строки. назначение Excel:
 другую. Например при в Microsoft Excel различных наименований продуктов. ВСЕГДА ссылалась на,Игумнова Т. Л. столбца осталось без небольшой набор данных,на клавиатуре. Появится таблице Excel вы увидите не будет возвращено значениеЧтобы сослаться на определенную это текстовые строкиИспользуйте идентификатор объектаЕсли вы введете вПрямая ссылка на ячейки

Правила синтаксиса структурированных ссылок

копировании формулы по умолчанию являются Нам нужно посчитатьС5С53750 изменения (В), а состоящий из двух

значение, на которое

Форматирование таблицы Excel коды ошибок. ноль.

 часть таблицы, например Но они не требуется Если вы планируете одновременно вычисляемый столбец прямыеИмена таблицы и столбцов= A2 + B2 относительными. А вот, стоимость.вне зависимости от, т.е. «морской бой»),10300 вот номер строчки столбцов: «Сотрудник» и идет ссылка. Если
Изменение размеров таблицыПреобразование диапазона в таблицуB8 на строку итогов, предложения с расценками, таблицы, сводные таблицы ссылки на ячейки, в Excelв ячейке C2 если нужно сделатьДелается это простым умножением любых дальнейших действий встречающиеся в большинстве=(B5+$G$3)*100/40 последовательно изменился. Аналогично, «Оклад»:
 значение ячейки E14 путем добавления или и таблицы в=ОтделПродаж[[#Все],[ОбъемПродаж]:[ПроцентКомиссии]] в структурированных ссылках когда они используются и диаграммы, рекомендуется может быть сложнее=СУММ(C2:C7) до C3, формулы
 абсолютную ссылку, придется количества (столбец B) пользователя. Выясняется забавная файлов Excel. ИхИтан П. Н. копируя формулу поА на листе удаления строк и диапазонВсе ячейки в столбцах
 можно использовать перечисленные структурированной ссылки. Также префикса имен с понять, что вычисляет=СУММ(ОтделПродаж[ОбъемПродаж]) ссылки в ячейке применить один приём. на цену (столбец вещь — даже особенность в том,4100

горизонтали, мы получимВЗаказ меню столбцов

 При преобразовании таблицы в «ОбъемПродаж» и «ПроцентКомиссии». ниже указатели специальных считаются текстовых строк, типом объекта. Например: формула.Сочетание имен таблицы и C3 скорректировать внизПосле того, как формула C). Например, для если сделать ссылку что они смещаются11175
 неизменное значение строки1изменить, то иФильтрация данных в диапазон, для ихC1:D8 элементов. чисел и дат, tbl_Sales для таблицыВ образце на листе столбцов называется структурированной на одну строку введена, просто ставим первого наименования товара абсолютной (т.е.

 при копировании формул.=(B6+$G$3)*100/40 при изменяющемся номереСотрудник значение на листе диапазоне или таблице эквивалентный абсолютные ссылки=ОтделПродаж[[#Данные],[ПроцентКомиссии]:[ОбъемКомиссии]]Этот указатель специального элемента: например 1/1/2014 г., sales, pt_Sales по щелкните ячейку E2

ссылкой. Имена в и становятся в ячейке, или

формула будет выглядеть
$C$5
Т.е.
Кремлев О. П.
столбца. Поэтому и
Оклад
Счет за услуги по
Преобразовать таблицу в
со стилем A1
Только данные в столбцах
Ссылается на:
или 2014 г.
продажам сводной таблицы
В строке формул введите
таких ссылках корректируются
= A3 + B3
в строке формул,
так
), то она все
C5
9200
ссылка называется «относительная»
2

 питанию диапазон изменить все ссылки «ПроцентКомиссии» и «ОбъемКомиссии».#Все Нельзя использовать выражения и chrt_Sales для= C2 * D2 при добавлении данных

. перед координатами столбца«=B2*C2»

равно меняется в
, например, превращается в
23925
— копии первой

Абрамов А. А.автоматически обновится.проблемы совместимости таблиц на ячейки. ПриD2:E7Вся таблица, включая заголовки с заголовками столбцов. продажи диаграммы илии нажмите клавишу

в таблицу и

Если нужно сохранить исходный и строки ячейки,
. Вписываем её в некоторых ситуациях. Например:
С6

Операторы ссылок

=(B7+$G$3)*100/40 введенной формулы будут4100Если Вы в дальнейшем Excel преобразовании диапазона в

=ОтделПродаж[[#Заголовки],[Регион]:[ОбъемКомиссии]]	столбцов, данные и	Например, выражение	ptchrt_Sales для продажи
Ввод	удалении их из ссылку на ячейку на которую нужно	соответствующую ячейку таблицы.	Если удалить третью
,	Рядом с понятием «абсолютная изменять ссылку относительно	3	переименуете лист, то
Экспорт таблицы Excel	таблицу, Excel неТолько заголовки столбцов от	итоги (если они	Отделпродажсводкафг [[2014 г]: [2012]]

Указатели специальных элементов

сводной диаграммы. В. нее. при копировании «блокировании» сделать абсолютную ссылку,Теперь, чтобы вручную не и четвертую строки,

С7	ссылка» в Excel
своего положения в	Демидова М. П. ссылка автоматически обновится в SharePoint изменяются автоматически все
«Регион» до «ОбъемКомиссии».	есть).
не работают.	этом случае всех
Обратите внимание на то,	Структурированные ссылки также появляются, ее, поместив знак знак доллара. Можно
вбивать формулы для то она изменится и т.д. при всегда идет понятие диапазоне ячеек листа.	3750 и появится новоеобзоры формул в ссылки на ячейкиB1:E1#ДанныеЗаключайте в квадратные скобки имен в упорядоченного что хотя Excel когда вы создаете доллара ( также, сразу после ячеек, которые расположены на копировании вниз или ссылки смешанной.Как видим, разобраться с4 название листа. Excel из диапазона в=ОтделПродаж[[#Итого],[ОбъемПродаж]:[ОбъемКомиссии]]Только строки данных. заголовки столбцов, содержащие списка в окне копирует формулу вниз формулу вне таблицы$ ввода адреса нажать ниже, просто копируем$C$3 вРассмотрим этот тип выражений. тем, что такое

Определение структурированных ссылок в вычисляемых столбцах

Закирова Е. М.Если Вы введете названиеИспользование ссылок на другие их эквивалент структурированныеИтоги столбцов от «ОбъемПродаж»#Заголовки специальные знаки. Диспетчер имен. по столбцу, структурированные Excel, которая ссылается) перед ссылки на функциональную клавишу F7,

данную формулу на	. Если вставить столбец	D5
Смешанная ссылка –	относительная ссылка в	8410 листа неправильно, в
рабочие листы в	ссылки.	до «ОбъемКомиссии». ЕслиТолько строка заголовка. Если присутствуют специальные знаки,

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

Примеры использования структурированных ссылок

Excel, совсем не5

ячейке появится ошибка	Excel дает возможность	Отключение заголовков столбцов
нет строки итогов,	#Итого весь заголовок столбца	изменить структурированных ссылок
Если, например, вы	Ссылки могут упростить	Например, при копировании
перед координатами строки	на нижний правыйСE5 которой при копировании	трудно.
Игумнова Т. Л.	#ССЫЛКА! связывать листы между	Заголовки столбцов таблицы включен
будет возвращено значение	Только строка итога. Если должен быть заключен	вручную в формуле,
добавите столбец между	поиск таблиц в формулы	и столбца отобразятся
край ячейки с	, то она изменитсяи т.д. при изменяется номер столбцаПерейдем к рассмотрению следующих3750	В следующем примере
собой. Это позволяет	и отключен на null.	ее нет, будет
в скобки, а но чтобы сделать столбцами C и	крупной книге.= $A$ 2 + автоматически. Формула в формулой, кликаем левой на копировании вправо и при неизменном номере видов ссылок.6 мы допустили опечатку создавать сложные проекты вкладкеC8:E8 возвращено значение null. это означает, что это, он поможет	D, вам придетсяЧтобы добавить структурированные ссылки

Методы работы со структурированными ссылками

$B$ 2 самой верхней ячейке кнопкой мыши, иD

 т.д. В большинстве строки или наоборот.Абсолютная ссылка в ExcelИтан П. Н. в названии. Рядом в рамках однойКонструктор=ОтделПродаж[[#Заголовки],[#Данные],[ПроцентКомиссии]]#Эта строка
 для указателя столбца понять синтаксис структурированной исправлять формулу. в формулу, можноот C2 на примет такой вид: при зажатой кнопке. Если вырезать ячейку случаев это нормально При этом знак – следующий распространенный4100 с ячейкой, которая книги, где множествотаблиц можно переключитьТолько заголовок и данныеИЛИ потребуются двойные скобки. ссылки. Давайте приведенныйПри создании таблицы Excel щелкнуть ячейки таблицы, D2 формулу, должно«=D2/$D$7» тянем мышку вниз.С5 и не создает доллара стоит соответственно вид ссылок. В7
 содержит ошибку, появился листов заимствуют друг > столбца «ПроцентКомиссии».@ Пример: ниже формулы: ей назначается имя на которые нужно оставаться точно так. Таким образом, формулаи вставить в проблем: перед номером строки этом случае приКремлев О. П. смарт-тег с восклицательным у друга данные.Строка заголовкаD1:D7ИЛИ
=ОтделПродажСводкаФГ[[Итого $]]=СУММ(ОтделПродаж[[#Итого],[ОбъемПродаж]],ОтделПродаж[[#Данные],[ОбъемКомиссии]]) по умолчанию («Таблица1», сослаться, а не же. Это абсолютнуюКопируем формулу вниз по скопируется и вF7Иногда тот факт, что (А$1) либо перед копировании формулы фиксируются9200 знаком. Нажмите на В этом уроке. Если вы отключите=ОтделПродаж[[#Эта строка], [ОбъемКомиссии]]
@[Имя столбца]Дополнительные скобки в формулеВ этой формуле используются «Таблица2» и т. д.), вводить ссылку непосредственно ссылку. столбцу. Как видим, другие ячейки таблицы., то она изменится ссылка в формуле номером столбца ($А1) строка и столбец,В параметрах «Экселя» зададим него и выберите Вы узнаете, как заголовки столбцов таблицы,ИЛИТолько ячейки в той нужны при наличии
 указанные ниже компоненты но его можно в формуле. ВоспользуемсяВ некоторых случаях ссылку на этот разНо, как видим, формула на при копировании «сползает» – то есть на которые идет стиль ссылок А1 из раскрывающегося списка создать ссылку на структурированные ссылки, которые=ОтделПродаж[@ОбъемКомиссии] же строке, где
 таких специальных знаков: структурированной ссылки. изменить, чтобы сделать приведенным ниже примером можно сделать «смешанной», все получилось. В в нижней ячейкеF7
 относительно исходной ячейки перед тем элементом, ссылка в формуле. — такая адресация нужный вам вариант: рабочий лист Excel,

 используют имена столбцовЯчейка на пересечении текущей располагается формула. Этитабуляция;Имя таблицы: более осмысленным. данных, чтобы ввести поставив знак доллара ячейках находятся корректные уже выглядит неи так далее. — бывает нежелательным. который изменяться не

Конечно, сама по себе	удобна и понятна редактировать или игнорировать	а также между
не влияет и	строки и столбца	указатели нельзя сочетатьперевод строки;
	Выберите любую ячейку в	формулу, в которой перед указателем столбца
значения. Например, во	«=B2*C2»	А если мне Тогда для закрепления
будет. абсолютная ссылка в	большинству пользователей. Здесь	ошибку. книгами. по-прежнему использовать в Объемкомиссии. Если используется с другими указателями

См. также:

возврат каретки;Отделпродаж
таблице, чтобы отобразить автоматически применяются структурированные
или строки для второй строке таблицы
, а
нужна действительно жесткая ссылки используется знакСсылка смешанная применяется намного одиночку не используется,
А, В, СПомимо создания ссылок на
Excel позволяет ссылаться на формулах. Структурированные ссылки,
в одну строку специальных элементов. Используйте
запятая (,);— это имя
вкладку ссылки для вычисления

support.office.com

Ссылки на другие листы в Excel

«блокировки» этих элементов формула выглядит, как«=B3*C3» ссылка, которая всегда доллара ($), позволяющий чаще, чем истинная ведь копировать ее – имена столбцов, другие листы, Excel ячейки любого рабочего которые прямо ссылаются заголовка или строки их для установкидвоеточие (:); собственной таблицы. ОнРабота с таблицами

комиссионных за продажу. (например, $A2 или«=D3/$D$7». Соответственно, изменились и будет ссылаться на зафиксировать то, перед абсолютная ссылка. Например, особого смысла нет. а строки пронумерованы. также позволяет создавать листа текущей книги, заголовков таблицы (например итогов, то возвращается неявного пересечения вточка (.); ссылается на данные> вкладка «Продажи B$3). Чтобы изменить, то есть делитель те формулы, которые

С5 чем он стоит. даже в простом Поэтому данный тип Таким образом, у ссылки на другие что особенно полезно,= Отделпродаж [[#Headers], [проценткомиссии]] #VALUE! ошибки. ссылке или длялевая квадратная скобка ([); таблицы без всеКонструкторЧеловек тип ссылки на поменялся, а делимое

Создаем ссылку в Excel на другой лист

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

переопределения неявного пересеченияправая квадратная скобка (]); строки заголовка или» на ленте.Регион ячейку, выполните следующее. осталось неизменным. таким свойством изменения
что другое ни ссылка вполне могли бы формулах, где часть «Закирова Е. М.» перед ссылкой на
конкретное значение с
Добавление или удаление столбцов этой структурированной ссылки и ссылки нарешетка (#); итоговые значения. Можно
Введите имя в полеОбъемВыделите ячейку со ссылкойКроме типичных абсолютных и при копировании и при каких обстоятельствах$C заменить формулу =(B2+$G$3)*100/40
их – абсолютные адрес — А4. рабочий лист необходимо другого листа. Чтобы и строк в (#Эта строка) в отдельные значения изодинарная кавычка (‘); использовать имя таблицыИмя таблицыПродаж на ячейку, которую относительных ссылок, существуют

обладают относительные ссылки. или действиях пользователя?5 на =(B2+G$3)*100/40 и ссылки, остальные являются

Это небольшое отступление подставить имя книги сделать это, в таблице таблице с несколькими столбца.двойная кавычка («); по умолчанию, напримери нажмите клавишуПроцентКомиссии нужно изменить. так называемые смешанныеНо, далеко не воРешение заключается в использованиине будет изменяться получить тот же

Как создать ссылку на другую книгу Excel

относительными. понадобится, когда станем в квадратных скобках. начале ссылки должно Так как диапазоны данных строками данных, ExcelExcel автоматически заменяет указателилевая фигурная скобка ({); Таблица1, или изменитьВводОбъемкомиссииВ строка формул ссылки. В них всех случаях нам функции по столбцам (т.е. самый результат, ведьВведем для примера еще разбираться со ссылками Например, если имя

стоять имя листа таблицы часто меняются, автоматически заменит ее «#Эта строка» болееправая фигурная скобка (});

его для использования

office-guru.ru

Абсолютная ссылка в Excel — описание, примеры.

.Владимирщелкните ссылку на одна из составляющих нужны именно относительныеДВССЫЛ (INDIRECT)С мы выполняли копирование один набор данных в формулах. книги – с восклицательным знаком ссылок на ячейки укороченной формой (со короткими указателями @знак доллара ($); пользовательское имя.В этом примере мы

Формулы в «Эксель»

Северный ячейку, которую нужно изменяется, а вторая ссылки. Например, нам, которая формирует ссылкуникогда не превратится формулы по вертикали, – месячная надбавкаТеперь представим, что мыКнига1 (!). Например, если для структурированные ссылки знаком @). Две в таблицах, содержащихкрышка (^);Указателя столбца: используем имя260 изменить. фиксированная. Например, у

Ссылка относительная

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

ОтделПродаж10 %Для перемещения между сочетаниями смешанной ссылки $D7

	* же таблице рассчитать*	* текстовой строки. *
D	* в любом случае*	* для всех сотрудников:*
основе данных по	Лист1	ячейку
если используется имя	E5 (если текущая строка —	данных. Но если
звездочка (*);	[ОбъемПродаж]	.
Сергей	используйте клавиши	строчка изменяется, а
удельный вес стоимости	Если ввести в ячейку	,
остался бы неизменным.	F	окладу заработную плату

, то ссылка наA1 таблицы в формуле 5) в таблице толькознак «плюс» (+);иДля имен таблиц используютсяЮжный+T. столбец фиксированный. У каждого наименования товара формулу:E И это неG

каждого сотрудника. Поставим ячейкуна листе для подсчета всеПри работе со структурированными одна строка, Excelзнак равенства (=);[Объемкомиссии следующие правила:660В таблице ниже показано, ссылки D$7, наоборот, от общей суммы.=ДВССЫЛ(«C5»)или говоря уж о2 в ячейку С2А1Лист1 ячейки данных в ссылками рекомендуется обращать не заменяет указательзнак «минус» (-);] — это указателиИспользуйте допустимые символы.15 % что происходит при изменяется столбец, но Это делается путем=INDIRECT(«C5»)F ситуациях, когда действительно

Месяц	* знак равенства и*	будет выглядеть следующим
, ссылка будет выглядеть	таблице, а затем	внимание на перечисленные
«#Эта строка», и	знак «больше» (>);	столбцов, использующие имена
Всегда запускать имени	Мария	копировании формулы в
строчка имеет абсолютное	деления стоимости на	то она всегда будет
), но может смещаться	надо зафиксировать только	Надбавка
введем следующую формулу	образом:	так:

добавьте строки данных, ниже аспекты.

 это может привести

знак «меньше» (

столбцов, они представляют.

с буквы, символ

Восточный

ячейке A1, содержащей

значение.

общую сумму. Например, указывать на ячейку по строкам (т.е. номер строки или3 (основываясь на сведениях,=[Книга1.xlsx]Лист1!А1Лист1!A1 ссылку на ячейкуИспользование автозавершения формул к тому, чтознак деления (/). Если они ссылаются подчеркивания (940 ссылку. Формула копируетсяКак видим, при работе чтобы рассчитать удельный

с адресом может сдвинуться на столбца, а остальноеЯнварь что оклад составляет

Чтобы использовать приведенную выше.

Ссылка абсолютная

изменяться автоматически. Может оказаться, что автозавершение при добавлении строкИспользуйте escape-символы для некоторых данных в столбце,_15 % в ячейку на

с формулами в вес картофеля, мыC5$C6 оставить доступным для370 40 % от конструкцию, необходимо, чтобыОбратите внимание, если вПереименование таблицы или столбца. формул очень полезным

вычисления будут возвращать специальных знаков в без строки заголовка) или обратной косойАлексей

	* две строки ниже*	* программе Microsoft Excel*
* его стоимость (D2)*	вне зависимости от	,
изменения.	4	зарплаты): =B2*100/40. Здесь
рабочая книга Excel,

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

 или итоговые значения

черты (). Используйте

Западный и на два для выполнения различных делим на общую любых дальнейших действий$C7Интересно будет узнать, чтоСоответственно, надо изменить и В2 – это на которую мы пробелы, то его таблицу, в приложении ссылок и для избежать таких проблем Перед некоторыми знаками, имеющими для любого столбца. буквы, числа, периоды410 столбца правее, т. е. задач приходится работать сумму (D7). Получаем пользователя, вставки илии т.д.). Аналогично, абсолютная ссылка в ячейку с расчетом

оклад первого сотрудника ссылаемся, была открыта.

 необходимо заключить в

Excel автоматически изменится

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

* Всегда заключайте указатели*	* и знаков для*	12 %
C3.	как с относительными,	следующую формулу:	удаления строк и
C	Excel может быть	зарплаты С2, теперь	в таблице. Нажмем
Автор: Антон Андронов	одинарные кавычки (‘	название этой таблицы	Дополнительные сведения читайте
в таблицу несколько	ставить одинарную кавычку	в квадратные скобки,	остальных имя подчеркивания.
Юлия	Ссылка на текущую (описание):	так и с	«=D2/D7»
т.д. Единственная небольшая	$5	задана не только	она будет содержать

Enter и подведемВ любом, даже базовом ‘). Например, если или заголовок столбца,

Ссылка смешанная

в статье Использование строк, прежде чем (‘), которая служит как показано. Невозможно использовать «C»;СеверныйНовая ссылка абсолютными ссылками. В. сложность состоит в- не будет самостоятельным указанием знака следующую формулу: указатель мыши к пакете «Майкрософт Офис» вы хотите создать

используемые во всех автозавершения формул. использовать формулы со escape-символом. Пример:Указатель элемента: «c», «R» или800$A$1 (абсолютный столбец и некоторых случаях используютсяВ случае, если мы том, что если смещаться по строкам, доллара перед номеромЗарплата правому нижнему краю содержится мощный табличный ссылку на ячейку структурированных ссылках книги.Целесообразность создания структурированных ссылок структурированными ссылками.=ОтделПродажСводкаФГ[‘#Элементов] «r» в поле

Интересный факт

15 % абсолютная строка) также смешанные ссылки. попытаемся скопировать формулу целевая ячейка пустая, но может «гулять» строки и/или столбца.=(B2+G3)*100/40 ячейки С2, дожидаясь, редактор Excel («Эксель»). A1, которая находитсяПеремещение, копирование и заполнение в два разаКогда вы создаете вычисляемыйПри наличии таких специальных[#Totals] имя, так какВадим$A$1 (абсолютная ссылка) Поэтому, пользователь даже в другие строки то по столбцам. Такие «Эксель» позволяет путемКогда мы нажмем Enter, пока указатель не Список доступных в на листе с структурированных ссылок. выбранными параметрами столбец, для формулы

знаков escape-символ (‘)

fb.ru

Типы ссылок на ячейки в формулах Excel

и уже являетесь назначенЮжныйA$1 (относительный столбец и среднего уровня должен тем же способом,ДВССЫЛ ссылки называют нажатия клавиши F4 то увидим, что примет форму тонкого нем функций поистине названием Все структурированные ссылки не По умолчанию при создании часто используется структурированная в формуле необходим:[#Data]

Относительные ссылки

в качестве сочетания900 абсолютная строка) четко понимать разницу что и предыдущийвыводит 0, чтосмешанными: после указания адреса в данной ячейке черного крестика. Удерживая впечатляет: начиная отБюджет июля изменяются при копировании формулы, щелкнув ячейку ссылка. Она можетлевая квадратная скобка ([);— это указатели клавиш для выбора15 %C$1 (смешанная ссылка) между ними, и раз, то получим не всегда удобно.Ну, а если к ячейки выбрать вид зарплата правильно пересчиталась. нажатой левую клавишу возможности сортировки и. Ссылка будет выглядеть или перемещении формулы,

Смешанные ссылки

в пределах таблицы быть неопределенной илиправая квадратная скобка (]); специальных элементов, которые столбца или строкиОбразцов данных в таблице$A1 (абсолютный столбец и уметь пользоваться этими совершенно неудовлетворяющий нас Однако, это можно ссылке дописать оба ссылок – при А вот когда мыши, протянем формулу фильтрации данных и следующим образом: использующей структурированной ссылки. два раза выбирает полностью определенной. Например,решетка (#); ссылаются на определенные для активной ячейки выше, включая заголовки относительная строка) инструментами. результат. Как видим, легко обойти, используя доллара сразу ( первом нажатии ссылка мы протянем формулу вниз до конца заканчивая возможностью построения‘Бюджет июля’!А1Примечание: диапазон ячеек и чтобы создать вычисляемыйодинарная кавычка (‘). части таблицы, такие при их вводе

Абсолютные ссылки

столбцов, скопируйте и$A3 (смешанная ссылка)Автор: Максим Тютюшев уже во второй чуть более сложную$C$5 с относительной изменится на всех сотрудников, таблицы, то есть на их основе. Копирование структурированных ссылок и автоматически вводит структурированной

столбец «ОбъемКомиссии», которыйИспользуйте пробелы для повышения как строка итогов. в поле вставьте ее вA1 (относительный столбец иПримечание: строке таблицы формула конструкцию с проверкой) — она превратится на абсолютную, при то у них до ячейки С7 сводных диаграмм. ИВ следующем примере мы выполнив заливки структурированной ссылки вместо диапазон вычисляет объем комиссии удобочитаемости структурированных ссылок.Указатель таблицы.имя ячейку A1 на относительная строка)

Мы стараемся как имеет вид через функцию

в втором – на зарплата не пересчитается, (можно заменить данное практически невозможно представить будем ссылаться с ссылки не то ячеек в формуле. в рублях, вы С помощью пробелов можно или новом листе Excel.C3 (относительная ссылка) можно оперативнее обеспечивать«=D3/D8»ЕПУСТОабсолютную смешанную с фиксированным ведь используемая относительная действие двойным кликом ни один хранимый одного листа Excel же самое. При Такое поведение псевдовыбора можете использовать следующие повысить удобочитаемость структурированной[[#Итого],[ОбъемПродаж]]ПерейтиЧтобы создать таблицу, выделитеПримечание: вас актуальными справочными, то есть сдвинулась:и не будет номером строки, при ссылка попыталась взять мыши по правому в редакторе набор на значение, которое копировании, структурированные ссылки его значительно упрощает формулы: ссылки. Пример:и [[#Данные],[ОбъемКомиссии]] — это

Действительно абсолютные ссылки

. любую ячейку вМы стараемся как материалами на вашем не только ссылка=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))

меняться никак при третьем – на

 значения из G4..G8,

 нижнему краю ячейки).

данных без вычисляемых относится к другому не изменяются, во ввод структурированной ссылки.Тип структурированной ссылки=ОтделПродаж[ [Продавец]:[Регион] ] указатели таблицы, которыеНе используйте ссылки на диапазоне данных и можно оперативнее обеспечивать языке. Эта страница на ячейку с=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»)) любом копировании, т.е. смешанную с фиксированным где абсолютно ничего Столбец автоматически заполнится посредством формул строк рабочему листу. Это время при вводе Вы можете включитьПримерили

 представляют внешние части

 ячейки

planetaexcel.ru

нажмите

Источник

Как использовать функцию ДВССЫЛ – примеры формул

В этой статье объясняется синтаксис функции ДВССЫЛ, основные способы ее использования и приводится ряд примеров формул, демонстрирующих использование ДВССЫЛ в Excel.

В Microsoft Excel существует множество функций, некоторые из которых просты для понимания, другие требуют длительного обучения. При этом первые используются чаще, чем вторые. И тем не менее, функция Excel ДВССЫЛ (INDIRECT на английском) является единственной в своем роде. Эта функция Excel не выполняет никаких вычислений, не оценивает никаких условий не ищет значения.

Итак, что такое функция ДВССЫЛ (INDIRECT) в Excel и для чего ее можно использовать? Это очень хороший вопрос, и, надеюсь, вы получите исчерпывающий ответ через несколько минут, когда закончите чтение.

Функция ДВССЫЛ в Excel — синтаксис и основные способы использования

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

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

Функция ДВССЫЛ в Excel возвращает ссылку на ячейку, используя текстовую строку. Она имеет два аргумента, первый является обязательным, а второй – нет:

ДВССЫЛ(ссылка_на_ячейку; [a1])

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

a1 — логическое значение, указывающее, какой тип ссылки содержится в первом аргументе:

Если значение ИСТИНА или опущено, то используется ссылка на ячейку в стиле A1.
Если ЛОЖЬ, то возвращается ссылка в виде R1C1.

Таким образом, ДВССЫЛ возвращает либо ссылку на ячейку, либо ссылку на диапазон.

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

Как работает функция ДВССЫЛ

Чтобы получить представление о работе функции, давайте создадим простую формулу, которая демонстрирует, как можно применить ДВССЫЛ в Excel.

Предположим, у вас есть число 5 в ячейке A1 и текст «A1» в ячейке C1. Теперь поместите формулу =ДВССЫЛ(C1) в любую другую ячейку и посмотрите, что произойдет:

Функция ДВССЫЛ обращается к значению в ячейке C1. Там в виде текстовой строки записан адрес «A1».
Функция ДВССЫЛ направляется по этому адресу в ячейку A1, откуда извлекает записанное в ней значение, то есть число 555.

Итак, в этом примере функция ДВССЫЛ преобразует текстовую строку в ссылку на ячейку.

Аналогичным образом можно получить ссылку на диапазон. Для этого просто нужно функции ДВССЫЛ указать два адреса – начальный и конечный. Вы видите это на скриншоте ниже.

Формула ДВССЫЛ(C1&»:»&C2) извлекает адреса из указанных ячеек и превращается в =ДВССЫЛ(«A1:A5»).

В итоге мы получаем ссылку =A1:A5

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

Как использовать ДВССЫЛ в Excel — примеры формул

Как показано в приведенном выше примере, вы можете использовать функцию ДВССЫЛ, чтобы записать адрес ячейки как обычную текстовую строку и получить в результате значение этой ячейки. Однако этот простой пример — не более чем намек на возможности ДВССЫЛ.

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

Создание косвенных ссылок из значений ячеек

Как вы помните, функция ДВССЫЛ в Excel позволяет использовать стили ссылок A1 и R1C1. Обычно вы не можете использовать оба стиля на одном листе одновременно. Вы можете переключаться между двумя типами ссылок только с помощью опции «Файл» > «Параметры» > «Формулы» > R1C1 . По этой причине пользователи Excel редко рассматривают использование R1C1 в качестве альтернативного подхода к созданию ссылок.

В формуле ДВССЫЛ вы можете использовать любой тип ссылки на одном и том же листе, если хотите. Прежде чем мы двинемся дальше, давайте более подробно рассмотрим разницу между стилями ссылок A1 и R1C1.

Стиль A1 — это обычный и привычный всем нам тип адресации в Excel, который указывает сначала столбец, за которым следует номер строки. Например, B2 обозначает ячейку на пересечении столбца B и строки 2.

Стиль R1C1 является обозначает координаты ячейки наоборот – за строками следуют столбцы, и к этому нужно привыкнуть:) Например, R5C1 относится к ячейке A5, которая находится в строке 5, столбце 1 на листе. Если после буквы не следует какая-либо цифра, значит, вы имеете в виду ту же строку или столбец, в которых записана сама формула.

А теперь давайте сравним на простом примере, как функция ДВССЫЛ обрабатывает адреса вида A1 и R1C1:

Как вы видите на скриншоте выше, две разные формулы возвращают один и тот же результат. Вы уже поняли, почему?

Формула в ячейке D1: =ДВССЫЛ(C1)

Это самый простой вариант. Формула обращается к ячейке C1, извлекает ее значение — текстовую строку «A2» , преобразует ее в ссылку на ячейку, переходит к ячейке A2 и возвращает ее значение, равное 456.

Формула в ячейке D3: =ДВССЫЛ(C3;ЛОЖЬ)

ЛОЖЬ во втором аргументе указывает, что указанное значение (C3) следует рассматривать как ссылку на ячейку в формате R1C1, т. е. сначала идет номер строки, за которым следует номер столбца. Таким образом, наша формула ДВССЫЛ интерпретирует значение в ячейке C3 (R2C1) как ссылку на ячейку на пересечении строки 2 и столбца 1, которая как раз и является ячейкой A2.

Создание ссылок из значений ячеек и текста

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

В следующем примере формула =ДВССЫЛ(«А»&C1) возвращает значение из ячейки А1 на основе следующей логической цепочки:

Функция ДВССЫЛ объединяет элементы в первом аргументе ссылка_на_ячейку — текст «А» и значение из ячейки C1. Значение в C1 – это число 1, что в результате формирует адрес А1. Формула переходит к ячейке А1 и возвращает ее значение – 555.

Использование функции ДВССЫЛ с именованными диапазонами

Помимо создания ссылок на ячейки из текстовых строк, вы можете заставить функцию Excel ДВССЫЛ создавать ссылки на именованные диапазоны.

Предположим, у вас есть следующие именованные диапазоны на вашем листе:

Яблоки – С2:E2
Лимоны — C3: E3
Апельсины – C4:E4 и так далее по каждому товару.

Чтобы создать динамическую ссылку Excel на любой из указанных выше диапазонов с цифрами продаж, просто запишите его имя, скажем, в H1, и обратитесь к этой ячейке при помощи формулы =ДВССЫЛ(H1).

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

=СУММ(ДВССЫЛ (H1))
=СРЗНАЧ(ДВССЫЛ (H1))
=МАКС(ДВССЫЛ (H1))
=МИН(ДВССЫЛ (H1))

Теперь, когда вы получили общее представление о том, как работает функция ДВССЫЛ в Excel, мы можем поэкспериментировать с более серьёзными формулами.

ДВССЫЛ для ссылки на другой рабочий лист

Полезность функции Excel ДВССЫЛ не ограничивается созданием «динамических» ссылок на ячейки. Вы также можете использовать ее для формирования ссылки на другие листы.

Предположим, у вас есть важные данные на листе 1, и вы хотите получить эти данные на листе 2. На скриншоте ниже показано, как можно справиться с этой задачей.

Нам поможет формула:

=ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

Давайте разбираться, как работает эта формула.

Как вы знаете, обычным способом сослаться на другой лист в Excel является указание имени этого листа, за которым следуют восклицательный знак и ссылка на ячейку или диапазон, например Лист1!A1:С10. Так как имя листа часто содержит пробелы, вам лучше заключить его (имя, а не пробел в одинарные кавычки, чтобы предотвратить возможную ошибку, например,

‘Лист 1!’$A$1 или для диапазона – ‘Лист 1!’$A$1:$С$10 .

Наша задача – сформировать нужный текст и передать его функции ДВССЫЛ. Все, что вам нужно сделать, это:

записать имя листа в одну ячейку,
букву столбца – в другую,
номер строки – в третью,
объединить всё это в одну текстовую строку,
передать этот адрес функции ДВССЫЛ.

Помните, что в текстовой строке вы должны заключать каждый элемент, кроме номера строки, в двойные кавычки и затем связать все элементы в единое целое с помощью оператора объединения (&).

С учетом вышеизложенного получаем шаблон ДВССЫЛ для создания ссылки на другой лист:

ДВССЫЛ («‘» & имялиста & «‘!» & имя столбца нужной ячейки & номер строки нужной ячейки )

Возвращаясь к нашему примеру, вы помещаете имя листа в ячейку A2 и вводите адреса столбца и строки в B2 и С2, как показано на скриншоте выше. В результате вы получите следующую формулу:

ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

Кроме того, обратите внимание, что если вы копируете формулу в несколько ячеек, вам необходимо зафиксировать ссылку на имя листа, используя абсолютные ссылки на ячейки, например $A$2.

Замечание.

Если какая-либо из ячеек, содержащих имя листа и адреса ячеек (A2, B2 и c2 в приведенной выше формуле), будет пуста, ваша формула вернет ошибку. Чтобы предотвратить это, вы можете обернуть функцию ДВССЫЛ в функцию ЕСЛИ :

ЕСЛИ(ИЛИ(A2=»»;B2=»»;C2-“”); «»; ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

Чтобы формула ДВССЫЛ, ссылающаяся на другой лист, работала правильно, указанный лист должен быть открыт в Экселе, иначе формула вернет ошибку #ССЫЛКА. Чтобы не видеть сообщение об ошибке, которое может портить вид вашей таблицы, вы можете использовать функцию ЕСЛИОШИБКА, которая будет отображать пустую строку при любой возникшей ошибке:

ЕСЛИОШИБКА(ДВССЫЛ(«‘»&A2&»‘!»&B2&C2); «»)

Формула ДВССЫЛ для ссылки на другую книгу Excel

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

Чтобы упростить задачу, давайте начнем с создания ссылки на другую книгу обычным способом (апострофы добавляются, если имена вашей книги и/или листа содержат пробелы):
‘[Имя_книги.xlsx]Имя_листа’!Арес_ячейки

Но, чтобы формула была универсальной, лучше апострофы добавлять всегда – лишними не будут .

Предполагая, что название книги находится в ячейке A2, имя листа — в B2, а адрес ячейки — в C2 и D2, мы получаем следующую формулу:

=ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2)

Поскольку вы не хотите, чтобы ячейки, содержащие имена книг и листов, изменялись при копировании формулы в другие ячейки, вы можете зафиксировать их, используя абсолютные ссылки на ячейки – $A$2 и $B$2 соответственно.

Если адреса ячеек заменить их значениями, то мы получим такой промежуточный результат:

=ДВССЫЛ(«‘[INDIRECT.xlsx]Продажи’!D3»)

Ну а итоговый результат вы видите на скриншоте ниже.

Hbc6

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

=ДВССЫЛ(«‘[» & Название книги & «]» & Имя листа & «‘!» & Адрес ячейки )

Примечание. Рабочая книга, на которую ссылается ваша формула, всегда должна быть открыта, иначе функция ДВССЫЛ выдаст ошибку #ССЫЛКА. Как обычно, функция ЕСЛИОШИБКА может помочь вам избежать этого:

=ЕСЛИОШИБКА(ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2); «»)

Использование функции Excel ДВССЫЛ чтобы зафиксировать ссылку на ячейку

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

Чтобы проиллюстрировать разницу, сделайте следующее:

Введите любое значение в любую ячейку, например, число 555 в ячейку A1.
Обратитесь к A1 из двух других ячеек тремя различными способами: =A1, =ДВССЫЛ(«A1») и ДВССЫЛ(С1), где в С1 записан адрес «А1».
Вставьте новую строку над строкой 1.

Видите, что происходит? Ячейка с логическим оператором =А1 по-прежнему возвращает 555, потому что ее формула была автоматически изменена на =A2 после вставки строки. Ячейки с формулой ДВССЫЛ теперь возвращают нули, потому что формулы в них не изменились при вставке новой строки и они по-прежнему ссылаются на ячейку A1, которая в настоящее время пуста:

После этой демонстрации у вас может сложиться впечатление, что функция ДВССЫЛ больше мешает, чем помогает. Ладно, попробуем по-другому.

Предположим, вы хотите просуммировать значения в ячейках A2:A5, и вы можете легко сделать это с помощью функции СУММ:

=СУММ(A2:A5)

Однако вы хотите, чтобы формула оставалась неизменной, независимо от того, сколько строк было удалено или вставлено. Самое очевидное решение — использование абсолютных ссылок — не поможет. Чтобы убедиться, введите формулу =СУММ($A$2:$A$5) в какую-нибудь ячейку, вставьте новую строку, скажем, в строку 3, и увидите формулу, преобразованную в =СУММ($A$2:$A$6).

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

Решение состоит в использовании функции ДВССЫЛ, например:

=СУММ(ДВССЫЛ(«A2:A5»))

Поскольку Excel воспринимает «A1: A5» как простую текстовую строку, а не как ссылку на диапазон, он не будет вносить никаких изменений при вставке или удалении строки (строк), а также при их сортировке.

Использование ДВССЫЛ с другими функциями Excel

Помимо СУММ, ДВССЫЛ часто используется с другими функциями Excel, такими как СТРОКА, СТОЛБEЦ, АДРЕС, ВПР, СУММЕСЛИ и т. д.

Пример 1. Функции ДВССЫЛ и СТРОКА

Довольно часто функция СТРОКА используется в Excel для возврата массива значений. Например, вы можете использовать следующую формулу массива (помните, что для этого нужно нажать Ctrl + Shift + Enter), чтобы вернуть среднее значение трех наименьших чисел в диапазоне B2:B13

{=СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(1:3)))}

Однако, если вы вставите новую строку в свой рабочий лист где-нибудь между строками 1 и 3, диапазон в функции СТРОКА изменится на СТРОКА(1:4), и формула вернет среднее значение четырёх наименьших чисел вместо трёх.

Чтобы этого не произошло, вставьте ДВССЫЛ в функцию СТРОКА, и ваша формула массива всегда будет оставаться правильной, независимо от того, сколько строк будет вставлено или удалено:

={СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

Аналогично, если нам нужно найти сумму трёх наибольших значений, можно использовать ДВССЫЛ вместе с функцией СУММПРОИЗВ.

Вот пример:

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

А чтобы указать переменное количество значений, которое мы хотим сосчитать, можно это число вынести в отдельную ячейку. К примеру, в С1 запишем цифру 3. Тогда формулу можно изменить таким образом:

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:»&C1))))}

Согласитесь, что получается достаточно гибкий расчёт.

Пример 2. Функции ДВССЫЛ и АДРЕС

Вы можете использовать Excel ДВССЫЛ вместе с функцией АДРЕС, чтобы получить значение в определенной ячейке на лету.

Как вы помните, функция АДРЕС используется в Excel для получения адреса ячейки по номерам строк и столбцов. Например, формула =АДРЕС(1;3) возвращает текстовую строку «$C$1», поскольку C1 — это ячейка на пересечении 1-й строки и 3-го столбца.

Чтобы создать ссылку на ячейку, вы просто встраиваете функцию АДРЕС в формулу ДВССЫЛ, например:

=ДВССЫЛ(АДРЕС(1;3))

Конечно, эта несложная формула лишь демонстрирует технику. Более сложные примеры использования функций ДВССЫЛ И АДРЕС в Excel см. в статье Как преобразовать строки в столбцы в Excel .

И вот еще несколько примеров формул в которых используется функция ДВССЫЛ, и которые могут оказаться полезными:

ВПР и ДВССЫЛ — как динамически извлекать данные из разных таблиц (см. пример 2).
Excel ДВССЫЛ и СЧЁТЕСЛИ — как использовать функцию СЧЁТЕСЛИ в несмежном диапазоне или нескольких выбранных ячейках.

Использование ДВССЫЛ для создания выпадающих списков

Вы можете использовать функцию Excel ДВССЫЛ с инструментом проверки данных для создания каскадных выпадающих списков. Они показывают различные варианты выбора в зависимости от того, какое значение пользователь указал в предыдущем выпадающем списке.

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

В ячейке А1 вы создаете простой выпадающий список с названиями имеющихся именованных диапазонов. Для второго зависимого выпадающего списка в ячейке В2 вы используете простую формулу =ДВССЫЛ(A1), где A1 — это ячейка, в которой выбрано имя нужного именованного диапазона.

К примеру, выбрав в первом списке второй квартал, во втором списке мы видим месяцы этого квартала.

Рис9

Чтобы сделать более сложные трехуровневые меню или раскрывающиеся списки с многоуровневыми записями, вам понадобится немного более сложная формула ДВССЫЛ в сочетании с вложенной функцией ПОДСТАВИТЬ.

Подробное пошаговое руководство по использованию ДВССЫЛ с проверкой данных Excel смотрите в этом руководстве: Как создать зависимый раскрывающийся список в Excel.

Функция ДВССЫЛ Excel — возможные ошибки и проблемы

Как показано в приведенных выше примерах, функция ДВССЫЛ весьма полезна при работе со ссылками на ячейки и диапазоны. Однако не все пользователи Excel охотно принимают этот подход, в основном потому, что постоянное использование ДВССЫЛ приводит к отсутствию прозрачности формул Excel и несколько затрудняет их понимание. Функцию ДВССЫЛ сложно просмотреть и проанализировать ее работу, поскольку ячейка, на которую она ссылается, не является конечным местоположением значения, используемого в формуле. Это действительно довольно запутанно, особенно при работе с большими сложными формулами.

В дополнение к сказанному выше, как и любая другая функция Excel, ДВССЫЛ может вызвать ошибку, если вы неправильно используете аргументы функции. Вот список наиболее типичных ошибок и проблем:

Ошибка #ССЫЛКА!

Чаще всего функция ДВССЫЛ возвращает ошибку #ССЫЛКА! в следующих случаях:

Аргумент ссылка_на_ячейку не является допустимой ссылкой Excel. Если вы пытаетесь передать функции текст, который не может обозначать ссылку на ячейку (например, «A1B0»), то формула приведет к ошибке #ССЫЛКА!. Во избежание возможных проблем проверьте аргументы функции ДВССЫЛ .
Превышен предел размера диапазона. Если аргумент ссылка_на_ячейку вашей формулы ДВССЫЛ ссылается на диапазон ячеек за пределами строки 1 048 576 или столбца 16 384, вы также получите ошибку #ССЫЛКА в Excel 2007 и новее. Более ранние версии Excel игнорируют превышение этого лимита и действительно возвращают некоторое значение, хотя часто не то, что вы ожидаете.
Используемый в формуле лист или рабочая книга закрыты.Если ваша формула с ДВССЫЛ адресуется на другую книгу или лист Excel, то эта другая книга или электронная таблица должны быть открыты, иначе ДВССЫЛ возвращает ошибку #ССЫЛКА! . Впрочем, это требование характерно для всех формул, которые ссылаются на другие рабочие книги Excel.

Ошибка #ИМЯ?

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

Ошибка из-за несовпадения региональных настроек.

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

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

В стандартной конфигурации Windows для Северной Америки и некоторых других стран разделителем списка по умолчанию является запятая.

В результате при копировании формулы между двумя разными языковыми стандартами Excel вы можете получить сообщение об ошибке « Мы обнаружили проблему с этой формулой… », поскольку разделитель списка, используемый в формуле, отличается от того, что установлен на вашем компьютере. Если вы столкнулись с этой ошибкой при копировании какой-либо НЕПРЯМОЙ формулы из этого руководства в Excel, просто замените все запятые (,) точками с запятой (;) (либо наоборот). В обычных формулах Excel эта проблема, естественно, не возникнет. Там Excel сам поменяет разделители исходя из ваших текущих региональных настроек.

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

Надеемся, что это руководство пролило свет для вас на использование ДВССЫЛ в Excel. Теперь, когда вы знаете ее сильные стороны и ограничения, пришло время попробовать и посмотреть, как функция ДВССЫЛ может упростить ваши задачи в Excel. Спасибо за чтение!

Вот еще несколько статей по той же теме:

Как удалить сразу несколько гиперссылок — В этой короткой статье я покажу вам, как можно быстро удалить сразу все нежелательные гиперссылки с рабочего листа Excel и предотвратить их появление в будущем. Решение работает во всех версиях Excel,…

Как использовать функцию ГИПЕРССЫЛКА — В статье объясняются основы функции ГИПЕРССЫЛКА в Excel и приводятся несколько советов и примеров формул для ее наиболее эффективного использования. Существует множество способов создать гиперссылку в Excel. Чтобы сделать ссылку на…

Гиперссылка в Excel: как сделать, изменить, удалить — В статье разъясняется, как сделать гиперссылку в Excel, используя 3 разных метода. Вы узнаете, как вставлять, изменять и удалять гиперссылки на рабочих листах, а также исправлять неработающие ссылки. Гиперссылки широко используются…

Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…

Источник

Очищайте ячейки от лишних пробелов, переносов строк и непечатаемых символов

Сколько времени у вас занимает очистка данных перед тем, как можно будет приступить к анализу? Лишние пробелы и непечатаемые символы почти неизбежны при импорте данных в Excel из веб-страниц или сторонних программ. Поиск и удаление вручную или применение функций Excel не продуктивно, если вы работаете с большими таблицами.

Инструмент «Очистить ячейки» поможет очистить массив данных в секунды:

Как Проверить Наличие Пробелов в Excel

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Как видите, это довольно простой способ, как можно убрать пробелы в числах в Excel, но стоит учитывать, что он работает лишь в случаях форматирования текста. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как “John Doe “. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.

В любом из вариантов открывается окно «Найти и заменить» во вкладке «Заменить». В поле «Найти» устанавливаем курсор и дважды кликаем по кнопке «Пробел» на клавиатуре. В поле «Заменить на» вставляем один пробел. Затем жмем по кнопке «Заменить все».

Способ 2: удаление пробелов между разделителями

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

Выделите часть таблицы с числовыми данными, в которых находятся лишние пробелы.
На ленте инструментов опять же кликните по кнопке «Найти и выделить» и выберите опцию «Заменить». Кстати, это действие можно произвести путем использования горячих клавиш Ctrl+H.
Появится уже знакомое окно, где нужно ввести две переменные.
В поле «Найти» введите одинарный пробел.
Поле «Заменить на» не трогайте, в нем ничего не должно находиться.
Нажмите кнопку «Заменить все».

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Формула обязательно должна быть с относительными ссылками без долларов , если мы хотим, чтобы она распространилась на все последующие строки. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

И — это означает, что мы проверяем два условия и они должны обе выполняться. Если бы нужно было, чтобы выполнялось одно из условий (либо результат больше 75 либо сотрудник — льготник), то нужно было бы использовать функцию ИЛИ, еще проще если условие одно.

Как в Эксель сделать пробел? Ваша онлайн-энциклопедия

: Теперь работает :)
текстовые получаются и выгрузке с программы удалён. При попытке пробелы будут убраны. числовыми значениями пропадут
этом программа вам с текстовыми выражениями,
без проблем удалить быть удалены всеCtrl+Space в поле помощи функции добавьте вспомогательный столбец.
Используем функцию TRIM (СЖПРОБЕЛЫ) (простой текст, числа
заменить все
Nika их нельзя например

Шаг 4: После данного выбора все пустые ячейки в указанном диапазоне будут выделены. Теперь, не нажимая ничего, введите необходимо значение, которое нужно поместить во все пустые ячейки и нажмите Ctrl + Enter. Пустые ячейки заполнятся нужным содержимым.

Способ 3: удаление путем форматирования

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

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Только что мы разобрались, как удалить пробелы в ячейках Excel с текстовыми выражениями, но что, если появилась потребность убрать лишние одинарные пробелы в числовых выражениях, которые зачастую устанавливаются автоматически. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

Являются специальными символами, которые чаще используются в программе Word или при публикации текста в блогах. Полезным (существует также ) может быть при обязательном соединении чисел, слов или дат, например, в предложении «ул. Сидорова» необходимо прикрепить «ул.» к слову «Сидорова». Поставить неразрывный пробел можно клавишами CTRL+SHIFT+ПРОБЕЛ.

В эксель убрать пробелы. Четыре способа, как быстро удалить пробелы в Excel

Выделите часть таблицы с числовыми данными, в которых находятся лишние пробелы.
На ленте инструментов опять же кликните по кнопке «Найти и выделить» и выберите опцию «Заменить». Кстати, это действие можно произвести путем использования горячих клавиш Ctrl+H.
Появится уже знакомое окно, где нужно ввести две переменные.
В поле «Найти» введите одинарный пробел.
Поле «Заменить на» не трогайте, в нем ничего не должно находиться.
Нажмите кнопку «Заменить все».

Как объединить диапазон ячеек?

Щелкните первую ячейку и нажмите shift, щелкнув последнюю ячейку в диапазоне, который вы хотите объединить. Важно: Убедитесь, что данные есть только в одной из ячеек в диапазоне.
Выберите Главная > Объединить и поместить в центре.

Функция СЖПРОБЕЛЫ( ) , английский вариант TRIM(), удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ() используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать лишние пробелы.

Как заменить пробелы в excel между цифрами

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

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

Розовым цветом мы специально подсветили те позиции, которые написаны с лишними пробелами. Поэтому команда ВПР не выдала по ним результат. Чтобы исправить это, добавим к ВПР функцию СЖПРОБЕЛЫ и посмотрим, что получится.

Как Быстро Заполнять Таблицу в Excel – Удаление пробелов | 📂Все о программе Word

Кликните дважды левой кнопкой мыши по ячейке с формулой.
Впишите между аргументами пробелы. Однако делается это нестандартным способом. Пробел должен выступать также в роли аргумента. .
Нажмите Enter для применения изменений.

Представим себе следующий пример. У нас есть таблицам с ФИО, по каждому сотруднику есть результат в процентах и информация о наличии льгот. Нам необходимо выделить с помощью условного форматирования только тех сотрудников, которые имеют результат выше 75 и имеют льготы.

Источник

Удаление лишних пробелов в Microsoft Excel

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

Технология удаления пробелов

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

Способ 1: использование инструмента «Заменить»

С заменой двойных пробелов между словами на одинарные в Экселе отлично справляется инструмент «Заменить».

«Главная»

«Найти и выделить»

«Редактирование»

«Заменить»

Ctrl+H

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

Таким образом, мы избавились от лишних двойных пробелов между словами в документе.

Способ 2: удаление пробелов между разрядами

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

«Найти и выделить»

«Редактирование»

«Главная»

«Заменить»

Снова запускается окно «Найти и заменить» во вкладке «Заменить». Но на этот раз мы будем вносить в поля немного другие значения. В поле «Найти» устанавливаем один пробел, а поле «Заменить на» оставляем вообще незаполненным. Чтобы удостовериться на предмет того, что в этом поле нет пробелов, устанавливаем курсор в него и зажимаем кнопку backspace (в виде стрелки) на клавиатуре. Держим кнопку до тех пор, пока курсор не упрется в левую границу поля. После этого жмем на кнопку «Заменить все».

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

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

Способ 3: удаление разделителей между разрядами путем форматирования

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

Так как пробелы были сделаны с помощью инструментов форматирования, то только при помощи этих же инструментов их можно убрать.

«Формат ячеек…»

Происходит запуск окна форматирования. Переходим во вкладку «Число», в случае если открытие произошло в другом месте. Если разделение было задано при помощи форматирования, то в блоке параметров «Числовые форматы» должен быть установлен вариант «Числовой». В правой части окна располагаются точные настройки данного формата. Около пункта «Разделитель групп рядов ()» вам просто нужно будет снять галочку. Затем, чтобы произведенные изменения вступили в силу, жмите на кнопку «OK».

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

Способ 4: удаление пробелов с помощью функции

Инструмент «Найти и заменить» отлично подходит для удаления лишних пробелов между символами. Но что делать, если их нужно убрать в начале или в конце выражения? В этом случае на помощь придет функция из текстовой группы операторов СЖПРОБЕЛЫ.

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

Синтаксис этого оператора довольно прост и имеет всего один аргумент:

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

«Вставить функцию»

Запускается окно Мастера функций. В категории «Полный алфавитный перечень» или «Текстовые» ищем элемент «СЖПРОБЕЛЫ». Выделяем его и жмем на кнопку «OK».

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

Как видим, содержимое ячейки отобразилось в области, в которой находится функция, но уже без лишних пробелов. Мы выполнили удаление пробелов только для одного элемента диапазона. Чтобы удалить их и в других ячейках, нужно провести аналогичные действия и с другими ячейками. Конечно, можно проводить с каждой ячейкой отдельную операцию, но это может отнять очень много времени, особенно, если диапазон большой. Существует способ значительно ускорить процесс. Устанавливаем курсор в нижний правый угол ячейки, в которой уже содержится формула. Курсор трансформируется в небольшой крестик. Он носит название маркера заполнения. Зажимаем левую кнопку мыши и тянем маркер заполнения параллельно тому диапазону, в котором нужно убрать пробелы.

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

Выделяем диапазон с преобразованными значениями. Жмем на кнопку «Копировать», расположенную на ленте во вкладке «Главная» в группе инструментов «Буфер обмена». Как альтернативный вариант можно после выделения набрать сочетание клавиш Ctrl+C.

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

Как видим, после вышеописанных действий значения с лишними пробелами были заменены идентичными данными без них. То есть, поставленная задача выполнена. Теперь можно удалить транзитную область, которая была использована для трансформации. Выделяем диапазон ячеек, в которых содержится формула СЖПРОБЕЛЫ. Кликаем по нему правой кнопкой мыши. В активировавшемся меню выбираем пункт «Очистить содержимое».

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

Как видим, существует целый ряд способов быстрого удаления лишних пробелов в Экселе. Но все эти варианты реализуются при помощи всего двух инструментов – окна «Найти и заменить» и оператора СЖПРОБЕЛЫ. В отдельном случае можно использовать также форматирование. Не существует универсального способа, который было бы максимально удобно использовать во всех ситуациях. В одном случае будет оптимальным использовать один вариант, а во втором – другой и т.д. Например, с удалением двойного пробела между словами быстрее всего справится инструмент «Найти и заменить», а вот убрать пробелы в начале и в конце ячейки корректно сможет только функция СЖПРОБЕЛЫ. Поэтому о применении конкретного способа пользователь должен принимать решение самостоятельно с учетом ситуации.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Excel. Обнаружение лишних пробелов

Типичная ошибка, которая встречается в электронных таблицах, связана с невидимыми символами пробелов. [1] В ячейке В2 (рис. 1) находится формула, берущая название цвета в ячейке В1 и возвращающая соответствующий код из таблицы D1:E6: =ВПР(B1;D1:E6;2;ЛОЖЬ).

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

В следующем примере формула в ячейке В2 возвращает ошибку, указывая, что значение Красный не было найдено в таблице. Сотни и тысячи пользователей Excel потратили массу времени, чтобы понять, почему подобные операции не работают. Ответ прост: в ячейке D5 написано не «Красный», а «Красный », то есть за словом следует пробел. Для Excel это две разные последовательности символов.

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

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

один ведущий пробел и более;
один завершающий пробел и более;
два и более последовательных пробела в самом тексте.

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

Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
Выполните команду Главная –>Условноеформатирование –>Создатьправило, чтобы открыть диалоговое окно Созданиеправилаформатирования (рис. 3).
В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
Нажмите Ok два раза.

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

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

Функция СЖПРОБЕЛЫ действует так, что формула, описанная в шаге 4, также применяет условное форматирование ко всем числовым ячейкам. Если в вашем диапазоне встречаются числа, используйте на шаге 4 формулу: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(D2));D2<>СЖПРОБЕЛЫ(D2)). Кроме того, нужно иметь в виду, что функция СЖПРОБЕЛЫ не удаляет (и не воспринимает при условном форматировании) знак неразрывного пробела.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 183–185.

Как получить слово после последнего пробела

Получить слово до первого пробела достаточно просто:
=ПСТР( A1 ;1;НАЙТИ(» «; A1 )-1)
Но куда чаще сложности возникают с получением слова(символа), находящегося на определенной позиции между пробелом. Я беру в качестве примера пробел, но на самом деле это может быть абсолютно любой символ. Например, для получения второго слова(т.е. слова между первым пробелом и третьим), можно составить такую формулу:
=ПСТР( A1 ;НАЙТИ(» «; A1 )+1;НАЙТИ(» «; A1 ;НАЙТИ(» «; A1 )+1)-НАЙТИ(» «; A1 )-1)
На мой взгляд, выглядит несколько закручено, хотя все предельно просто:

НАЙТИ(» «; A1 )+1 — ищем позицию первого пробела;
НАЙТИ(» «;A1;НАЙТИ(» «; A1 )+1) — ищем позицию второго пробела и затем из этой позиции вычитаем позицию первого пробела( -НАЙТИ(» «; A1 ) ).

Но есть проблема — если второго пробела нет, то формула выдаст ошибку #ЗНАЧ!. Тогда придется еще и проверку на ошибку делать, что явно не добавит формуле элегантности. Поэтому я предпочитаю использовать такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));1;999*2);999);» «;»»)
На первый взгляд куда кошмарнее, чем первая. Но у неё есть ряд преимуществ:
она не нуждается в проверке на отсутствие пробелов;
изменением одного числа можно получить не второе, а 3-е, 4-е и т.д. слово.
Разберем самое главное: чтобы получить первое слово от начала строки, нужно в блоке 999*2 заменить 2 на 1:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));1;999*1);999);» «;»»)
Чтобы получить 5-е — на 5:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));1;999*5);999);» «;»»)
И ТО, К ЧЕМУ ШЛИ — СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОБЕЛА
Если вдруг пробелов будет меньше, чем указанное число — то мы получим слово после последнего пробела (т.е. первое слово с конца строки). Это значит, что если указать *999 — в большинстве случаев получим как раз последнее слово.
Как это работает: при помощи функции ПОДСТАВИТЬ мы заменяем все пробелы в тексте на 999 пробелов(число может быть меньше 999, но не должно быть меньше длины исходной строки). Далее при помощи функции ПСТР мы выдергиваем первые 999 символов, помноженные на число, обозначающее необходимое нам слово(999*1 — первое). По сути только то количество слов, которое указано(в данном случае одно — 999*1). Затем функция ПРАВСИМВ возвращает нам только последнее слово — т.е. нужное нам. А далее та же ПОДСТАВИТЬ убирает лишние теперь пробелы, заменяя их все на пустую строку — «» .
Вроде бы достигли того, что нам нужно было. Но вдруг необходимо получить второе слово с конца строки? Как тогда быть? Считать пробелы? А если у нас их штук 50 хотя бы? Можно использовать некую модификацию приведенной выше формулы, но которая как раз возвращает слово с конца строки:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));999*1);1;999);» «;»»)
Принцип тот же: если в блоке 999*1 заменить 1 на 5, то получим 5-е слово с конца строки.
Если необходимо выдергивать слова по пробелам, то лучше дополнить еще одной функцией — СЖПРОБЕЛЫ:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ(СЖПРОБЕЛЫ( A1 );» «;ПОВТОР(» «;999));1;999*1);999);» «;»»)
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(» «&ПОДСТАВИТЬ(СЖПРОБЕЛЫ( A1 );» «;ПОВТОР(» «;999));999*1);1;999);» «;»»)

Так же можно «вытянуть» определенное количество слов:
=СЖПРОБЕЛЫ(ПСТР(ПРАВСИМВ(» «&ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;999));999*3);1;999*2))
3 — третье слово с конца строки.
2 — количество слов.

Остается еще добавить, что вместо пробелов могут быть и другие символы. Например, очень часто встречается ситуация, когда надо из текста получить не одно слово в конкретной позиции, а конкретную строку из текста, разнесенного в одной ячейке на строки:

Тогда для получения второй строки( ТЦ Таганка и ТЦ Опус ) можно применить такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(СИМВОЛ(10)&ПОДСТАВИТЬ( C2 ;СИМВОЛ(10);ПОВТОР(СИМВОЛ(10);999));1;999*2);999);СИМВОЛ(10);»»)
СИМВОЛ(10) здесь означает перенос строки. Обычно эти переносы делаются с клавиатуры. Входим в режим редактирования ячейки, ставим курсор в нужное место строки и нажимаем Alt+Enter.
я для получения месяцев( Август 2015 г. и Сентябрь 2015 г. ) — такую:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(«/»&ПОДСТАВИТЬ( C2 ;»/»;ПОВТОР(«/»;999));999*1);1;999);»/»;»»)
Если лень прописывать внутри формулы один символ несколько раз, его можно записать в ячейку(скажем, G1 ) и в формуле указать ссылку на эту ячейку:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ( G1 &ПОДСТАВИТЬ( C2 ; G1 ;ПОВТОР( G1 ;999));999*1);1;999); G1 ;»»)
Теперь для изменения символа надо будет изменить его один раз в ячейке и формула «вытащит» нужное слово/строку, опираясь именно на этот символ.

Tips_All_WordFromEndString.xls (29,5 KiB, 2 572 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Удаление ненужных пробелов в цифрах в Excel

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

Пробелы в числах

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

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

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

Найти и заменить

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

Выделить ячейку, столбец, строчку или некоторое их количество (где следует убрать «пробелы»).
После этого нужно зажать последовательность клавиш на клавиатуре Ctrl+H. Данное сочетание клавиш вызовет специальное окно инструмента, которое называется «Найти и заменить» или «Find & Replace» (в англоязычной версии).
В графу «Найти» следует ввести двойной пробел, а в нижнюю «Заменить на» только один. Также в строке «Найти» можно установить один пробел, а «Заменить на» оставить пустым, если требуется избавиться от промежутков вообще.
После этого остается нажать «Заменить все».

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

Функция СЖПРОБЕЛЫ

Для удаления пробелов в ячейках Excel часто используют специальную функцию, которая называется СЖПРОБЕЛЫ или TRIM (в англоязычной версии). Она несколько сложнее в исполнении, чем предыдущий метод, но гораздо действеннее и способна справиться с более сложным типом неразрывных отступов. Чтобы все получилось, нужно придерживаться инструкции:

Следует выбрать нужный столбец или строчку, в которой требуется произвести манипуляцию по удалению ненужных промежутков и рядом (напротив, если столбец и ниже, если строка) создать еще один столбец, строчку.
Теперь в ячейке только что созданной строчки или столбца следует ввести функцию, которая выглядит так: = СЖПРОБЕЛЫ (номер соседней ячейке, где есть лишние пропуски). В англоязычной версии вместо СЖПРОБЕЛЫ вставляется TRIM.
Дальше нужно продублировать эту функцию, просто растянув ее на все остальные ячейки.
Следующим шагом будет, замена первого столбца (или строчки) на тот, где введены формулы. Чтобы это получилось, нужно выделить все ячейки с формулами, скопировать их (Ctrl+C), а после нажать левой кнопкой мыши на первую ячейку первого столбца (или строчки).
Дальше следует нажать сочетание клавиш Shift+F10 и V.

И таким образом произойдет удаление ненужных пробелов между числами или буквами. Второй столбик (или строчку) можно удалить.

Формула ПОДСТАВИТЬ

Кроме первых двух способов есть еще один метод, в котором используется специальная формула «ПОДСТАВИТЬ», если используется англоязычная версия, то «SUBSTITUTE».

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

Форматирование ячеек

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

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

Удаление пробелов только слева или справа

Если нужно произвести удаление пробелов только слева или справа (иначе называются начальные и конечные), нужно выполнить следующие действия:

Убрать пробел слева, в начале строки. Чтобы все сработало нужно ввести в соседнюю ячейку следующую формулу: =ПСТР(A1;НАЙТИ(ЛЕВСИМВ(СЖПРОБЕЛЫ(A1))). Если английская версия, то тогда: =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1)). Где А1 целевое значение, которое нужно обработать.
Убрать пробел справа, в конце строки. В соседнюю ячейку надо ввести следующую формулу: =ЛЕВСИМВ(Ф1;МАКС((ПСТР(A1&ПОВТОР(“ “;99);СТРОКА(А1:А99);1);» «)* СТРОКА(A1:A99))). В англоязычной версии: =LEFT(A1;MAX((MID(A1&REPT(» «;99);ROW(A1:A99),1);» «)*ROW(A1:A99))). Где А1 целевое значение, которое нужно обработать.

Четыре способа, как быстро удалить пробелы в Excel

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

Как удалить пробелы в Excel

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

Способ 1: с помощью инструмента «Заменить»

Если вы в процессе работы в программе случайно устанавливали два пробела между словами вместо одного, тогда без проблем удалить пробелы в Excel можно с помощью инструмента «Заменить». Пользоваться им довольно просто:

Перейдите на вкладку «Главная».
На панели инструментов, в правой ее части, нажмите по кнопке «Найти и выделить». Нажмите по ней.
В появившемся выпадающем списке выберите пункт «Заменить».
Появится окно, в котором в поле «Найти» введите двойной пробел. Для этого просто дважды нажмите по соответствующей клавише.
В поле «Заменить на» пропишите один пробел.
Нажмите кнопку «Заменить все».
В окне с отчетом нажмите «ОК».

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

Способ 2: удаление пробелов между разрядами

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

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

После этого вы обнаружите, что пробелы между числами исчезли, соответственно, поставленная задача выполнена.

Способ 3: удаление путем форматирования текста

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

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

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

Способ 4: с помощью специальной функции

Все вышеописанные методы неэффективны в том случае, если нужно в Excel удалить пробелы, находящиеся в начале или в конце значения. Однако с этим безукоризненно справляется функция СЖПРОБЕЛЫ.

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

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

Источник

Это похоже на вашу предыдущую проблему с добавленным поворотом, что имя листа состоит из нескольких слов. Excel обрабатывает эти ссылки, заключая имя листа в одинарные кавычки (апострофы). Таким образом, строка ссылки в ячейке A1 будет:

='Sheet Space'!E8

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

=INDIRECT("'"&$C$1&"'!E"&ROW()+7)

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

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

Источник

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

Прямая ссылка на ячейки	Имена таблицы и столбцов в Excel
=СУММ(C2:C7)	=СУММ(ОтделПродаж[ОбъемПродаж])

Это сочетание имен таблицы и столбца называется структурированной ссылкой. Имена в структурированных ссылках корректируются при добавлении данных в таблицу или их удалении.

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

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

Менеджер по продажам	Область	Сумма продаж	ПроцентКомиссии
Владимир	Северный	260	10 %
Сергей	Южный	660	15 %
Мария	Восточный	940	15 %
Алексей	Западный	410	12 %
Юлия	Северный	800	15 %
Вадим	Южный	900	15 %

Скопируйте пример данных из приведенной выше таблицы, включая заголовки столбцов, и вставьте их в ячейку A1 нового листа Excel.
Чтобы создать таблицу, выделите любую ячейку в диапазоне данных и нажмите клавиши CTRL+T.
Установите флажок Моя таблица с заголовками и нажмите кнопку ОК.
В ячейке E2 введите знак равенства (=) и щелкните ячейку C2.

В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].
Введите звездочку (*) непосредственно после закрывающей скобки и щелкните ячейку D2.

В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].
Нажмите клавишу ВВОД.

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

Что произойдет, если я буду использовать прямые ссылки на ячейки?

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

В образце листа щелкните ячейку E2.
В строке формул введите =C2*D2 и нажмите клавишу ВВОД.

Обратите внимание на то, что хотя Excel копирует формулу вниз по столбцу, структурированные ссылки не используются. Если, например, вы добавите столбец между столбцами C и D, вам придется исправлять формулу.

Как изменить имя таблицы?

При создании таблицы Excel ей назначается имя по умолчанию («Таблица1», «Таблица2» и т. д.), но его можно изменить, чтобы сделать более осмысленным.

Выберите любую ячейку в таблице, чтобы отобразить вкладку Работа с таблицами > Конструктор на ленте.
Введите нужное имя в поле Имя таблицы и нажмите клавишу ВВОД.

В этом примере мы используем имя ОтделПродаж.

При выборе имени таблицы соблюдайте такие правила:

Используйте допустимые символы. Имя всегда должно начинаться с буквы, символа подчеркивания (_) или обратной косой черты (). Остальная часть имени может включать в себя буквы, цифры, точки и символы подчеркивания. В имени нельзя использовать латинские буквы C, c, R и r, так как они служат для быстрого выделения столбца или строки с активной ячейкой при вводе их в поле Имя или Перейти.

Не используйте ссылки на ячейки. Имена не могут иметь такой же вид, как ссылки на ячейки, например Z$100 или R1C1.
Не используйте пробелы для разделения слов. В имени нельзя использовать пробелы. Можно использовать символ подчеркивания (_) и точку (.). Примеры допустимых имен: ОтделПродаж, Налог_на_продажи, Первый.квартал.
Используйте не более 255 знаков. Имя таблицы может содержать не более 255 знаков.
Использование уникальных имен таблиц Повторяющиеся имена не допускаются. Excel не различает символы в верхнем и нижнем регистрах в именах, поэтому если вы введете «Продажи», но уже имеете другое имя «SALES» в той же книге, вам будет предложено выбрать уникальное имя.
Использование идентификатора объекта Если вы планируете использовать сочетание таблиц, сводных таблиц и диаграмм, рекомендуется префиксировать имена с помощью типа объекта. Например, tbl_Sales для таблицы продаж, pt_Sales для сводной таблицы продаж и chrt_Sales для диаграммы продаж или ptchrt_Sales для сводной диаграммы продаж. При этом все имена будут храниться в упорядоченном списке в диспетчере имен.

Правила синтаксиса структурированных ссылок

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

=СУММ(ОтделПродаж[[#Итого],[ОбъемПродаж]],ОтделПродаж[[#Данные],[ОбъемКомиссии]])

В этой формуле используются указанные ниже компоненты структурированной ссылки.

Имя таблицы:
DeptSales — это пользовательское имя таблицы. Он ссылается на данные таблицы без каких-либо строк заголовка или итогов. Вы можете использовать имя таблицы по умолчанию, например Table1, или изменить его, чтобы использовать пользовательское имя.
Описатель столбцов:
[Сумма продаж]
и
[Сумма комиссии] — это описатели столбцов, которые используют имена столбцов, которые они представляют. Они ссылаются на данные столбца без заголовка столбца или строки итогов. Всегда заключайте описатели в квадратные скобки, как показано ниже.
Описатель элемента:
[#Totals] и [#Data] — это специальные описатели элементов, которые ссылаются на определенные части таблицы, например на строку итогового значения.
Табличный описатель:
[#Totals], [Сумма продаж]] и [[#Data],[Сумма комиссии]] являются табличными описателями, представляющими внешние части структурированной ссылки. Внешние ссылки следуют за именем таблицы и заключают их в квадратные скобки.
Структурированная ссылка:
(DeptSales[[#Totals],[Sales Amount]] и DeptSales[[#Data],[Commission Amount]] представляют собой структурированные ссылки, представленные строкой, которая начинается с имени таблицы и заканчивается описателем столбца.

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

Заключайте указатели в квадратные скобки. Все указатели таблиц, столбцов и специальных элементов должны быть заключены в парные скобки ([ ]). Указатель, содержащий другие указатели, требует наличия таких же внешних скобок, в которые будут заключены внутренние скобки других указателей. Например: =DeptSales[[Sales Person]:[Region]]
Все заголовки столбцов — это текстовые строки. Но для них не требуются кавычки, если они используются в структурированной ссылке. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Нельзя использовать выражения с заголовками столбцов. Например, выражение ОтделПродажСводкаФГ[[2014]:[2012]] недопустимо.

Заключайте в квадратные скобки заголовки столбцов, содержащие специальные знаки. Если присутствуют специальные знаки, весь заголовок столбца должен быть заключен в скобки, а это означает, что для указателя столбца потребуются двойные скобки. Пример: =ОтделПродажСводкаФГ[[Итого $]]

Дополнительные скобки в формуле нужны при наличии таких специальных знаков:

TAB
Канал строки
Возврат каретки
Запятая (,)
Двоеточие (:)
Точка (.)
Левая скобка ([)
Правая скобка (])
Знак фунта (#)
Одна кавычка (‘)
Двойная кавычка («)
Левая фигурная скобка ({)
Правая фигурная скобка (})
Знак доллара ($)
Caret (^)
Амперсанд (&)
Звездочка (*)
Знак «плюс» (+)
Знак равенства (=)
Знак минус (-)
Больше символа (>)
Меньше символа (<)
Знак деления (/)
При знаке (@)
Обратная косая черта ()
Восклицательный знак (!)
Левая скобка (()
Правая скобка ())
Знак процента (%)
Вопросительный знак (?)
Обратный тик (‘)
Точка с запятой (;)
Тильда (~)
Подчеркивание (_)

Используйте escape-символы для некоторых специальных знаков в заголовках столбцов. Перед некоторыми знаками, имеющими специфическое значение, необходимо ставить одинарную кавычку (‘), которая служит escape-символом. Пример: =ОтделПродажСводкаФГ[‘#Элементов]

Ниже приведен список специальных символов, которым требуется escape-символ (‘) в формуле:

Левая скобка ([)
Правая скобка (])
Знак фунта(#)
Одна кавычка (‘)
При знаке (@)

Используйте пробелы для повышения удобочитаемости структурированных ссылок. С помощью пробелов можно повысить удобочитаемость структурированной ссылки. Пример: =ОтделПродаж[ [Продавец]:[Регион] ] или =ОтделПродаж[[#Заголовки], [#Данные], [ПроцентКомиссии]].

Рекомендуется использовать один пробел:

После первой левой скобки ([)
Перед последней правой скобкой (]).
После запятой.

Операторы ссылок

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

Эта структурированная ссылка:	Ссылается на:	Используя:	Диапазон ячеек:
=ОтделПродаж[[Продавец]:[Регион]]	Все ячейки в двух или более смежных столбцах	: (двоеточие) — оператор ссылки	A2:B7
=ОтделПродаж[ОбъемПродаж],ОтделПродаж[ОбъемКомиссии]	Сочетание двух или более столбцов	, (запятая) — оператор объединения	C2:C7, E2:E7
=ОтделПродаж[[Продавец]:[ОбъемПродаж]] ОтделПродаж[[Регион]:[ПроцентКомиссии]]	Пересечение двух или более столбцов	(пробел) — оператор пересечения	B2:C7

Указатели специальных элементов

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

Этот указатель специального элемента:	Ссылается на:
#Все	Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).
#Данные	Только строки данных.
#Заголовки	Только строка заголовка.
#Итого	Только строка итога. Если ее нет, будет возвращено значение null.
#Эта строка ИЛИ @ ИЛИ @[Имя столбца]	Только ячейки в той же строке, где располагается формула. Эти указатели нельзя сочетать с другими указателями специальных элементов. Используйте их для установки неявного пересечения в ссылке или для переопределения неявного пересечения и ссылки на отдельные значения из столбца. Excel автоматически заменяет указатели «#Эта строка» более короткими указателями @ в таблицах, содержащих больше одной строки данных. Но если в таблице только одна строка, Excel не заменяет указатель «#Эта строка», и это может привести к тому, что при добавлении строк вычисления будут возвращать непредвиденные результаты. Чтобы избежать таких проблем при вычислениях, добавьте в таблицу несколько строк, прежде чем использовать формулы со структурированными ссылками.

Определение структурированных ссылок в вычисляемых столбцах

Когда вы создаете вычисляемый столбец, для формулы часто используется структурированная ссылка. Она может быть неопределенной или полностью определенной. Например, чтобы создать вычисляемый столбец с именем Commission Amount, который вычисляет сумму комиссии в долларах, можно использовать следующие формулы:

Тип структурированной ссылки	Пример	Примечания
Неопределенная	=[ОбъемПродаж]*[ПроцентКомиссии]	Перемножает соответствующие значения из текущей строки.
Полностью определенная	=ОтделПродаж[ОбъемПродаж]*ОтделПродаж[ПроцентКомиссии]	Перемножает соответствующие значения из каждой строки обоих столбцов.

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

Примеры использования структурированных ссылок

Ниже приведены примеры использования структурированных ссылок.

Эта структурированная ссылка:	Ссылается на:	Диапазон ячеек:
=ОтделПродаж[[#Все],[ОбъемПродаж]]	Все ячейки в столбце «ОбъемПродаж».	C1:C8
=ОтделПродаж[[#Заголовки],[ПроцентКомиссии]]	Заголовок столбца «ПроцентКомиссии».	D1
=ОтделПродаж[[#Итого],[Регион]]	Итог столбца «Регион». Если нет строки итогов, будет возвращено значение ноль.	B8
=ОтделПродаж[[#Все],[ОбъемПродаж]:[ПроцентКомиссии]]	Все ячейки в столбцах «ОбъемПродаж» и «ПроцентКомиссии».	C1:D8
=ОтделПродаж[[#Данные],[ПроцентКомиссии]:[ОбъемКомиссии]]	Только данные в столбцах «ПроцентКомиссии» и «ОбъемКомиссии».	D2:E7
=ОтделПродаж[[#Заголовки],[Регион]:[ОбъемКомиссии]]	Только заголовки столбцов от «Регион» до «ОбъемКомиссии».	B1:E1
=ОтделПродаж[[#Итого],[ОбъемПродаж]:[ОбъемКомиссии]]	Итоги столбцов от «ОбъемПродаж» до «ОбъемКомиссии». Если нет строки итогов, будет возвращено значение null.	C8:E8
=ОтделПродаж[[#Заголовки],[#Данные],[ПроцентКомиссии]]	Только заголовок и данные столбца «ПроцентКомиссии».	D1:D7
=ОтделПродаж[[#Эта строка], [ОбъемКомиссии]] ИЛИ =ОтделПродаж[@ОбъемКомиссии]	Ячейка на пересечении текущей строки и столбца Commission Amount. При использовании в той же строке, что и заголовок или итоговая строка, возвращается ошибка #VALUE! . Если ввести длинную форму этой структурированной ссылки (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее укороченной формой (со знаком @). Две эти формы идентичны.	E5 (если текущая строка — 5)

Методы работы со структурированными ссылками

При работе со структурированными ссылками учитывайте следующее.

Автозаполнение формул может оказаться очень полезным при вводе структурированных ссылок для соблюдения правил синтаксиса. Дополнительные сведения см. в статье Использование автозаполнения формул.
Решите, следует ли создавать структурированные ссылки для таблиц в полувыборах По умолчанию при создании формулы при щелчке диапазона ячеек в таблице выбирается полуэлемерная ячейка и автоматически вводится структурированная ссылка вместо диапазона ячеек в формуле. Псевдовыбор облегчает ввод структурированной ссылки. Это поведение можно включить или отключить, установив или снимите флажок Использовать имена таблиц в формулах в диалоговом окне Параметры файлов > > Формулы > Работа с формулами.
Использование книг с внешними ссылками на таблицы Excel в других книгах Если книга содержит внешнюю ссылку на таблицу Excel в другой книге, эта связанная исходная книга должна быть открыта в Excel, чтобы избежать ошибок #REF! в целевой книге, содержащей ссылки. Если сначала открыть целевую книгу и #REF! появятся ошибки, они будут устранены при открытии исходной книги. Если сначала открыть книгу с исходным кодом, коды ошибок не будут отображаться.
Преобразование диапазона в таблицу и таблицы в диапазон. При преобразовании таблицы в диапазон все ссылки на ячейки изменяются на эквивалентные абсолютные ссылки стиля A1. При преобразовании диапазона в таблицу Excel не изменяет автоматически ссылки на ячейки этого диапазона на эквивалентные структурированные ссылки.
Отключение заголовков столбцов. Вы можете включить и отключить заголовки столбцов таблицы на вкладке Конструктор таблицы > строке заголовков. Если отключить заголовки столбцов таблицы, структурированные ссылки, использующие имена столбцов, не затрагиваются, и вы по-прежнему можете использовать их в формулах. Структурированные ссылки, которые ссылаются непосредственно на заголовки таблицы (например, =DeptSales[[#Headers],[%Commission]]), приведут к #REF.
Добавление и удаление столбцов и строк в таблице. Так как диапазоны табличных данных часто меняются, ссылки на ячейки для структурированных ссылок настраиваются автоматически. Например, если вы используете имя таблицы для подсчета всех ячеек в ней, и добавляете строку данных, ссылка на ячейки автоматически меняется.
Переименование таблицы или столбца. Если переименовать столбец или таблицу, в приложении Excel автоматически изменится название этой таблицы или заголовок столбца, используемые во всех структурированных ссылках книги.
Перемещение, копирование и заполнение структурированных ссылок Все структурированные ссылки остаются неизменными при копировании или перемещении формулы, которая использует структурированную ссылку.

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

Направление заполнения:	И при заполнении нажимаете :	Выполняется действие:
Вверх или вниз	Не нажимать	Указатели столбцов не будут изменены.
Вверх или вниз	CTRL	Указатели столбцов настраиваются как ряд.
Вправо или влево	Нет	Указатели столбцов настраиваются как ряд.
Вверх, вниз, вправо или влево	SHIFT	Вместо перезаписи значений в текущих ячейках будут перемещены текущие значения ячеек и вставлены указатели столбцов.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Статьи по теме

Общие сведения о таблицах

Excel Видео: создание и форматирование таблицы
Excel Итог данных в таблице
Excel Форматирование таблицы
Excel Изменение размера таблицы путем добавления или удаления строк и столбцов
Фильтрация данных в диапазоне или таблице
Преобразование таблицы в диапазон
Проблемы
с совместимостью таблиц ExcelЭкспорт таблицы Excel в SharePoint
Общие сведения о формулах в Excel

Источник

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

один ведущий пробел и более;
один завершающий пробел и более;
два и более последовательных пробела в самом тексте.

Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
Выполните команду Главная —> Условное форматирование —> Создать правило, чтобы открыть диалоговое окно Создание правила форматирования (рис. 3).
В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
Нажмите Ok два раза.

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. — СПб.: Питер, 2014. — С. 183–185.

Источник

Удаление лишних пробелов в Microsoft Excel

Технология удаления пробелов

Способ 1: использование инструмента «Заменить»

С заменой двойных пробелов между словами на одинарные в Экселе отлично справляется инструмент «Заменить».

«Главная»

«Найти и выделить»

«Редактирование»

«Заменить»

Ctrl+H

Таким образом, мы избавились от лишних двойных пробелов между словами в документе.

Способ 2: удаление пробелов между разрядами

«Найти и выделить»

«Редактирование»

«Главная»

«Заменить»

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

Способ 3: удаление разделителей между разрядами путем форматирования

«Формат ячеек…»

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

Способ 4: удаление пробелов с помощью функции

Синтаксис этого оператора довольно прост и имеет всего один аргумент:

«Вставить функцию»

Отблагодарите автора, поделитесь статьей в социальных сетях.

Как убрать лишние пробелы в Excel?

Рассмотрим способы удаления пробелов в Excel (например, между словами, в начале или конце предложений, в числах).

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

Достаточно часто лишние пробелы в Excel встречаются в тексте в следующих случаях:

В начале или в конце предложений;
Дублирование пробелов;
Невидимые символы, в т.ч. неразрывный пробел;
В числах (в разделителях разрядов).

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

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

Как удалить пробелы в Excel?

Рассмотрим 2 основных способа, как можно удалить лишние символы:

Инструмент «Найти и заменить». Удаление пробелов через диалоговое окно;
С помощью функций. Формульный вариант удаления.

Инструмент «Найти и заменить»

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

Для начала выделяем диапазон ячеек с данными и выбираем на панели вкладок Главная -> Редактирование -> Найти и выделить -> Заменить либо просто нажимаем сочетание клавиш Ctrl + H:

Появляется стандартное диалоговое окно замены, затем вводим в поле Найти два пробела, а в Заменить на — один. Нажимаем Заменить все и повторяем операцию до тех пор, пока Excel находит повторения.

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

С помощью функций

В этом случае можно воспользоваться стандартной функцией СЖПРОБЕЛЫ, которая удаляет все пробелы, за исключением одиночных между словами.

Предположим, что у нас имеется список фамилий (столбец A) содержащий лишние пробелы как между словами, так и в начале или конце фамилии. Давайте создадим вспомогательный столбец (столбец B), куда введем формулу СЖПРОБЕЛЫ ссылающуюся на столбец с фамилиями:

Далее копируем содержимое столбца B и вставляем значения без связей в столбец A, после чего удаляем вспомогательный столбец.
Минусом данного способа является его неудобство при работе с большими объемами данных, так как для каждой редактируемой ячейки необходимо создавать дополнительное место где будет обрабатываться исходная ячейка.

Неразрывный пробел в Excel

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

Данный символ внешне выглядит как обычный пробел, однако его нельзя удалить с помощью функции СЖПРОБЕЛЫ или инструмента «Найти и Заменить» (при вводе в поле для замены обычного пробела).

Как видим в ячейке A2 содержатся лишние пробелы, но функция СЖПРОБЕЛЫ их не удаляет.

Каждый символ в Excel имеет свою кодировку в таблице знаков ANSI (для ОС Windows), в том числе и интересные нам одиночный пробел (символ 32) и неразрывный (символ 160).

Поэтому с помощью функции ПОДСТАВИТЬ заменяем неразрывный пробел (символ 160) на обычный (символ 32), а затем к полученному результату применяя функцию СЖПРОБЕЛЫ получаем требуемый результат:

На самом деле убрать неразрывный пробел можно и с помощью «Найти и заменить», для этого в поле Найти необходимо ввести неразрывный пробел и заменить его либо на обычный, либо удалить, оставив поле Заменить на пустым.

Возникает вопрос, как это сделать?

Первый способ — непосредственно скопировать данный символ из текста и ввести в поле Найти.

Второй способ — воспользоваться Alt-кодом (для систем Windows) и ввести комбинацию Alt + 0160 (удерживая клавишу Alt нужно набрать 0160 на цифровой клавиатуре).

Как убрать пробелы в числах в Excel?

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

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

Аналогичный результат можно получить и с помощью формулы:

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

30 функций Excel за 30 дней: СЖПРОБЕЛЫ (TRIM)

Вчера в марафоне 30 функций Excel за 30 дней мы наткнулись на ленивого шурина по имени AREAS (ОБЛАСТИ). Эта функция не часто используется на практике, но с ее помощью мы увидели, как работают три оператора ссылок в Excel. Надеюсь, что этот урок был для Вас полезен.

Третий день марафона мы посвятим изучению функции TRIM (СЖПРОБЕЛЫ). В Январе кое-кто пытается сбросить несколько лишних килограммов, и для этой цели можно использовать программу Excel, чтобы сделать в ней счётчик калорий или график снижения веса. К сожалению, функция TRIM (СЖПРОБЕЛЫ) не поможет Вам в борьбе с лишними килограммами, зато может удалить лишние пробелы из текстовой строки.

Итак, давайте посмотрим на справочную информацию и примеры использования функции TRIM (СЖПРОБЕЛЫ) в Excel. Если у Вас есть свои приёмы или примеры по этой функции, пожалуйста, делитесь ими в комментариях. И удачи с подсчётом калорий!

Функция 03: TRIM (СЖПРОБЕЛЫ)

Функция TRIM (СЖПРОБЕЛЫ) удаляет из текстовой строки все пробелы, кроме одинарных пробелов между словами.

Как можно использовать функцию TRIM (СЖПРОБЕЛЫ)?

Функция TRIM (СЖПРОБЕЛЫ) может помочь с очисткой текста, загруженного с веб-сайта или импортированного из другого приложения. Функция TRIM (СЖПРОБЕЛЫ):

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

Синтаксис TRIM (СЖПРОБЕЛЫ)

Функция TRIM (СЖПРОБЕЛЫ) имеет вот такой синтаксис:

TRIM(text)
СЖПРОБЕЛЫ(текст)

text (текст) – это ссылка на ячейку или текстовая строка, из которой надо удалить пробелы

Ловушки TRIM (СЖПРОБЕЛЫ)

Функция TRIM (СЖПРОБЕЛЫ) удаляет только стандартные символы пробела из текста. Если Вы копируете текст с веб-сайта, он может содержать символы неразрывного пробела, которые функция TRIM (СЖПРОБЕЛЫ) удалить не сможет.

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

Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ), чтобы удалить все пробелы в начале и в конце текстовой строки. На рисунке ниже в ячейке C5 записан текст с двумя лишними пробелами в начале и двумя в конце строки. Функция TRIM (СЖПРОБЕЛЫ) в ячейке C7 удаляет эти 4 лишних пробела.

Пример 2: Удаляем все пробелы, кроме одиночных между словами

Вы можете применить функцию TRIM (СЖПРОБЕЛЫ) к лишним пробелам между словами в тексте. На рисунке ниже в ячейке C5 между слов есть три лишних пробела. Функция TRIM (СЖПРОБЕЛЫ) в ячейке C7 удаляет эти пробелы, а также по два лишних пробела в начале и конце текстовой строки.

Пример 3: Как НЕ удаляются некоторые специальные символы

Функция TRIM (СЖПРОБЕЛЫ) НЕ удаляет некоторые символы, используемые в качестве пробела. Например, неразрывный пробел, наверняка будет присутствовать в тексте, скопированном с веб-сайта. На рисунке ниже ячейка C5 содержит один неразрывный пробел, и он не удаляется.

Символ неразрывного пробела Вы можете удалить вручную, использовать для этого функцию SUBSTITUTE (ПОДСТАВИТЬ) или макрос. Позднее, в течение нашего марафона 30 функций Excel за 30 дней, Вы узнаете еще несколько способов очистки данных в Excel.

Четыре способа, как быстро удалить пробелы в Excel

Как удалить пробелы в Excel

Способ 1: с помощью инструмента «Заменить»

Перейдите на вкладку «Главная».
На панели инструментов, в правой ее части, нажмите по кнопке «Найти и выделить». Нажмите по ней.
В появившемся выпадающем списке выберите пункт «Заменить».
Появится окно, в котором в поле «Найти» введите двойной пробел. Для этого просто дважды нажмите по соответствующей клавише.
В поле «Заменить на» пропишите один пробел.
Нажмите кнопку «Заменить все».
В окне с отчетом нажмите «ОК».

Способ 2: удаление пробелов между разрядами

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

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

После этого вы обнаружите, что пробелы между числами исчезли, соответственно, поставленная задача выполнена.

Способ 3: удаление путем форматирования текста

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

Способ 4: с помощью специальной функции

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

Как убрать лишние пробелы в Excel?

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

Достаточно часто лишние пробелы в Excel встречаются в тексте в следующих случаях:

В начале или в конце предложений;
Дублирование пробелов;
Невидимые символы, в т.ч. неразрывный пробел;
В числах (в разделителях разрядов).

Как удалить пробелы в Excel?

Рассмотрим 2 основных способа, как можно удалить лишние символы:

Инструмент «Найти и заменить». Удаление пробелов через диалоговое окно;
С помощью функций. Формульный вариант удаления.

Инструмент «Найти и заменить»

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

С помощью функций

Неразрывный пробел в Excel

Как видим в ячейке A2 содержатся лишние пробелы, но функция СЖПРОБЕЛЫ их не удаляет.

Возникает вопрос, как это сделать?

Первый способ — непосредственно скопировать данный символ из текста и ввести в поле Найти.

Как убрать пробелы в числах в Excel?

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

Источник