​​Скрытие пустых строк по фильтру

Иногда приходится сталкиваться с вопросом скрытия строк таблицы в которых нет значений. При этом фильтр по какому-либо имеющемуся в таблице столбцу зачастую не подходит, так как данные по строке могут оказаться заполненными не везде, и фильтрация не даст должного результата. Также не подходит скрытие строк с помощью инструмента «Группа и структура», так как его настройка может не совпадать с тем, что хочет пользователь. Каждый раз скрывать пустые строки вручную перед печатью и раскрывать во время работы также не лучший вариант.
Выходом из этой ситуации может оказаться суммирование всех значений в служебном столбце, находящемся за границей области печати. При этом, если хотя бы в одной ячейке есть значение, сумма в служебном столбце будет либо больше, либо меньше ноля. Затем нужно включить фильтр по заданному столбцу, и отфильтровать данные, выбрав в числовых фильтрах условие «не равно 0».
Примечание: если у вас в одной строке содержатся только два одинаковых по модулю значения, например плюс пять и минус пять, то результатом работы функции =СУММ() будет ноль и эта строка при фильтрации будет скрыта, хотя в ней содержатся значения. Поэтому, в качестве небольшой модификации к вышесказанному, можно использовать для служебного столбца, к примеру, функцию =МАКС().

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

Если у вас есть книга Excel, содержащая итоговую таблицу и несколько таблиц на других листах, которые сформированы единообразно, то вместо формул сложения ячеек с каждого листа типа:
='Первый квартал'!B3+'Второй квартал'!B3+'Третий квартал'!B3+'Четвёртый квартал'!B3 или
=СУММ('Первый квартал'!B3;'Второй квартал'!B3;'Третий квартал'!B3;'Четвёртый квартал'!B3)
можно воспользоваться формулой: =СУММ('Первый квартал:Четвёртый квартал'!B3).

Для создания такой формулы выполните приведенные ниже действия:
1. Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ(
2. Щелкните на вкладке первого листа (в нашем случае это Первый квартал)
3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Четвёртый квартал) и выберите ячейку B3
4. Нажмите Enter, и формула будет введена в ячейку

Можно выбор ячейки осуществлять до нажатия клавиши Shift. Тогда действия 2 и 3 будут выглядеть так:
2. Щелкните на вкладке первого листа (в нашем случае это Первый квартал) и выберите ячейку B3
3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Четвёртый квартал)

​​Перенос части таблицы с относительными ссылками

Если вам необходимо перенести часть таблицы с относительными ссылками в другое место, то необходимо воспользоваться инструментом «Найти и заменить». Найти необходимо знак равно «=», и заменить его на то, что точно не встретится на вашем листе Excel, например, на символы «ййй», как в приведённом примере. То есть, вместо формулы: «=ВПР(E6;B$6:C$16;2;ЛОЖЬ)» мы получаем «йййВПР(E6;B$6:C$16;2;ЛОЖЬ)». После этого данные ячеек можно смело переносить в любое место листа. Затем нужно провести обратную замену «ййй» на знак равно «=» и в исходном блоке, и в том, который был перенесён.
БУДЬТЕ ВНИМАТЕЛЬНЫ к ячейкам, содержащим итоги. Если вы перенесёте «йййСУММ(F6:F10)» в столбец «J», то при возврате к исходной формуле вместо ожидаемого результата «=СУММ(J6:J10)» вы получите именно «СУММ(F6:F10)».

Форматирование шрифта

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

​​Суммарные итоги

Для расчёта суммарных итогов одновременно по строкам и столбцам таблицы необходимо выделить ячейки с данными и те ячейки, где будет располагаться результат. Затем нажать на значок инструмента «Автосумма», либо воспользоваться сочетанием клавиш «Alt + =». При использовании инструмента «Автосумма» можно считать не только сумму, но и среднее значение, минимум, максимум или количество.

​​Открытие второго окна с текущим документом

