Премиум каналы

Excel|Tricks and Features|Приёмы и возможности

Раскрытие и применение известных и малоизвестных возможностей Excel

Последние записи с канала

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

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

20.12.2021

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

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

18.12.2021

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

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

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

17.12.2021

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

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

16.12.2021

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

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

15.12.2021

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

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

14.12.2021

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

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

13.12.2021

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

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

11.12.2021

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

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

10.12.2021

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

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

09.12.2021

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

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

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

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

08.12.2021

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

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

07.12.2021

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

Для переноса текста в ячейке (ввода разрыва строки):
1. Дважды щелкните ячейку, в которую требуется ввести разрыв строки или выберите ячейку и нажмите клавишу F2.
2. В ячейке щелкните место, где нужно ввести разрыв строки, и нажмите ALT+ENTER (ВВОД).
Похожий приём есть и для других офисных программ Libro или Open Office, только следует использовать сочетание Ctrl + Enter. В версии Telegram для рабочего стола (Telegram Desktop) для ввода разрыва строки используется сочетание Ctrl + Enter. Однако в Telegram Web необходимо использовать Shift + Enter.

06.12.2021

​​Закрытие окон в программе 1С

Друзья, сегодня Воскресенье и, несмотря на то, что данное сообщение является из разряда «вне темы» (off topic), надеюсь, оно тоже будет вам интересным. Я, например, только полгода назад сделал для себя это открытие. Открытие заключается в том, как быстро закрывать окна в программе .
Нажмите правой клавишей мыши на вкладке выбранного окна, вызвав контекстное меню, содержащее варианты закрытия окон: «Закрыть все», «Закрыть все другие» и «Закрыть все справа». С ужасом вспоминаю как я закрывал в своё время 100500 окон по одному. :)))))))

05.12.2021

​​Преобразование чисел из текстового формата в числовой

Зачастую, при выгрузке данных из , например, на основе которых будет формироваться отчёт о движении денежных средств, мы можем наблюдать Зеленые треугольники в ячейках с числами. Эти зелёные треугольники обозначают ячейки с числами, хранящимися как текст. Числа, хранящиеся как текст, могут приводить к непредвиденным, вернее к никаким результатам.
Варианты преобразования, которые приходилось встречать – это:
1. Заходить в строке формул в каждую ячейку и нажимать клавишу Ввод (Enter);
2. Выделять ячейки и нажимать на Кнопку предупреждения об ошибке в Excel, затем выбирать вариант преобразования. И так для каждой ячейки.
Оба вышеуказанных метода являются непродуктивными и их нужно забыть. Поэтому, нам необходим Мастер распределения текста по столбцам.
1.Выделите столбец с текстом, который вы хотите преобразовать.
2.Перейдите на вкладку Данные нажмите кнопку Текст по столбцам в разделе Работа с данными.
3.В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.
4.Уберите выбор для всех символов-разделителей.
5.Нажмите кнопку Далее и проследите, чтобы был выбран Общий формат.
6.Поле Поместить в не меняйте.
7.Нажмите кнопку Готово и зелёные треугольники исчезнут.
При необходимости задайте необходимый числовой формат вашим преобразованным данным.

04.12.2021

​​Как зафиксировать ссылку на ячейку в формуле

Если Вы уже давно научились фиксировать ячейки в формулах Excel, но у вас не было времени разобраться с вопросом что и в каком случае фиксируется, то вот ответ.
В программе числами обозначаются строки, а буквами латинского алфавита столбцы, следовательно,
A1 – незафиксированная ячейка (относительная ссылка);
$A1 – зафиксирован столбец, так как знак $ стоит перед буквой (смешанная ссылка);
A$1 – зафиксирована строка, так как знак $ стоит перед числом (смешанная ссылка);
$A$1 – зафиксированы и столбец, и строка (абсолютная ссылка).
Знак $ можно проставлять вручную, а можно использовать клавишу F4. Если сразу при вводе ссылки вы не воспользовались клавишей F4, то необходимо щёлкнуть в любом месте ссылку на ячейку, которую нужно изменить (в самой ячейке её содержащую при разрешённом редактировании или в строке формул) и переключать виды ссылок.
В прилагаемом ролике имеется таблица 5 х 5, затем формируются новые таблицы путём указания ссылки на верхнюю левую ячейку исходной таблицы с вышеуказанными вариациями вида ссылки.

04.12.2021

​​Разделение текста по столбам

1.Выделите ячейку или столбец с текстом, который вы хотите разделить.
2.Перейдите на вкладку Данные нажмите кнопку Текст по столбцам в разделе Работа с данными.
3.В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.
4.Выберите разделители для своих данных. Например, запятую и/или пробел. Данные можно предварительно просмотреть в окне Образец разбора данных.
5.Нажмите кнопку Далее.
6.В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.
7.Нажмите кнопку Готово.

02.12.2021

​​Перевод чисел в числовой формат с разделителями

Нажатие сочетания клавиш Ctrl + Shift + 1 позволяет быстро применить формат числа с 2 знаками после запятой, разделителем между разрядами и традиционным форматом отрицательных чисел: 574 587 471,82.

02.12.2021

​​Найти и заменить неразрывный пробел в числах из 1С

При копировании (именно копировании, а не выгрузке) отчётов из 1С на лист Excel числа копируются с пробелами между разрядами, типа 172 564 230,34. С подобными числами невозможно производить арифметических действий. Решением вопроса является применение инструмента «Найти и заменить». Щелкните Главная -> Найти и выделить -> Заменить или сочетание клавиш Ctrl + H. На экране появится диалоговое окно «Найти и заменить» с выбранной закладкой «Заменить».ОЧЕНЬ ВАЖНО: так как числа выгружаются не просто с пробелом, а с неразрывным пробелом, то необходимо скопировать искомый «пробел» прямо из ячейки и вставить его в строку «Найти», а строку «Заменить на:» оставить пустой. Выделить необходимый диапазон и нажать кнопку «Заменить всё».

02.12.2021

​​Редактирование формул и клавиша F2

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

02.12.2021
Отзывы: - 0
Поделитесь вашим мнением! Оставьте отзыв:

Похожие

Избранное