инфохаб «Selection»

Сосредоточьтесь на главном

инфохаб «Selection»

Excel

50+ полезных советов и тонкостей при работе с Excel

Полезные советы и хитрости при работе с Excel, решения наиболее частых проблем и вопросов при работе с программой

 

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

Microsoft Excel (Microsoft Office Excel) — популярная программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS, а также Android, iOS и Windows Phone. Она предоставляет возможности экономико-статистических расчетов, графические инструменты, язык макропрограммирования потоков данных Power Query и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic for Application). Microsoft Excel входит в состав Microsoft Office.

Официальная страница поддержки Microsoft Office / Microsoft 365 -  support.microsoft.com/ru-ru/microsoft-365

 

Excel. Тонкости работы, решения частых проблем


Личные шаблоны. Сохранение книги в виде шаблона

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

Сохранение книги в виде шаблона
Если вы впервые сохраняете книгу в виде шаблона, сначала укажите расположение личных шаблонов по умолчанию.
Выберите Файл > Параметры.
Нажмите Сохранение, а затем в разделе Сохранение книг укажите путь к месту расположения личных шаблонов в поле Расположение личных шаблонов по умолчанию.
Этот путь обычно: C:\Users\ [имя пользователя]\Documents\Настраиваемые шаблоны Office
Нажмите кнопку ОК.

Если этот параметр настроен, все настраиваемые шаблоны, сохраненные в папке "Мои шаблоны", автоматически отображаются в разделе Личные на странице Создать (Файл > Создать).

Откройте книгу, которую вы хотите использовать в качестве шаблона.
Выберите Файл > Экспорт.
В разделе Экспорт выберите команду Изменить тип файла.
В поле Типы файлов книг дважды щелкните пункт Шаблон.
В поле Имя файла введите имя шаблона.
Нажмите кнопку Сохранить, а затем закройте шаблон.

Создание книги на основе шаблона
Выберите Файл > Создать.
Выберите пункт Личные.
Папка личных шаблонов
Дважды щелкните только что созданный шаблон.
В приложении Excel будет создана книга на основе шаблона.

Шаблон по умолчанию

Автоматическое открытие определенной книги или шаблона при запуске Excel
Чтобы открывать одну и ту же книгу при запуске Excel, в меню Файл выберите пункт Сохранить как > Компьютер, нажмите Обзор и выберите папку XLSTART.

В чистой установке выпуска Windows Vista или более новой версии путь к папке XLSTART обычно выглядит так:
Office 2013 — C:\Пользователи\(Имя пользователя)\AppData\Microsoft\Excel\XLSTART;
Office 2013 C2R — C:\Пользователи\(Имя пользователя)\AppData\Microsoft\Excel\XLSTART.
Если вы выполнили обновление с другой версии Windows, путь может выглядеть так:
Office 2013 C2R — C:\Program Files\Microsoft Office 15\root\Office 15\XLSTART
Примечание: Если книга или шаблон содержит макрос, который автоматически запускается при открытии книги, например Auto_Open, макрос будет по умолчанию запускаться при запуске Excel.

Отмена открытия конкретной книги при запуске Excel
Чтобы книга не открывалась автоматически при запуске Excel:
перейдите в папку, в которой хранится книга (XLSTART или альтернативная папка), выберите книгу и нажмите Удалить.

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

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

Чтобы создать шаблон:
выберите Файл > Сохранить как;
в поле Тип файла выберите пункт Шаблон;
в поле Сохранить в укажите папку, в которую нужно сохранить шаблон;
в поле Имя файла сделайте следующее:
чтобы создать шаблон книги по умолчанию, введите Книга;
чтобы создать шаблон листа по умолчанию, введите Лист;
чтобы создать настраиваемый шаблон книги или листа, введите любое имя;
нажмите Сохранить;
выберите Файл > Закрыть.

Макросы. Работа с макросами

Как включить макросы
Макрос включить Окно разработчика
Параметры- Настроить ленту -Разработчик
Можно создать отдельную книгу – сохранить отдельно в формате книга с макросами
Макросы привязываются к книге. Можно привязать все макросы к отдельной книге и производить нужные операции– затем данные переносить в обычные книги
Запись макроса - вкладка Вид
ActiveCell.Offset(0, 0).Select  