Вы можете открыть второе окно с текущим документом для одновременной работы с его разными частями. Для этого перейдите в меню «Вид» и нажмите кнопку «Новое окно» (в группе «Окно»). Откроется второе окно этого же документа. При этом, отражение имени файла в разных окнах будет выглядеть как «Документ:1» и «Документ:2», где 1 и 2 – это номера копий. Вы можете открыть третью, четвёртую, пятую и так далее копию окна. Вопрос в необходимости такого количества.
Обратите, пожалуйста, внимание на следующее: если у вас в оригинальном файле закреплены области и настроен определённый масштаб листа, то новые окна (копии) будут открываться свободными от этих настроек. Для того, чтобы в оригинальном файле сохранить эти настройки, при закрытии копий следите за тем, чтобы эта была не первая. Это окно оставляем «Документ:1», а эти «Документ:2», «Документ:3», «Документ:4» и так далее закрываем.

​​Скрытый и очень скрытый лист

Если вы хотите скрыть свой лист из книги Excel настолько, чтобы он не был виден при вызове контекстного меню и строка «Показать…» была неактивной или активной только для листов, скрытых обычным способом, вам необходимо перейти в редактор Visual Basic (на панели вкладок выбрать Разработчик -> Код -> Visual Basic или воспользоваться комбинацией клавиш Alt + F11). Далее выбрать лист и ниже в окне свойств (Properties) для свойства Visible выбирать атрибут «2 - xlSheetVeryHidden» (очень скрытый). Если окно свойств изначально не отображается, то в панели вкладок Visual Basic выбирать View -> Properties Window и блок появится (также окно появится при нажатии F4).
Лист, скрытый подобным способом можно отобразить только, если снова зайти в редактор VBA и свойству Visible присвоить атрибут «0 - xlSheetHidden» (скрытый), чтобы далее отобразить его стандартным способом, либо сразу выставить «-1 - xlSheetVisible» (видимый), чтобы он отобразился в книге сразу.

​​Извлечение текста слева и справа от / до определённого символа

Если у вас есть текст, где разделителем между левой и правой частью является дефис, то можно легко отделить одну часть от другой. Например, слева у вас название контрагента, а справа название договора, а вам нужно составить списки отдельно контрагентов и отдельно договоров.
Готовые формулы для выделения левой и правой частей для ячейки A11 следующие:
В ячейке "A11" забираем только ВСЕ ЛЕВЫЕ СИМВОЛЫ. Минус 2 (два) - это чтобы не захватить пробел и дефис.
=ЛЕВСИМВ(A11;НАЙТИ("-";A11)-2)
В ячейке "A11" забираем только ВСЕ ПРАВЫЕ СИМВОЛЫ. Минус 1 (один) - это чтобы не захватить пробел.
=ПРАВСИМВ(A11;ДЛСТР(A11)-НАЙТИ("-";A11)-1)

​​Диалоговое окно «Формат ячеек»

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

​​Сообщение об ошибке: «Невозможно вставить новые ячейки»

Если при попытке вставить новые столбцы или строки вы видите на экране следующее сообщение: «Microsoft Excel не удаётся вставить новые ячейки, так как это приведёт к сдвигу непустых ячеек за пределы листа… », необходимо сделать следующие действия (на примере столбцов):
1. Выделить полностью столбец, находящийся за пределами нужных вам данных.
2. Нажать сочетание клавиш Ctrl + Shift + курсор вправо, чтобы выделить все столбцы до конца книги.
3. Нажать сочетание клавиш Ctrl + минус, чтобы удалить столбцы.

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

​​Быстрая вставка текущей даты и времени

Если вы используете несколько вариантов расчётов, например, добавляя в книгу новые листы и хотите зафиксировать когда была создана очередная версия, то есть быстрая возможность вставки даты и времени, чтобы не вводить их вручную.
Вставка даты: сочетание клавиш Ctrl + Shift + 4
Вставка времени: сочетание клавиш Ctrl + Shift + 6

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

Если Вам приелся бледно-жёлтый цвет примечания, то вы можете придать ему немного жизни, изменив формат. Для этого вызовите контекстное меню, щёлкнув правой клавишей на границе примечания (именно на границе) и выберите «Формат примечания…». Разлейте немного красок, изменив цвет и толщину границы, заливку и способы заливки фона примечания, в том числе, добавив рисунок. Если необходимо изменить только формат шрифта, можно сделать это, выделив шрифт и вызвав контекстное меню. При этом доступной к изменению останется только единственная закладка «Шрифт».