Подробнее
ActiveCell.Offset(1, 0).Select - перемещение вниз от выделенной ячейки  
ActiveCell.Offset(-1, 0).Select - перемещение вверх от выделенной ячейки  
ActiveCell.Offset(0, 1).Select - перемещение вправо от выделенной ячейки  
ActiveCell.Offset(1, -1).Select - перемещение влево от выделенной ячейки
ActiveCell(2).Select - перемещение вниз от выделенной ячейки  
ActiveCell(0).Select - перемещение вверх от выделенной ячейки  
ActiveCell(, 2).Select - перемещение вправо от выделенной ячейки  
ActiveCell(, 0).Select - перемещение влево от выделенной ячейки  

Примеры

Sub Макрос1()
   'Offset(0, 1) - это ячейка, которая находится
       'в той же строке, но в следующем столбце.
   ActiveCell.Offset(0, 1).Value = ActiveCell.Value
   'Очистка активной ячейки.
   ActiveCell.Formula = ""
End Sub
   Sub vp()
ActiveCell(2).Select
i = i + 2 'наращиваем цикл
Wend
End Sub0

Отмена запуска автоматического макроса при запуске Excel

Автоматический макрос (например, Auto_Open), записанный в книгу, которая открывается при запуске Excel, запускается сразу же при ее открытии.
Чтобы макрос не запустился, удерживайте клавишу SHIFT во время запуска Excel.

Замена знаков (букв и цифр)

В поле найти введите текст или числа, которые вы хотите найти
В условиях поиска можно использовать подстановочные знаки, например вопросительный знак (?) и звездочку (*).
Звездочка используется для поиска любой строки знаков. Например, если ввести г*д, то будут найдены слова "год" и "город".
*- любое количество знаков
Вопросительный знак заменяет один любой знак. Например, если ввести г?д, то будут найдены слова "гад", "гид" и "год".
Совет: Звездочки, вопросительные знаки и тильда (~) можно найти в данных листа, поставив перед ними символ тильды в поле найти. Например, чтобы найти данные, содержащие слово "_км_", введите в качестве условия поиска строку ~ .

Подстановочные знаки
* - любое количество символов (включая ни одного символа)
? - один любой символ
Если нужно найти (заменить) именно звездочку или знак вопроса, то перед ними нужно поставить тильду, вот так будет искать знак "*": ~*

Примеры:
1. д?м - ищет "дом", "дым", "Дум", ...
2. д*м - ищет все то, что в п.1 и плюс к этому "джем", "детдом", ...
3. д?м? - ищет "дума", "дома", "Дима", ...
4. д?м* - ищет все то, что в п.1, в п.3 и плюс к этому "демократия", "демонстрация", "домик", ...
5. д*м? - ищет "дерьмо", "драхма", "Динамо", ...
6. д*м* - ищет все то, что в п.1-5, плюс к этому "доминанта", "драмма", "драмма", "Дмитрий", ...
7. ?д?м* - ищет "адамас", "вдумчивость", ...
8. *д?м? - ищет все то, что в п.3 и плюс к этому "ведьма"; "диадема", ...
9. *д?м* - ищет все то, что в п.4 и плюс к этому "расдумья", "содомия"; "Адам" ...
10. *д*м? - ищет "аденома", "подпрограмма", ...
11. *д*м* - ищет "таксидермист", "недоумок", "адмирал", ...

Замена перевода строки
Чтобы ввести в поле "Найти" знак перевода строки, нужно нажать Ctrl-J
Ctrl-J нажимать, когда курсор находится в поле "Найти".

Найти и выбрать лист в книге

Щелкните правой кнопкой мыши по кнопкам прокрутки, находящимся слева от ярлычков листов и выберите нужный лист:  

Сортировка

Существует два способа открыть меню сортировки:

1
Щелкнуть правой кнопкой мыши по таблице.
Выбрать «Сортировку» и способ.

2
Контекстное меню.
Открыть вкладку «Данные» - диалоговое окно «Сортировка».
Сортировка таблицы по отдельному столбцу:
Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач.
Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Выбор.
Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.

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

Автоматическая нумерация строк

В отличие от других программ Microsoft Office, в Excel нет кнопки для автоматической нумерации данных. Однако можно легко добавить последовательные числа в строки данных путем перетаскивания маркера заполнения, чтобы заполнить столбец последовательностью чисел, или с помощью функции СТРОКА.

Заполнение столбца последовательностью чисел
Выделите первую ячейку в диапазоне, который необходимо заполнить.
Введите начальное значение последовательности.
Введите значение в следующую ячейку, чтобы задать шаблон.
СОВЕТ : Например, если необходимо ввести последовательность 1, 2, 3, 4, 5..., введите в две первых ячейки значения 1 и 2. Если необходимо ввести последовательность 2, 4, 6, 8..., введите значения 2 и 4.
Выделите ячейки, содержащие начальные значения.
Перетащите маркер заполнения Маркер заполнения , охватив диапазон, который необходимо заполнить.
Для заполнения в порядке возрастания перетащите маркер вниз или вправо, в порядке убывания — вверх или влево.
СОВЕТ : Если маркер заполнения не отображается, возможно, его сначала необходимо отобразить. Дополнительные сведения см. в статье Отображение или скрытие маркера заполнения.

Выделить все графические объекты

Найти и выделить
Выделить группу ячеек
Объекты

Удалить пустые строки

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

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

Фильтр
Диапазон должен быть отформатирован как таблица с заголовками.
Выделяем «шапку» таблицы.
На вкладке «Данные» нажимаем кнопку «Фильтр» («Сортировка и фильтр»). Справа от названия каждого столбца появится стрелочка вниз.
Нажимаем – открывается окно фильтрации. Снимаем выделение напротив имени «Пустые».

Делаем первую букву в ячейке прописной

По формуле:
=ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ЛЕВСИМВ(A1;1)))
Записываем формулу в соседнюю ячейку, подставляем вместо А1 адрес ячейки с текстом , затем копируем чтобы получить для других ячеек

Отменить замену дроби на дату при вставке

Параметры
Дополнительно (идем в конец списка)
Параметры совместимости с lotus для –конкретн книга, лист
Преобразовывать формулы в формат Exel ставим галочку

Округлить целое число

Формула: =ОКРУГЛ(A3;-3).
Округлить можно не только число, но и значение выражения.
Первый аргумент функции – числовое выражение для нахождения стоимости.

Найти/ выделить/ удалить гиперссылки в Excel

CTRL + F» - диалоговое окно « Найти и заменить ».
Нажмите кнопку Параметры внизу.
Теперь нажмите стрелку вниз на кнопке « Формат » и выберите « Выбрать формат из ячейки ».
Выберите ячейку с гиперссылкой, и она покажет предварительный просмотр (слева от кнопки «Формат») с форматом гиперссылки.
Нажмите кнопку Найти все , и она покажет вам все гиперссылки в Excel.
Вы можете выбрать одну или несколько гиперссылок из результата с помощью кнопок CTRL или Shift .
Чтобы удалить их, щелкните правой кнопкой мыши выделенные ячейки и выберите « Удалить гиперссылки ».

Перенос текста на новую строку внутри одной ячейки

Для переноса текста на новую строку внутри одной ячейки Excel необходимо нажать ALT+ENTER (зажимаете клавишу ALT, затем не отпуская ее, нажимаете клавишу ENTER)

Создание своего стиля таблицы

support.office.com/ru-ru/article

Транспонировать (поменять строки и столбцы местами)

Выделяем всю таблицу и копируем ее (CTRL+C), ставим курсор в любом месте листа Excel и правой кнопкой вызываем меню, кликаем по команде специальная вставка.
В появившемся окне ставим галочку возле пункта транспонировать.
Остальное оставляем как есть и жмем ОК.

Изменение порядка рядов в диаграмме

Расставляем данные в линейчатой диаграмме Excel в порядке убывания/возрастания без сортировки исходных данных
Если порядок исходных данных на рабочем листе имеет значение и изменён быть не может, давайте заставим полосы диаграммы появиться в точности в том же порядке.
В линейчатой диаграмме Excel кликните правой кнопкой мыши по любой из подписей вертикальной оси и в контекстном меню нажмите Формат оси (Format Axis). Или просто дважды кликните по подписям вертикальной оси.
В появившейся панели в разделе Параметры оси (Axis Options) настройте следующие параметры:
В группе Пересечение с горизонтальной осью (Horizontal axis crosses) выберите В точке с максимальным значением категории (At maximum category).
В группе Положение оси (Axis position) выберите Обратный порядок категорий (Categories in reverse order).

Невозможно выполнить операции с числами. Текст –число не устанавливается числовой формат.

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

Причина 1. В записи числа присутствуют посторонние символы.

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.
Убрать лишние пробелы также можно с помощью операции Найти/Заменить.
В поле Найти вводим пробел (нужно скопировать пустое пространство между цифрами, буквами для замены), а поле Заменить на оставляем пустым, далее Заменить все.
Если в числе были обычные пробелы, то этих действий будет достаточно.
Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).
Пробелы можно удалить и формулой. Варианты:
Для обычных пробелов: =--ПОДСТАВИТЬ(B4;" ";"")
Для неразрывных пробелов: =--ПОДСТАВИТЬ(B4;СИМВОЛ(160);"")
Сразу для тех и других пробелов: =--ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);"");" ";"")
Иногда, чтобы добиться желаемого результата, приходится комбинировать перечисленные способы. Например, сначала удалять пробелы, а затем преобразовывать формат ячеек

Причина 2. Число сохранено как текст

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

Есть несколько способов решения данной проблемы

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

С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами -- нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) -- в поле Найти вводим , (запятую) -- в поле Заменить на тоже вводим , (запятую) -- Заменить все.
Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 – Enter
Подобную операцию можно осуществить и с датами с тем лишь отличием, что менять нужно точку на точку.
Кроме того, сторонние программы могут выгружать числа с точкой в качестве десятичного разделителя, тогда поможет замена точки на запятую.
Аналогичную замену можно проделать и формулой, используя функцию ПОДСТАВИТЬ() 

С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку -- выполняем команду Копировать -- выделяем диапазон с проблемными числами -- Специальная вставка -- Сложить -- ОК.
Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат
Вариантом этого приёма может быть умножение диапазона на 1 

С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные -- Текст по столбцам – Готово

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

Каракули и иероглифы в Excel. Неверная кодировка

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

Вариант 1
на вкладке «Данные», в блоке «Получение внешних данных» — нажать кнопку «Из текста»;
указать файл из которого нужно получить информацию;
затем в разделе «Формат данных» выбрать нужный вариант «С разделителями» или «Фиксированной ширины»;
в поле «Формат файла» изменить кодировку на «Юникод Windows», нажать «Далее»;
если раннее был выбран вариант «С разделителями», то на этом шаге можно выбрать символ (табуляция, точка, пробел и т.д.), нажать «Далее»;
на последнем шаге нужно определиться с форматом импортированных ячеек (Общий, текстовый, Дата), нажать «Готово».

Вариант 2
Можно попробовать пересохранить файл с иероглифами в нужной кодировке:
«Файл» — > «Сохранить как»;
щёлкаем по треугольнику после кнопки «Сервис»;
иероглифы в excel вместо текста
выбираем пункт «Параметры веб-документа»;
переходим на закладку «Кодировка» и выбираем вместо Кириллицы Юникод (UTF-8), жмём «ОК».

Если не форматируется ячейка

Вариант 1
Надо все нужные ячейки скопировать, сделать специальную вставку с суммированием с любой пустой ячейкой
Выбираем пустую ячейки. Копируем. Выделяем диапазон который не форматируется. Выбираем "Специальная вставка" (внизу, текст). ставим галочку "суммировать" (сложить) ( с пустой ячейкой)

Вариант 2
Копируем любую ячейку, формат для которой изменяется без проблем
Выделяем "непослушные" ячейки-правая кнопка мыши-Специальная вставка (PasteSpecial) - Форматы (Formats)

Удалить нужное количество символов слева или справа

Определить количество символов в тексте можно с помощью функции ДЛСТР.
Количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ.
Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.

Отмена автоматической замены чисел датами

Приложение Microsoft Excel программируется так, чтобы облегчить ввод дат.
Например, 12/2 меняется на 2-дек. Это очень плохо, когда вы вводите что-то, которое вы не хотите менять на дату. К сожалению, отключить эту функцию невозможно. Но есть и другие способы обойти.
Предформатируйте ячейки, в которых нужно вводить числа в виде текста. В этом случае Excel не будет пытаться изменить данные, вводимые в даты.

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

Апостроф (') перед вводом числа, например, ' 11-53 или ' 1/47. После нажатия клавиши Ввод этот апостроф не отображается в ячейке.
Ноль и пробел перед вводом дробной части, например 1/2 или 3/4, чтобы они не переменялись на 2 января или 4 марта, например. Введите 0 1/2 или 0 3/4.
После нажатия клавиши Ввод ноль не сохраняется в ячейке, а ячейка становится десятичным числом.

Выделение ячеек определенного формата

Способ быстрого выделения СРАЗУ ВСЕХ имеющихся на (одном) Листе ячеек определённого цвета, либо стиля, чтобы потом их сразу все, одновременно, можно было редактировать (например - очистить, или скопировать что-то сразу в каждую из них).
Выделяете диапазон для поиска (или одну ячейку, если поиск по всему листу),
жмете Контрл+F (появится окно "Найти и заменить"),
жмете кнопку "Параметры", дмете стрелку рядом со словом "Формат", выбираете в появившемся списке "Выбрать формат из ячейки", маркер мыши изменится - топаете им на ячейку-образец, жмете кнопку "Найти все".
В этом же окне "Найти и заменить" внизу появится список адресов найденных ячеек, топаете мышой на первую (она выделится) и жмете Контрл+Шифт+Енд (выделятся все ячейки и в списке, и на листе). Закрываете крестиком окно "Найти и заменить" - все найденные на листе ячейки остаются выделенными.

Не выделяются пустые ячейки

Можно предположить, что это вставленный откуда-то диапазон. В этом случае (иногда) бывает так, что пустые ячейки не распознаются пустыми.
Как можно решить проблему:
1. выделить диапазон, поставить автофильтр, в нем выбрать "Пусто", выделить полученное, нажать Альт+Шифт+4, удалить строки;
2. выделить диапазон, нажать Контрл+F, нажать "Найти все", внизу окошка поиска появится список найденного с выделенной первой строкой, прокрутите список до конца и кликните мышкой на последнюю строку, держа при этом нажатым Шифт - выделятся все пустые, затем просто удалить строки.

Выделение чередующихся столбцов или строк. Условное форматирование

Выделяем столбцы или строки таблицы без заголовков.
Активируйте инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
каждый четвертый столбец
=ОСТАТ(СТОЛБЕЦ(A2);4)=0
Щелкните на кнопку «Формат», чтобы задать оформление для столбцов.
каждой второй строки
Таким же самым способом мы можем выделять строки. Для этого нужно немного изменить формулу. Например, для выделения цветом каждой второй строки используем такую формулу:
=ОСТАТ(СТРОКА(A2);2)=0

Не меняется цвет ячеек
Условное форматирование
Удалить правило

Как каждую вторую строку перенести в ячейку рядом с первой? И удалить пустую

Формулы
=ИНДЕКС($C$1:$C$300;СТРОКА()*2-1)
=ИНДЕКС($C$1:$C$300;СТРОКА()*2)

Макрос
Sub Макрос1()
For r = 2 To Selection.Count Step 2
   Selection(r).Cut Selection(r).Offset(, 1)
Next
End Sub

Удалить каждую вторую или третью строку

В конце таблицы делаем вспомогательный столбец. Заполняем чередующимися данными. Например, «о у о у о у» и т.д. Вносим значения в первые четыре ячейки.
Потом выделяем их. «Цепляем» за черный крестик в правом нижнем углу и копируем буквы до конца диапазона. Диапазон.
Устанавливаем «Фильтр». Отфильтровываем последний столбец по значению «у». Фильтрация.
Выделяем все что осталось после фильтрации и удаляем.
Убираем фильтр – останутся только ячейки с «о».

Выделить несколько удаленных строк

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

Как правильно скопировать формулу в Excel без сдвига?

Сделайте в формуле ссылки на ячейки абсолютными, для этого замените, например, C9 на $C$9. Тогда в формуле все останется без изменений после копирования.

Пример:
у вас формула:
=A1+B1
Вам нужно, чтобы при копировании формула менялась так:
=A1+C1
=A1+D1
И т.д.
Тогда вместо A1 сделайте $A$1

Вставка новой строки в тексте внутри ячейки в Excel

Чтобы начать новую строку текста или добавить интервал между строками или абзацами текста в ячейке листа, нажмите клавиши ALT + ВВОД, чтобы вставить разрыв строки.

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

Функция ОБЪЕДИНИТЬ- функция, которая умеет соединять текстовые строки. Она является усовершенствованной версией функции СЦЕП. В нее добавлена возможность задавать разделитель (1-ый аргумент) и указывать ей, как обрабатывать пустые ячейки (2-ой аргумент).

Например, формула
=ОБЪЕДИНИТЬ(", ";ИСТИНА;A2:A10)
соединит ячейки диапазона A2:A10, проставив после каждой запятую и пробел, но при этом пустые ячейки будут пропущены. Если заменить ИСТИНА на ЛОЖЬ, то пустые ячейки также буду учтены и отделены от других частей текста указанным в первом аргументе разделителем.

Разумеется, в качестве ячеек для склеивания можно задавать диапазоны, одиночные ячейки, текстовые строки.
Задаем новый столбец или строку
Вставить формулу (Текстовые)
Задать разделитель (пробел, точка, запятая, точка с запятой и тд
Пустые строки по умолчанию (пропускает пустые строки
Текст – задать диапазон+ текст 2 3 – можно много добавить диапазонов
Копируем и Вставить по столбцу или строке дальше

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

Каждую строку Эксель копирует из Ворда в одну ячейку, а нужно чтобы разделял по ячейкам
1 трали-вали 12131 12315465 1564654 465465456 фвыафвыафыва
2 чики-пуки 12121 12121123 1321212 454545447 выфвфывыфвфв
3 галина-бланка 11511 11121545 1156562 454556646 васывывафысыв
4 буль-буль 14545 15466555 1155458 455555555 вавыаыввмпмм
Выделить столбец - Данные - Текст по столбцам - С разделителями - Далее - Пробел - Готово

Вставить скопированные ячейки без замены содержимого

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

При вставке таблицы Word в Excel строка разбивается на несколько строк

При копировании из Ворда и вставке в Эксель, ячейка таблицы Ворда, где несколько строк вставляется в Эксель разные ячейки друг над другом, как сделать чтобы таблица эксель имела ввид как и ворде?
Внутри каждой ячейки таблицы в Word находятся две строки, разделенные нажатием Enter (если бы Enter при создании таблицы не нажимали, то и проблемы не было). Потому при вставке этой таблицы в Excel создается две ячейки Excel на одну ячейку Word. А надо - одна ячейка Excel на одну ячейку Word.
Можно ли что-то придумать, чтобы при вставке одна ячейка Word помещалассь в одну ячейку Excel, а не в две? 

1. Можно использовать стандартный приём замены концов абзаца.
в Word перед копированием выделить таблицу, заменить ^p (абзац) на какой-нибудь неиспользуемый символ, например #. Скопировать, вернуть обратно.
В Excel вставить, заменить # на Ctrl+J (так вводится символ перевода строки в ячейке, он не отображается в поле ввода).
(В поле "заменить" - набрать экселовский конец абзаца , то есть набрать alt-0010 (на цифровой клавиатуре)
нажать "Заменить всё".)

Массовое создание гиперссылок

Есть адреса типа www.youtube.com
Нужно их массово сделать гиперссылкой  https://www.youtube.com/

1.) Если вы не хотите делать макрос и до тех пор, пока не возражаете против дополнительного столбца, просто создайте новый столбец рядом с столбцом URL-адресов.
В новом типе столбца в формуле =HYPERLINK(A1) (заменяя A1 любой интересующей вас ячейкой). Затем скопируйте формулу до остальных 200 записей.
Исходный столбец можно спрятать или скопировать конечный столбец в Ворд и назад в Эксель

2) Как преобразовать столбец текстовых URL в активные гиперссылки в Excel?
Создайте макрос:
В меню "Сервис" в Microsoft Excel выберите "Макро" и нажмите "Редактор Visual Basic". В меню "Вставка" выберите "Модуль". Скопируйте и вставьте этот код в окно кода модуля. Он автоматически назовет себя HyperAdd.

Sub HyperAdd()
   'Converts each text hyperlink selected into a working hyperlink
   For Each xCell In Selection
       ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
   Next xCell
End Sub

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

ПРИМЕЧАНИЕ:
Не выбирайте весь столбец. Выберите только ячейки, которые вы хотите изменить, на интерактивные ссылки, иначе вы окажетесь в бесконечном цикле и перезапустите Excel

Перенос таблицы из Word – разрыв строки - конец абзаца

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

Поможет сочетание Ctrl+J - именно оно является альтернативой Alt+Enter в диалоговых окнах или полях ввода Excel:
Обратите внимание, что после того, как вы поставите мигающий курсор в поле и нажмёте Ctrl+J - в самом поле ничего не появится. Не пугайтесь - это нормально, символ-то невидимый :)
Нюанс: после выполнения замены введённый с помощью Ctrl+J невидимый символ остаётся в поле Найти и может помешать в дальнейшем - не забудьте его удалить, установив курсор в это поле и несколько раз (для надёжности) нажав на клавиши Delete и Backspace.

Итак, замены в Word сделали, опять же на #, переносим Excel. И в другую ячейку вводим формулу
Перевод / Translate
=ПОДСТАВИТЬ(A1;"#";СИМВОЛ(10))
Не забываем отметить в формате "Перенос текста".
Fairuza, большое спасибо. После очищаю от формул, и все замечательно.
В Word надо заменить все разрывы срок на что-либо, например # (или что Вам нравится). Выделить - Копировать - Вставить в Excel.
В Excel - выделить вставленную таблицу - Найти # - Заменить - вот тут набираете Alt 0010 (на цифровой панели) будет выглядеть как будто пустая, но не обращайте внимания – Заменить
Внутри каждой ячейки таблицы в Word находятся две строки, разделенные Разрыв строки. Потому при вставке этой таблицы в Excel создается две ячейки

Можно использовать стандартный приём замены концов абзаца.
1. В таблице Word заменить концы абзацев на неиспользуемый символ. Например на #
2. Затем copy-paste всю таблицу в Excel
3. Таблица осталась выделенной
Открыть диалоговое окно "найти и заменить".
В поле "найти" - #
В поле "заменить" – Ctrl+J (так вводится символ перевода строки в ячейке, он не отображается в поле ввода).
Или конец абзаца , то есть набрать alt-0010 (на цифровой клавиатуре)
нажать "Заменить всё".

Перенос текста на новую строку в ячейке

1) Это очень частая задача и решается она очень просто — для переноса текста на новую строку внутри одной ячейки Excel необходимо нажать ALT+ENTER (зажимаете клавишу ALT, затем не отпуская ее, нажимаете клавишу ENTER)
2) Используйте формат ячейки. Открыть его проще всего через командную строку или нажав Ctrl +1 ( 1 нажмите в верхнем ряду, на цифровой клавиатуре не сработает)
в появившемся окне на вкладке ВЫРАВНИВАНИЕ установите галочку ПЕРЕНОСИТЬ ПО СЛОВАМ
3) Как вариант, нажмите кнопку ПЕРЕНЕСТИ ТЕКСТ на вкладке ГЛАВНАЯ в самой программе

Как вытащить/извлечь гиперссылку из Excel

Макрос для получения ссылок из текста гиперссылок
1.Открыть документ Excel.
2. Выделите столбик с гиперссылками
2. Перейти в режим VBA (Alt+F11)
3. Вставить новый модуль (Insert > Module)
4. Скопировать код (ниже) и вставить в открывшееся окно

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

5. Нажать F5, чтобы запустить макрос
6. Выйти из VBA (Alt+Q)
После это в столбике справа должны появится отдельные ссылки.

Сделать неактивную ссылку активной для большого диапазона

Создаёте рядом новую колонку. заходите в верхнюю строку и применяете к ней Формула: Ссылки и массивы - Гиперссылка.
Далее в строку 1 ставите значение первой ячейки столбца неактивных ссылок, во вторую строку ставите ее же. нажимаете Ок.
Далее наводите на нижний правый уголок первой ячейки нового столбца, т.е. той, к которой всё это применяли и протягиваете до конца нужного диапазона
Столбец с псевдоссылками скрываем

Сравнение данных в столбцах

Возле первого пишете формулу =ПОИСКПОЗ(A1;$C$1:$C$10;0) и тянете вниз.
Возле второго пишете формулу =ПОИСКПОЗ(C1;$A$1:$A$10;0) и тянете вниз.
Рядом с теми ячейками, которые есть только в одном столбце, получите ошибку #Н/Д
В примере предполагается, что сравниваемые столбцы это A1:A10 и C1:C10. Для своих данных укажите соответствующие адреса ячеек

Вставить символ переноса строки

Если нам нужно избавиться от переносов, то первое, что обычно приходит в голову - это классическая техника "найти и заменить". Выделяем текст и затем вызываем окно замены сочетанием клавиш Ctrl+H или через Главная - Найти и выделить - Заменить (Home - Find&Select - Replace). Одна неувязка - не очень понятно, как ввести в верхнее поле Найти (Find what) наш невидимый символ переноса строки. Alt+Enter тут, к сожалению, уже не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получается.
Поможет сочетание Ctrl+J - именно оно является альтернативой Alt+Enter в диалоговых окнах или полях ввода Excel:

Вставка символа перенос строки
1. Ctrl+J
2. Alt+010

Код (символ) переноса строки - 10
Следовательно перенос строки в Excel в виде функции будет выглядеть вот так СИМВОЛ(10)
=ПОДСТАВИТЬ(A1;СИМВОЛ(10);" ")
A1 - это наш текст с переносом строки;
СИМВОЛ(10) - это перенос строки (мы рассматривали это чуть выше в данной статье);
" " - это пробел, так как мы меняем перенос строки на пробел
Если нужно проделать обратную операцию - поменять пробел на знак (символ) переноса, то функция будет выглядеть соответственно:

Код:
=ПОДСТАВИТЬ(A1;" ";СИМВОЛ(10))

Есть ли сочетание клавиш, с помощью которого можно вставить в диалога замены символ с кодом 160? Типа как Ctrl+J для переноса строки.
Alt+0160
Зажимаете Alt, затем на цифровой клавиатуре поочередно вводите нужные цифры: 0-1-6-0
Это коды ASCII. Например, Ctrl+J можно заменить на Alt+0010
Нужно в поле "Заменить на:" ввести 010 с нажатым ALTом

То же самое получается при нажатии Ctrl+j. В поле можно заметить мерцающую точку - это верхняя точка курсора, находящегося на "строке ниже".
Кстати, Alt+Enter можно вводить в текстовую константу в формуле. Формула выглядит странно, но работает, например вставить перенос строки после третьего символа:
КодВыделить код
=ЛЕВСИМВ(A1;3)&"
"&ПСТР(A1;4;99)
Это то же, что =ЛЕВСИМВ(A1;3)&СИМВОЛ(10)&ПСТР(A1;4;99)
=ЛЕВСИМВ(A1;3)&СИМВОЛ(10)&ПСТР(A1;4;99)
СЦЕПИТЬ
Функция ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции
ПСТР(текст;начальная_позиция;число_знаков)

Функции категории "Текстовые"

support.microsoft.com/ru-ru

БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
ЗНАЧЕН (Текст) – преобразует текст в число;
ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»

Функция СЖПРОБЕЛЫ
Удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать лишние пробелы.
Важно: Функция СЖПРОБЕЛЫ предназначена для удаления из текста знаков пробела 7-разрядного кода ASCII (значение 32). В наборе знаков Юникода существует дополнительный знак пробела, который называется знаком неразрывного пробела и имеет десятичное значение 160. Этот знак обычно используется на веб-страницах как сущность HTML  . Сама по себе функция СЖПРОБЕЛЫ не удаляет этот знак неразрывного пробела. Пример обрезки обоих пробелов из текста см. в десяти лучших способах очистки данных.
Синтаксис
СЖПРОБЕЛЫ(текст)
Текст   Обязательный. Текст, из которого удаляются пробе

СТРОЧН
Преобразует все буквы текста в строчные.

 

Еще в этой категории: Word: советы, уроки, хитрости »

selnew22

Инфохаб "Selection"
Ваш дружелюбный и опытный гид в мире информации 

 

              

Контакты

по вопросам сотрудничества и рекламы

in@infoselection.ru

по другим вопросам

of@infoselection.ru