Kitobni o'qish: «Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)»

Ренат Шагабутдинов
Shrift:

Рецензент Ксения Соколова

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

© Ренат Шагабутдинов, 2023

© Оформление. ООО «МИФ», 2025

* * *

Введение

Любая достаточно развитая технология неотличима от магии.

Третий закон Артура Кларка

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

Если что-то делается долго, вручную, механически в табличном редакторе, то, скорее всего, есть способ делать это быстрее. Простая проверка – представьте, что данных в 100 или 1000 раз больше. Будет ли работать ваш метод или понадобится в 100 раз больше времени, что сделает выполнение задачи невозможным на практике?

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

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

Не удивляйтесь: в примерах вы встретите песика Штруделя и кота Лемура. Это мои соавторы по курсу «Магия Excel» в МИФе. Штрудель – питомец руководителя команды МИФ.Курсов, а Лемур живет у меня (и ведет вместе со мной телеграм-канал «Магия Excel»). Мы решили, что таблицы – это слишком скучно, и наши маленькие друзья могут немного упростить обучение своими примерами (и просто присутствием). С курсом получилось:) Поэтому и в книгу я забрал ряд примеров.

Для кого эта книга?

Для всех, кто работает с таблицами – в любой версии Excel или в Google Spreadsheets (книга в целом – примеры, скриншоты – посвящена именно Excel, но мы будем проговаривать, в чем особенности и ключевые отличия Google Таблиц, они выделены курсивными врезками).

Большую таблицу с подробным сравнением Google Spreadsheets и Excel вы найдете по ссылке ниже.

Сравнение Excel и Google Таблиц

https://mif.to/0Irxx


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

Как читать?

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

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

Вам интересны новые возможности Excel (версии 2021 и 2024 и функции, появившиеся в 2022–2024 годах): вам – в главы про динамические массивы, функции LET и LAMBDA, раздел про совместную работу.

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

Идеальная ситуация для работы: у вас подписка Microsoft 365 или Excel 2021/2024. Версии 2016 и 2019 подойдут, но с динамическими массивами и функцией LAMBDA попрактиковаться не получится. Впрочем, вы можете опробовать данные функции в Google Таблицах – этот редактор доступен бесплатно для частных аккаунтов Google (Gmail), и в 2022 году в нем появилась LAMBDA, а функции SORT, FILTER и UNIQUE, появившиеся в Excel 2021, были уже много лет.

Версии Excel 2007, 2010 и 2013 также подойдут для работы с большинством инструментов, хотя это и менее желательный вариант.

2003 – увы, нет.

Excel Online вряд ли подойдет для полноценной работы, так как в нем доступны далеко не все инструменты, но если это единственный возможный для вас вариант (а он доступен бесплатно), то попрактиковаться с формулами, сводными таблицами и диаграммами там получится. Если вас интересует бесплатный вариант, то лучше посмотрите в сторону Google Таблиц, где есть практически все новые функции Excel, такие как LAMBDA, LET, FILTER, SORT, UNIQUE, – с формулами вы точно сможете полноценно поработать там.

Где скачать файлы с примерами?

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


Файл с примером: Мгновенное заполнение. xlsx

Архив с файлами-примерами в Excel доступен по ссылке https://mif.to/yU2bzV


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

Пароль к архиву с примерами – пятое слово в заключении.

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

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

А также в книге есть несколько Google Таблиц с примерами, ссылки на каждую из них – непосредственно в тексте.

Чтобы поработать с примерами на своем Google Диске, сделайте копию таблицы (Файл → Создать копию).

Обратите внимание: если в электронной версии книги вам не будут видны какие-то мелкие детали на скриншотах, то вы сможете все рассмотреть и «потрогать руками» в файлах-примерах непосредственно в Excel / Google Таблицах.

Какие в книге есть обозначения?

Текст про Google Таблицы – в курсивных врезках.

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


Формулы и ссылки на ячейки/диапазоны.


Сочетания клавиш в Windows (на Mac).

Основы. Интерфейс. Форматирование

Интерфейс Excel и типы файлов. Версии Excel

Самые значимые изменения в Excel произошли уже довольно давно – между версиями 2003 и 2007. Значительно увеличилось количество строк и столбцов на листе (более миллиона вместо 65 тысяч строк; о точных ограничениях – чуть ниже), появилась лента инструментов с отдельными вкладками.

Так выглядела панель инструментов в версии 2003 и ранее:



А в новых версиях Excel есть лента с отдельными вкладками, на которых расположены инструменты и команды (лента выглядит по-разному в разных версиях, но структура с вкладками и группами команд на каждой из них одинакова). Это скриншот из версии Microsoft 365 (по подписке).



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

Конечно, чем новее версия, тем лучше, но революционные изменения случаются не с каждой из них. Таковыми можно считать:

• изменение интерфейса (лента), размеров файла (более миллиона строк вместо 65 тысяч) и новые расширения (типы) файлов (книг Excel) в версии Excel 2007;

• появление надстроек Power Pivot, Power Query для версии Excel 2010;

• появление динамических массивов и соответствующих новых функций в Excel 2021 и в рамках пакета Microsoft 365 по подписке, а также функции LAMBDA (в Microsoft 365 и Excel 2024, но не в Excel 2021), позволяющей создавать собственные функции без макросов.

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


Версии Excel

https://mif.to/cfC9b


Давайте посмотрим на основные элементы в окне Excel.



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

Чтобы выделять разные ячейки, можно:

• выделять ячейку мышкой;

• переходить от ячейки к ячейке с помощью клавиш со стрелками на клавиатуре;

• вводить адрес ячейки в поле «Имя» и нажимать Enter, чтобы переместиться в нее;

• нажимать F5 и в появившемся окне «Переход» вводить адрес ячейки.



Для выделения диапазона (группы из нескольких ячеек):

• выделите его мышкой;

• выделите первую (левую верхнюю) ячейку диапазона, зажмите Shift и щелкните мышкой на последнюю (правую нижнюю) ячейку;

• нажмите F8 (Fn + F8) для перехода в режим расширения; теперь достаточно нажимать на стрелки на клавиатуре – и выделенный диапазон будет расширяться в соответствующем направлении;

• чтобы выделять строки или столбцы целиком, щелкните на их заголовки (номера);

• для выделения несмежных ячеек или диапазонов (то есть не одного прямоугольного диапазона, а нескольких, расположенных отдельно) зажмите Ctrl и выделите ячейки или диапазоны либо нажмите Shift + F8 (Fn + ⇑ + F8) и выделите несмежные диапазоны мышкой без зажатия Ctrl;

• Ctrl + Shift + стрелки (^ + ⇑ + стрелки) – выделение диапазона до последней заполненной ячейки в направлении стрелки; например, если выделить первую ячейку в столбце и нажать Ctrl + Shift + ↓ (^ + ⇑ + ↓), то будут выделены все ячейки в этом столбце до последней заполненной включительно.


А для перемещения по листу пригодится следующее.

• Прокручивание колеса мышки (с зажатым Ctrl колесико будет менять масштаб, а без – прокручивать окно). В Excel 2021 / Microsoft 365 появилась возможность прокручивать лист горизонтально: зажмите Ctrl + Shift и прокручивайте колесико мышки. Конечно, если у вашей мышки есть второе колесо прокрутки, удобнее всего использовать его для этой цели.

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

• Перемещаться на экран вверх и вниз с помощью клавиш PgUp и PgDn.

• Возвращаться к активной ячейке с помощью сочетания Ctrl + Пробел.

• Прокручивать экран по строкам можно с помощью стрелок на клавиатуре, но только при включенном индикаторе Scroll Lock. Иначе вы будете переходить с ячейки на ячейку, нажимая стрелки.

• В соседние ячейки можно попасть с помощью стрелок на клавиатуре и клавиш Tab (в ячейку справа от активной), Shift + Tab (в ячейку слева), Enter (в ячейку снизу) и Shift + Enter (в ячейку сверху).

• Ctrl + стрелки позволяют перемещаться до конца строки/столбца.

• Чтобы попасть в конец активной области (той области рабочего листа, в которой есть какие-либо данные), нажмите Ctrl + End. Вы попадете в ячейку, которая находится на пересечении последней строки и последнего столбца, содержащих данные.

Интерфейс Google Таблиц

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



Меню можно открывать мышкой или с помощью сочетаний клавиш. Все меню открываются сочетанием клавиши Alt и одной из букв – как правило, первой буквы названия меню на английском. Например, Alt + D – это меню «Данные» (Data).

Все сочетания клавиш (и не только для меню) можно посмотреть, нажав Ctrl + /.

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



Настройка ленты и панели быстрого доступа Excel

СКРЫТИЕ И НАСТРОЙКА ЛЕНТЫ

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

Лента в скрытом виде выглядит так: видны только вкладки.



Ленту инструментов в Excel можно настраивать: создавать собственные вкладки, добавлять команды на существующие, скрывать вкладки.

Для настройки ленты нужно зайти в «Параметры Excel» и выбрать там соответствующий раздел «Настроить ленту» (Customize Ribbon).



Чтобы скрыть некоторые вкладки, снимите флажки справа.



Раскрывайте вкладки и группы, чтобы посмотреть их состав, а также добавить новые команды, – для этого достаточно выбрать команду слева в списке и нажать «Добавить > >» (Add > >).

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



Далее эту группу можно переименовать (Rename) и выбрать слева список «Основные вкладки» (Main Tabs), он подойдет для добавления команд с этих самых вкладок – как раз как сводные таблицы в нашем случае. Если же вы хотите добавить какую-то команду, которой там нет, выберите «Все команды» (All Commands). А затем – нужную команду (в нашем случае вкладка «Вставка», «Сводная таблица»), после чего нажмите «Добавить».



Теперь на вкладке «Данные» у вас будет кнопка «Сводная таблица» (на вкладке «Вставка», где она находится по умолчанию, она не исчезнет).



Можно создавать и собственные вкладки, для этого нажмите «Создать вкладку». Преимущества своей вкладки:

• можно собрать любимые инструменты, используемые чаще всего, в одном месте;

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

Пример. Так выглядит «Проверка данных» на своем обычном месте на вкладке «Данные»:



Так она будет выглядеть, если создать новую вкладку и добавить ее туда:



ПАНЕЛЬ БЫСТРОГО ДОСТУПА

Панель быстрого доступа (Quick Access Toolbar, QAT) – простейший инструмент для настройки интерфейса под себя. Туда можно добавить любую команду – как из ленты, так и из списка вообще всех команд и инструментов Excel.



Чтобы добавить любую команду из ленты, просто щелкните правой кнопкой мыши и выберите «Добавить на панель быстрого доступа» (Add to Quick Access Toolbar).



Добавляем флажок «Сетка» на панель быстрого доступа.

Теперь «Сетка» будет доступна во всех книгах Excel всегда наверху – вне зависимости от того, на какой вкладке ленты вы находитесь (и от того, отображается ли лента).



Чтобы добавить команды, которых на ленте нет, зайдите в параметры Excel – «Панель быстрого доступа» либо на самой панели в выпадающем списке выберите «Другие команды…» (More Commands…).



Выбираем в выпадающем списке «Выбрать команды из» – Все команды» (Choose commands from – All Commands). Тут действительно все команды Excel, ряд из которых нигде больше не найдешь в принципе (например, такие опции, как проговаривание ячеек или мастер сводных таблиц и диаграмм из старых версий приложения).


Список длинный – вводите первую букву команды, чтобы быстрее найти необходимое


Например, вам часто нужно закреплять верхнюю строку. Эта команда вроде бы есть на ленте – в коллекции команд «Закрепить области» (Freeze Panes). Но добавить именно закрепление верхней строки на панель быстрого доступа не получится – только всю коллекцию.



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

В таком случае, когда команду из ленты добавить нельзя или ее там вовсе нет, нужно заходить в настройки панели быстрого доступа в параметрах Excel и там находить команду в списке всех команд или команд не на ленте (Commands Not in the Ribbon).



Теперь закреплять верхнюю строку можно будет одним кликом (или нажатием Alt, или /, или F10 с соответствующей положению кнопки цифрой).



НАСТРОЙКА ПАНЕЛИ БЫСТРОГО ДОСТУПА ДЛЯ ОТДЕЛЬНЫХ КНИГ

Если вдруг вы захотите создать отдельную конфигурацию панели быстрого доступа для конкретной книги Excel, а не приложения в целом, выберите книгу в списке «Настройка панели быстрого доступа», где по умолчанию указано «Для всех документов» (For all documents).



Кстати, панель быстрого доступа есть и в остальных приложениях MS Office. Вы можете добавить любимые инструменты на нее, например, в Power Point или Outlook.

В Google Таблицах аналога панели быстрого доступа, к сожалению, нет.

ДОСТУП К КОМАНДАМ НА ЛЕНТЕ И ПАНЕЛИ БЫСТРОГО ДОСТУПА С ПОМОЩЬЮ КЛАВИАТУРЫ

Чтобы с помощью клавиатуры перемещаться по ленте и панели быстрого доступа, нажмите Alt, или F10, или / (увы, эта опция работает только в Windows, но не на Mac). После этого у каждой вкладки появится буквенное обозначение, у инструмента на панели быстрого доступа – цифровое. Вводите указанные обозначения на клавиатуре: при выборе вкладки она будет открываться, и подписаны будут уже инструменты на ней.



Так как командам на панели быстрого доступа соответствуют цифры, получается, что добавление команды туда – это возможность создать собственное сочетание клавиш. Например, вы хотите, чтобы строку формул можно было скрывать быстро. Добавьте ее на панель быстрого доступа: правая кнопка мыши по флажку «Строка формул» на вкладке «Вид» (View → Formula Bar).

Теперь можно будет включить и отключить строку формул с помощью сочетания Alt + цифра (какая именно цифра – зависит от того, сколько у вас уже команд на панели, какой по порядку станет вновь добавленная).



В Microsoft 365 после 2020 года обновили алгоритм работы этого инструмента – теперь вы можете сразу после нажатия Alt нажимать на буквы, если помните их, не дожидаясь, пока они появятся на экране. Это удобно для тех команд, путь к которым вы уже запомнили.

ДОСТУП К КОМАНДАМ В ДИАЛОГОВЫХ ОКНАХ И МЕНЮ

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

Вот, например, контекстное меню, которое открывается по щелчку правой кнопкой мыши по ярлыку листа:


Чтобы выделить все листы, нужно нажать В

А вот диалоговое окно «Специальная вставка»:



У вас открыто диалоговое окно в Excel и есть несколько вкладок/разделов?

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

На скриншоте окно вставки гиперссылки (вызывается по сочетанию Ctrl + K).



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

Движение идет по кругу. То есть если вы на первой вкладке, Ctrl + Page Up откроет последнюю.



Ввод данных

Рассмотрим способы окончания ввода в ячейку. Закончить ввод данных/формулы в ячейку можно разными способами – смотря куда нужно попасть после.

• Ctrl + Enter (^ + Return) – вы останетесь в той же ячейке (это сочетание клавиш также позволяет ввести значения/формулы сразу в несколько выделенных ячеек);

• Enter (Return) – активной станет ячейка ниже;

• Tab – ячейка справа;

• Shift + Enter – ячейка сверху;

• Shift + Tab – ячейка слева.

Можно изменить то, куда вы будете попадать при нажатии Enter, в параметрах Excel: Параметры → Дополнительно → Переход к другой ячейке после нажатия клавиши ВВОД (Options → Advanced → After pressing Enter, move selection).



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



В Google Таблицах аналогично: можно завершить ввод и попасть в любую соседнюю ячейку:

Enter – вниз;

Shift + Enter – вверх;

Tab – вправо;

Shift + Tab – влево.

Если вы заполните несколько ячеек в одной строке через Tab и потом нажмете Enter, то переместитесь в начало следующей строки (под первый заголовок).

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

ИНСТРУМЕНТЫ EXCEL ДЛЯ БЫСТРОГО ВВОДА ДАННЫХ

Автозамена (AutoCorrect). Если вам часто нужно вводить какое-то словосочетание, адрес, имейл и так далее, придумайте для него короткое обозначение и добавьте в список автозамены в «Параметрах»: Параметры → Правописание → Параметры автозамены (Options → Proofing → AutoCorrect Options).



Обратите внимание, что список автозамены – один на все приложения Microsoft Office! Так что введенные вами варианты будут заменяться и, например, в Outlook.

Прогрессия (Series). Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:



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



После нажатия ОК получим все даты без выходных от 01.01.2023 до 30.12.2025 в столбец.



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



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



Результат:



Протягивание. Если вы уже задали какой-то паттерн (например, числа с шагом 10 или последние даты каждого очередного месяца), выделите несколько ячеек и далее протяните их мышкой за маркер в правом нижнем углу.



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



Это работает и с формулами – они скопируются до конца столбца (до последней строки с данными).

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

Еще можно использовать сочетание Ctrl + D для заполнения вниз. Но для этого придется сначала выделить все ячейки в столбце. Кстати, Ctrl + R – это заполнение вправо.

P. S. Google Таблицы сами предлагают протянуть формулу в таких ситуациях – просто можно нажать галочку или Ctrl + Enter. Двойной щелчок и Ctrl + D / Ctrl + R там тоже работают.



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



Раскрывающийся список (Drop-down List). Alt + ↓ (⌥ + ↓) или правая кнопка мыши и «Раскрывающийся список» (Pick from) в контекстном меню. И выбирайте из списка тех значений, что встречались в этом столбце ранее.



МГНОВЕННОЕ ЗАПОЛНЕНИЕ

Файл с примером: Мгновенное заполнение. xlsx

Мгновенное заполнение (Flash Fill) – один из самых простых и полезных инструментов Excel. Появилось мгновенное заполнение в Excel 2013. Этот инструмент позволяет преобразовать данные или извлекать какой-то фрагмент: достаточно задать один-два образца того, что надо получить из исходного столбца (или нескольких столбцов), – и мгновенное заполнение заполнит весь столбец значениями исходя из заданного вами паттерна (шаблона).

Вот примеры задач, которые может решить мгновенное заполнение:

• извлечь из текста фрагмент (например, имя из Ф. И. О.);

• переделать текст в другой вид, в том числе дополняя теми символами, которых нет в исходном тексте (например, вместо Ф. И. О. – инициалы с точками плюс полная фамилия);

• получить текст из нескольких столбцов (например, если у вас есть столбцы с датой и фамилией, а вам надо «склеить» это в одну фразу «День рождения такого-то человека такого-то числа такого-то месяца»);

• поменять регистр текста.

Вводим одно или несколько значений, которые хотим получить на основе исходных данных, и либо ждем, что Excel сам предложит заполнить, и нажимаем Enter (или щелкаем мышкой на серые значения), либо нажимаем Ctrl + E (^ + E).

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



После нажатия Ctrl + E данные будут заполнены.



В следующем случае ввели два значения, начали вводить третье – Excel предложил заполнить столбец (серые значения). Остается нажать Enter или щелкнуть по любой из этих ячеек, чтобы столбец заполнился.



Также можно вызвать мгновенное заполнение на ленте.



Есть несколько нюансов.

1. Мгновенное заполнение анализирует всю строку, все смежные столбцы (то есть нужно вводить данные в любом соседнем столбце с данными, без перерыва в виде пустого столбца). И это значит, что можно обрабатывать (извлекать и объединять) данные из нескольких столбцов. В этом примере мы формируем фразу, в которой есть переменная часть – название книги и продажи.



После нажатия Ctrl + E все строки будут корректно заполнены по этому образцу:



2. Не во всех случаях Excel сможет распознать ваш шаблон. Иногда приходится задавать несколько примеров (не один, а два или три). Иногда вообще не получается применить мгновенное заполнение.

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



…то в некоторых местах будут извлечены лишние данные:



А где-то будет вес в граммах – это не ошибка мгновенного заполнения, но нужно это учесть и конвертировать его в килограммы (это можно сделать с помощью функции ПРЕОБР / CONVERT, о которой мы поговорим ниже, или банального деления, которого хватит в случае с килограммами и граммами; ну а чтобы определять, нужно ли каждое конкретное число преобразовывать из граммов, пригодится функция ЕСЛИ / IF, о которой тоже поговорим ниже).



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

Рабочие листы Excel

Рабочая книга (файл) Excel состоит из листов – как минимум один лист в книге точно должен быть.

Бывают рабочие листы (состоящие из ячеек; как правило, в основном в Excel и Google Таблицах пользователи работают с ними) и листы диаграмм (на таких листах есть только одна диаграмма и никаких ячеек; самый простой способ создать такую диаграмму – нажать F11 (Fn + F11), выделив данные для ее построения). «Обычная» и привычная нам диаграмма называется внедренной и размещается поверх ячеек на обычном листе, такую можно создать с помощью сочетания клавиш Alt + F1 (Fn + ⌥ + F11) или вкладки «Вставка» (Insert) на ленте.

Ярлыки листов находятся в нижней части окна.


Можно щелкать на ярлык листа, чтобы перейти к нему, можно также использовать сочетания клавиш Ctrl + PgUp (Fn + ^ + ↑) и Ctrl + PgDn (Fn + ^ + ↓) для перехода от листа к листу.

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

Здесь же можно удалить лист – это удастся сделать, если он является не единственным видимым во всей книге (видимым, то есть не скрытым; скрыть лист можно здесь же в контекстном меню).

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



УДАЛЕНИЕ И ВСТАВКА СТРОК И СТОЛБЦОВ

Чтобы вставить столбцы или строки, щелкните правой кнопкой мыши и нажмите «Вставить» (Insert).

Также на панели на вкладке «Главная» (Home) есть выпадающий список для вставки новых строк/столбцов.



В Excel вставка и удаление строк/столбцов не изменит их общее количество на листе (напомним, 1 048 576 строк и 16 384 столбца, начиная с Excel 2007).

Данные будут просто смещаться. Ну а если у вас заполнены все строки (или столбцы), появится сообщение об ошибке при попытке вставить новую строку:

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

В Google Таблицах можно менять количество строк и столбцов на листе. Формальное ограничение – в количестве ячеек, их не может быть более 10 миллионов (за несколько лет это ограничение поменялось – сначала с 2 до 5, а затем до 10 миллионов, – так что вполне вероятно, что увеличится и далее в будущем). А сколько при этом окажется листов, строк и столбцов на них – неважно.



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


Удаление строк/столбцов

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



Также можно удалять строки с помощью выпадающего списка «Удалить» (Delete) на вкладке «Главная»:



И есть сочетание клавиш Ctrl + – (⌘ + —). Если у вас уже выделены строки или столбцы, они сразу будут удалены, если же выделены ячейки, то появится диалоговое окно, потому что в таком случае не очевидно, что именно нужно удалять, – вам нужно будет выбрать.




Удаление строк с пустыми значениями. Инструмент «Найти и выделить»

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



Сначала выделим эти пустые ячейки. Для этого выделим все ячейки в столбце «Сумма» и обратимся к инструменту «Найти и выделить» → «Выделить группу ячеек» (Find and Select → Go To Special).



В появившемся диалоговом окне выбираем, какую группу ячеек нужно выделить (в нашем случае это пустые ячейки – Blanks).



После нажатия ОК мы будем видеть такую картину:



Останется нажать Ctrl + – (⌘ + —) и выбрать команду «строку» (Entire row).




Как удалить пустые строки / строки с пустыми ячейками в Excel

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

Для этого понадобится инструмент «Найти и выделить» (на ленте на вкладке «Главная»). Выбираем там «Выделить группу ячеек», а в появившемся диалоговом окне – «Пустые ячейки» (Find & Select → Go To Special → Blanks).



После этого остается нажать Ctrl + — (это удаление ячеек/строк/столбцов). И выбрать «строку».

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

АВТОПОДБОР ШИРИНЫ СТОЛБЦА

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



Выделим все столбцы и щелкнем дважды по границе любого из них.



ОДНОВРЕМЕННОЕ РЕДАКТИРОВАНИЕ НЕСКОЛЬКИХ ЛИСТОВ

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

• если нужно выделить несколько отдельных листов – просто зажмите клавишу Ctrl и щелкайте на их ярлыки;

• если нужно выделить группу листов, идущих подряд, щелкните на ярлык первого, зажмите Shift и щелкните на ярлык последнего – будут сгруппированы все листы «от и до».

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



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

Разгруппировать листы можно в контекстном меню – достаточно щелкнуть правой кнопкой по любому из ярлыков и выбрать команду «Разгруппировать листы» (Ungroup Sheets).



Что такое формат и значение в Excel и Google Таблицах

У ячейки в Excel (и в Google Таблицах) есть значение и формат.

Значение – это то, что в ячейке содержится или возвращается формулой, введенной в эту ячейку: текст, числа, даты, время, формулы. И то, что может использоваться для последующих вычислений с помощью формул в других ячейках.

Формат – то, как это выглядит в таблице, как ячейка оформлена.

Если сравнить ячейку с контейнером, в котором что-то хранится, то можно сказать, что значение – это то, что в контейнере лежит, а форматирование – то, как он выглядит снаружи.

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

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



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

• Формат (то, как значение представлено) и само значение могут выглядеть по-разному. В ячейке может отображаться одно число, а на самом деле быть другое. Или отображаться целое число, а быть с дробной частью. Значение без всякого форматирования (или формула, если в ячейке она) всегда отображается в строке формул.



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

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

В ячейке или диапазоне можно удалить только данные (клавиша Delete), а можно только форматы или все вместе (кнопка «Очистить» / Clear на ленте во вкладке «Главная»).



В Google Таблицах можно очистить форматирование (но только стилевое, то есть заливку, выравнивание и прочие визуальные аспекты, но не форматирование числа) сочетанием клавиш Ctrl + \.

Переносить (копировать или вырезать и затем вставлять) тоже можно как форматы или значения отдельно, так и все вместе.

При обычной вставке (Ctrl + V) вставляются и форматы, и значения.

Чтобы вставить только значения или только форматы, используйте сочетание клавиш Ctrl +Alt + V (⌘ + ^ + V) и далее нажмите на клавиатуре букву «З» или «т» (или выберите соответствующий пункт в диалоговом окне):

→ З (Значения) – вставка только значений;

→ т (Форматы) – вставка только форматов.



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

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

Еще один вариант: правой кнопкой мыши потянуть за границы диапазона, а далее в контекстном меню выбрать «Копировать только значения» / «Копировать только форматы» (Copy Here as Values Only / Copy Here as Formats Only).

Также можно скопировать данные (Ctrl + C), а затем после копирования щелкнуть правой кнопкой мыши на ячейку для вставки и в контекстном меню выбрать нужный вариант.



Кроме того, можно вставить данные как обычно (Ctrl + V) и после этого нажать на появившийся смарт-тег «Параметры вставки» справа внизу.



Очень просто вставить только значения можно в Google Таблицах (а также только текст без форматирования в других облачных приложениях Google) – с помощью сочетания Ctrl + Shift + V. В Excel (и других приложениях Office – особенно полезно это будет в Word) Это сочетание было представлено в конце 2022 года и доступно подписчикам Microsoft 365.

Перенести форматирование с одной ячейки на другие можно, выделив ячейку-образец и нажав на кнопку «Формат по образцу» (Format Painter) на ленте инструментов.



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

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

Специальная вставка: несколько трюков

Файл с примерами: Специальная вставка. xlsx

Специальная вставка (Paste Special) нужна не только для того, чтобы вставлять значения или форматы (как мы обсуждали недавно), тем более что для этого есть и другие инструменты, о которых мы тоже говорили.

Вот еще несколько полезностей в этом окне.

ВСТАВКА ШИРИНЫ СТОЛБЦОВ

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



Достаточно выделить первую таблицу (можно столбцы целиком, а можно просто ячейки любой строки), скопировать их, а далее перейти к первому столбцу другой таблицы и вызвать специальную вставку (правая кнопка мыши – специальная вставка или Ctrl + Alt + V). И выбрать там «Ширины столбцов» (Column widths).



УМНОЖЕНИЕ И ДЕЛЕНИЕ С ПОМОЩЬЮ СПЕЦИАЛЬНОЙ ВСТАВКИ

С помощью специальной вставки можно преобразовать целый диапазон чисел благодаря математическим операциям. Например, если нужно сделать все числа отрицательными, можно скопировать ячейку с числом –1, выделить диапазон и в специальной вставке выбрать «Умножить» (Multiply).



Все числа будут умножены на –1.



Если нужно поделить все числа на тысячу, введите это число в любую ячейку, скопируйте – и далее такой же алгоритм, только в специальной вставке нужно будет выбрать «Разделить» (Divide).

Стили (Cell Styles)

Стиль (Cell Styles) – это готовый набор параметров форматирования ячейки, стилевого и/или числового. У стилей есть имена, их можно менять, удалять и создавать с нуля.



Чем полезны стили?

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

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


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

Если нужно забрать стили из другой книги Excel (она должна быть открыта), используйте команду «Объединить стили» (Merge Styles). Из выбранной книги в текущую попадут все стили – и созданные пользователем, и измененные стандартные (они заменят такие же стандартные в текущей книге – имейте это в виду, если много ячеек уже оформлены с помощью стандартных стилей).



Увы, в Google Таблицах стилей нет.

Пользовательские форматы (Custom format)

Файл с примерами: Пользовательские форматы. xlsx

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

Формат настраивается в окне формата ячеек – Ctrl + 1 (⌘ + 1) – выбирайте «Все форматы» (Custom) и вводите код формата в поле сверху.



В Google Таблицах: Формат → Числа → Другие форматы чисел (Format → Number → Custom number format).

СИМВОЛЫ, ИСПОЛЬЗУЕМЫЕ В КОДАХ ПОЛЬЗОВАТЕЛЬСКИХ ФОРМАТОВ

0 – незначащие цифры (отображаются всегда)

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



, – десятичная запятая (знак, отделяющий целую часть от дробной)

В американских региональных настройках и в Google Таблицах при любых настройках – точка, а не запятая.

Используйте ее в формате, если нужно отображать знаки после запятой.

Например:

0,000 – формат, в котором всегда будут отображаться три знака после запятой (даже если число целое).



# – значащие цифры (отображаются, если на этой позиции есть значение)

0,0# – формат, в котором всегда будет отображаться один знак после запятой (даже если число целое), а еще один – только если есть сотые.



? – цифры после запятой, если нужно выравнивать числа по десятичной запятой

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



# ## – разделители разрядов

В американских региональных настройках и Google Таблицах #,## (с запятой между решетками, а не пробелом).

# ## – разделители групп разрядов (пробелы в российских региональных настройках) к числу. Например, # ##0,00 – это числовой формат с разделителями групп разрядов и двумя обязательными знаками после запятой.



0 % – процентный формат

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



* – заполнение ячейки указанным символом до конца

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



"Текст" – текст, указанный в кавычках, будет отображаться в ячейке

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

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

В следующем примере к числу с разделителями разрядов мы добавляем текст "шт.".



Пробел – после формата числа округляет его до тысяч (два пробела – до миллиона, три – до миллиарда и так далее).

В американских региональных настройках – запятая, а не пробел.

В следующем примере в качестве формата используется 0 (с одним пробелом) после него. Это округляет числа до тысяч.



@ – текст, введенный в ячейке

Этот символ обозначает текст из ячейки. Например, @@ – повторение текста дважды. А @*- – формат с заполнением ячейки дефисами после текста.



_ (нижнее подчеркивание) – отступ на ширину указанного после нижнего подчеркивания символа

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



Без отступа эти числа выглядели бы так.



[Цвет] – цвет значения

Задается по названию (в Excel – на языке интерфейса, в Google Таблицах – на английском) [Красный][Red] или номеру [ЦветN][ColorN].

Указывается перед форматом.

Например, [Синий]0,00 – синий цвет, число с двумя знаками после запятой.

Коды цветов есть в файле с примерами «Пользовательские форматы. xlsx».



СТРУКТУРЫ ПОЛЬЗОВАТЕЛЬСКИХ ФОРМАТОВ

Есть две структуры пользовательских форматов.

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

Положительные; Отрицательные; Ноль; Текст

Пример 1: положительные числа – зеленым, отрицательные – красным, ноль не отображаем, текст – синим.

[Зеленый]0;[Красный]-0;;[Синий]@



Пример 2: положительные числа со знаком «плюс» и в процентном формате, отрицательные – со знаком «минус», красным цветом, в процентном формате. Ноль – как прочерк (дефис, «-»).

+ 0 %;[Красный]-0%;"-"



По условиям. Один формат для одного условия, опционально – другой для второго и третий для всех остальных случаев.

Условие 1; Условие 2; Остальные случаи

Условия указываются в квадратных скобках с использованием знаков «равно» (=), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=).

Пример 1: числа больше 2000 с разделителями разрядов, меньше – с одним знаком после запятой:

[>2000]# ##0;0,0



Пример 2: единицы отображаем как слово «один», двойки – как «два», остальные числа в обычном формате, как числа (без разделителей групп разрядов, без знаков после запятой).



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


Видео: https://www.youtube.com/watch?v=O-YOrSL89f4

https://kinescope.io/n8ohYBpQ3w3gufd8Ab4owc


Пользовательские числовые форматы в Google Таблицах (Custom number formats in Google Sheets) https://shagabutdinov.ru/custom_format/


Дополнительные примеры в статье и видео будут полезны и пользователям Excel.


Как изменить шаблон книги Excel по умолчанию

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

Для этого лучше изменить шаблон создаваемой по умолчанию (пустой) книги Excel.

Алгоритм следующий.

1. Создайте новую книгу Excel.

2. Добавьте в нее нужные вам пользовательские форматы.

3. Добавьте/создайте/измените стили, которые вам будут нужны в разных книгах. Обратите внимание, что если изменить стиль «Обычный» (Normal), то вы зададите формат абсолютно всех ячеек в новых книгах Excel (так как по умолчанию именно этот стиль применяется ко всем ячейкам).

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

5. Эту книгу нужно будет сохранить в системную папку XLSTART. Как ее найти?

Файл → Параметры → Центр управления безопасностью → Параметры центра управления безопасностью → Надежные расположения (File → Options → Trust Center → Trust Center Settings → Trusted Locations).


6. Теперь эту книгу нужно сохранить как шаблон. «Сохранить как» (Save As), формат – шаблон Excel (Excel Template), расширение. xltx, название «Книга» (Book) – именно так, без цифр.



7. Готово! После сохранения книги под названием «Книга. xltx» в папку XLSTART она будет выступать шаблоном для всех новых книг. Если вам захочется вернуться к состоянию «по умолчанию», просто удалите ее из папки XLSTART.

Если вы хотите сделать шаблон в Google Таблицах, проще поступить следующим образом: создайте таблицу-шаблон, сделайте ссылку для создания копии (добавьте /copy в конце), добавьте на панель закладок в браузере – и вот вам кнопка для быстрого создания таблицы по образу шаблона. К тому же можно настроить выборочные доступы – только для себя, для коллег из своей команды или для разных подразделений.

Условное форматирование (Conditional Formatting)

Файл с примерами: Условное форматирование. xlsx

Условное форматирование (Conditional Formatting) – это автоматическое форматирование ячеек при соблюдении заданных вами условий, например:

• все ячейки с числами более 10 000 выделяются полужирным шрифтом, и к ним применяется определенный числовой формат;

• все дубликаты заливаются зеленым цветом;

• к любой текстовой ячейке, содержащей слово «Москва», применяется красный цвет шрифта.

Условное форматирование (Conditional Formatting) находится на ленте инструментов на вкладке «Главная» (Home) в Excel.

В Google Таблицах – в меню «Формат» (Format) (Alt + O + F).

В меню условного форматирования Excel – следующие опции.

Правила выделения ячеек (Highlight Cells Rules): здесь можно настроить выделение ячеек, содержащих значения «больше/меньше определенного уровня» (для чисел), определенный текст, определенные даты или дубликаты.

Правила отбора первых и последних значений (Top/Bottom Rules): здесь правила с относительными условиями – например, для выделения значений выше или ниже среднего, лучших или худших значений (допустим, 10 % наименьших или 20 % наибольших значений).

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

Создать правило (New Rule): создание собственного правила с указанием всех параметров, в том числе есть возможность создать условное форматирование с условием, заданным формулами.

Удалить правила (Clear Rules): удаление правил из выделенных ячеек или всего листа.

Управление правилами (Manage Rules): вызов окна, в котором будут видны все существующие правила условного форматирования, можно удалять правила, настраивать их и менять их приоритетность.

«ОБЫЧНЫЕ» ПРАВИЛА

Числа

Рассмотрим работу условного форматирования на примере. Наша задача – выделить зеленым все ячейки с продажами более 400.



Выделяем все ячейки от B2 и до конца столбца (это проще всего сделать сочетанием клавиш Ctrl + Shift + ↓) и вызываем правило условного форматирования «Больше» (Greater Than).



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



Условное форматирование (и это касается не только правил «Больше», а любых правил вообще) применяется автоматически при изменении данных, то есть как только в какой-то ячейке мы поменяем значение на число менее 400, она перестанет форматироваться, и наоборот, если ячейка начнет удовлетворять условиям, к ней будет применена зеленая заливка.

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




Отбор первых и последних значений

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



Допустим, мы хотим выделить 5 самых больших чисел в диапазоне. Тогда мы выделяем диапазон с числами и выбираем вариант «Первые 10 элементов…» (Top 10 Items). Обратите внимание, что в названиях тут фигурируют 10 элементов или 10 %, хотя на самом деле эти значения мы можем задавать сами.



В поле с количеством по умолчанию будет 10, но мы вправе менять это число. В примере мы выделяем зеленым только 5 наибольших элементов.

Если нам нужно выделять какую-то часть (в процентах) самых больших или самых маленьких значений, то нужны правила «Первые 10 %» (Top 10 %) или «Последние 10 %» (Bottom 10 %). Допустим, мы хотим выделить красным худшую четверть – 25 % самых маленьких значений. Это правило «Последние 10 %».



Наконец, если мы хотим выделить все дни выше или ниже среднего значения, используем соответствующие правила «Выше среднего» (Above Average) и «Ниже среднего» (Below Average). У них нет числового параметра – только возможность выбора форматирования.



Текст

Правила для текстовых ячеек есть двух типов: точное соответствие («Равно») и «Текст содержит», то есть вхождение определенного слова / символа / сочетания символов в состав текстовой строки в ячейке.

Так, если в следующей таблице мы хотим выделить все ячейки с конкретной моделью ноутбука в столбце «Товар», то подойдет и правило «Равно» (Equal To), и правило «Текст содержит» (Text that Contains).




А вот если мы хотим выделить цветом все товары бренда Lenovo (то есть ячейки, в которых это слово входит в состав текста), то подойдет только правило «Текст содержит» (Text that Contains).



ССЫЛКИ НА ЯЧЕЙКИ В ПРАВИЛАХ УСЛОВНОГО ФОРМАТИРОВАНИЯ

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

Обычно условие (например, число, больше которого должны быть значения ячеек, чтобы они форматировались) указывается в самом правиле, например:



В таком случае все ячейки с числами более 400 будут автоматически форматироваться. Но чтобы изменить само условие (число 400), нужно будет изменять правило условного форматирования: Главная → Условное форматирование → Управление правилами (Home → Conditional Formatting → Manage Rules).

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



Обратите внимание на доллары в ссылке на ячейку E1 в правиле – это «закрепленная» (абсолютная, со знаками доллара) ссылка на ячейку E1. Мы форматируем целый столбец B с продажами, а сравниваем каждое число в нем с одной и той же ячейкой E1. Если бы мы не закрепили ссылку на нее долларами, то с ней сравнивалась бы только первая ячейка диапазона – B2. А уже B3 бы сравнивалась с пустой ячейкой E2, B4 – с E3 и так далее (по аналогии с протягиванием формул, при котором все относительные ссылки смещаются).

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



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

В таком случае нам нужна относительная ссылка, ведь B2 будет сравниваться с C2, B3 c С3 и так далее – каждый раз это разные ячейки в рамках одной и той же строки.

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



ГИСТОГРАММЫ, ЦВЕТОВЫЕ ШКАЛЫ И ЗНАЧКИ

В условном форматировании есть три опции для визуализации данных с помощью графических элементов: гистограммы (Data Bars), цветовые шкалы (Color Scales) и значки (Icon Sets). Причем их можно применять как к обычным ячейкам с данными, так и к полям сводных.

В Google Таблицах графических элементов (гистограмм и значков) в условном форматировании нет – только цветовые шкалы. Значки можно вставлять с помощью текстовых функций или через функцию IMAGE (если у вас есть ссылка на значок или эмодзи). А гистограмму можно построить с помощью функции SPARKLINE (горизонтальная гистограмма в этой функции имеет тип bar).


Гистограммы (Data Bars)

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

Есть двенадцать готовых вариантов гистограмм – со сплошным цветом и полупрозрачных.



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



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

• если сразу выбрать в списке наборов значков не готовый вариант, а «Другие правила» (More Rules), тогда вы попадете в диалоговое окно с настройками;

• через «Управление правилами» (Manage Rules) в «Условном форматировании».



Например, так будет выглядеть гистограмма, если активировать опцию «Показывать только столбец» (Show Bar Only) в настройках.



Цветовые шкалы (Color Scales)

Цветовые шкалы позволяют залить ячейки разными цветами (изменить цвет фона) в зависимости от их значений. В цветовых шкалах задаются «пограничные» цвета: для минимальных и максимальных значений (а также средних значений, хотя можно задать и только два цвета) форматируемого диапазона. Ячейки будут окрашиваться в соответствующие оттенки в зависимости от того, к чему ближе числа в этих ячейках.

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



Есть возможность настраивать свои шкалы, для этого нужно выбрать вариант «Другие правила» (More Rules). В диалоговом окне отмечаем нужные цвета и в образце видим, как будет выглядеть фон ячеек.




Наборы значков (Icon Sets)

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



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

Если их три, то для каждой трети (меньшая треть, средняя и наибольшая).

Если четыре, то для каждой из четырех равных частей.

Если пять, то, соответственно, для каждой из пяти.

Но настройки можно изменять – см. «Настройка графических объектов условного форматирования» ниже.


Превращаем цифры (оценки) в значки

Значки хорошо подходят для оценок, например от 1 до 5.



В таком случае нужно выбрать набор значков с количеством, соответствующим числу оценок, – и каждой будет соответствовать свой.



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




Настройка графических объектов условного форматирования

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

• В случае с цветовой шкалой все числа в диапазоне сортируются: для минимального применяется один «крайний» цвет шкалы, для максимального – другой, для среднего (если в шкале три цвета) – «средний» цвет, а для остальных – в зависимости от положения числа на спектре всех значений в диапазоне.

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

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

Если вам нужно что-то другое, заходите в настройки правила или сразу выбирайте вариант «Другие правила» (More Rules).



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



Например, можно задать точные числа (а не минимум/максимум, как по умолчанию) в качестве «пограничных».



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



Обратите внимание, что есть тип «Процент» (Percent) и «Процентиль» (Percentile), – так, по умолчанию у трехцветных шкал «средний» цвет для процентиля равен 50. Это медиана – значение, которое находится в середине набора чисел (если его упорядочить).

То есть процент – про значения, а процентиль – про положение в упорядоченном наборе чисел.

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



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

В следующем примере мы ориентируемся не на проценты, а на конкретные числа (для этого нужно поменять «Тип» на «Число»): числа больше 40 000 – галочка, числа от 25 000 до 40 000 – без значка (значок выбирается из списка, можно комбинировать значки из разных наборов или, как в этом случае, вовсе убрать значок для определенных условий), числа до 25 000 – крестик.



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



Проверка данных (Data Validation)

Проверка данных (Data Validation) – инструмент для предотвращения ошибок при вводе информации в ячейки. Проверка данных позволяет заранее определить, какие значения можно вводить, а какие нет. Находится на ленте в «Данных» (Data).



В Google Таблицах: в меню «Данные» → «Настроить проверку данных» (Data → Data Validation).

Для чего нужна проверка данных? Чтобы исключать типовые ошибки:

• текст вместо чисел;

• отрицательные числа там, где их быть не может;

• числа с дробной частью там, где должны быть целые;

• текст вместо даты;

• разные варианты написания одного и того же значения – например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами, – всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

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

Вот какие типы данных тут есть.

• Любое значение (Any value), то есть отсутствие проверки данных.

• Целое число (Whole number): можно задать ограничения снизу или сверху (например, числа больше 10 000). Удобно, если у вас натуральный показатель (в штуках) и дробной части быть не может.

• Действительное число (Decimal): тоже можно задавать ограничения. Подойдет, например, для величины сделки. У такого показателя может быть дробная часть (копейки), но может понадобиться ограничение (например, от 0).

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

• Дата (Date): можно задать ограничения, например «дата не позже / не раньше определенной или в промежутке».

• Время (Time): аналогично.

• Длина текста (Text length): ограничение на количество символов (можно задать ограничение и снизу, и сверху, и интервал).

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



Если же выбрать «Предупреждение» (Warning) или «Сообщение» (Information), то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.

ОБВЕСТИ НЕВЕРНЫЕ ДАННЫЕ

Если в ячейках с проверкой уже есть неверные данные (например, они были в них до того, как вы настроили проверку, либо у вас проверка с предупреждением, а не остановкой), их можно обвести, чтобы точно увидеть, где есть ошибки. Для этого выберите команду «Обвести неверные данные» (Circle Invalid Data).



Для удаления обводки выберите «Удалить обводку неверных данных» (Clear Validation Circles) в том же выпадающем списке.



ПОИСК В ВЫПАДАЮЩЕМ СПИСКЕ

В Google Таблицах и в Excel только с 2023 года в рамках подписки Microsoft 365 (опция появилась в начале 2022-го, но ее долго не было у обычных пользователей) в проверке данных можно искать значение, то есть после ввода части текста список фильтруется до тех значений, которые соответствуют введенному фрагменту. Это очень удобно, когда значений в исходном списке тысячи, например это названия товаров.



ОБНОВЛЯЕМЫЙ ВЫПАДАЮЩИЙ СПИСОК

Часто бывает, что проверка данных формируется на основе диапазона, к которому предполагается добавлять новые значения (список филиалов-городов, который может пополниться; сотрудников – можем нанять новых; и так далее).

Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?

Решения могут быть разные в зависимости от того, на разных ли листах данные.

Если данные на том же листе – превращаем справочник в таблицу (Ctrl + T или Ctrl + L, подробнее смотрите главу про таблицы) и ссылаемся на него.

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

Выделяем столбец в таблице на другом листе и присваиваем ему имя (можно ввести его в поле «Имя» слева от строки формул и нажать Enter).



После этого в проверке данных остается нажать F3 и выбрать в появившемся окне «Вставка имени» созданное имя.



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



В Excel 2007 и ранее в проверке данных в принципе нельзя было ссылаться на другой лист, это нужно было делать через ДВССЫЛ / INDIRECT, даже если список не предполагается обновлять.

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


Выпадающие списки в Excel с автоматическим добавлением новых значений

https://mif.to/VRU7P

https://kinescope.io/i7XACZADsM6FPApAT2HSTB


Проверка данных (Data Validation) с формулами

Файл с примерами: 3 Проверка данных с формулами. xlsx

Для понимания этой главы стоит разобраться с логическими значениями и формулами. Если вы еще не знакомы с этой темой, обратитесь к главе «Логические выражения и функция ЕСЛИ / IF».

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

Для создания правила проверки с формулой нужно проследовать по следующему пути: Данные → Проверка данных → Тип данных: Другой → Формула (Data → Data Validation → Allow: Custom → Formula).


Формула должна возвращать ИСТИНА (TRUE), то есть условие должно выполняться. Иначе проверка данных будет выдавать ошибку или предупреждение – зависит от настроек в разделе «Сообщение об ошибке» (Error Alert).

Рассмотрим несколько примеров проверки с формулами.

РАЗРЕШАЕМ ВВОДИТЬ ТЕКСТ ТОЛЬКО МЕНЬШЕ ОПРЕДЕЛЕННОЙ ДЛИНЫ

Длину текста можно определить с помощью функции ДЛСТР / LEN. Она возвращает число – количество символов в ячейке. Соответственно, если мы хотим запретить ввод текста длиннее, допустим, десяти символов, то формула будет выглядеть следующим образом:

=ДЛСТР(ссылка на первую ячейку диапазона)<=10

РАЗРЕШАЕМ ВВОДИТЬ ТОЛЬКО ФОРМУЛЫ

Функция ЕФОРМУЛА / ISFORMULA возвращает ИСТИНА, если ее аргумент – ячейка с формулой. Соответственно, следующая формула в проверке данных будет разрешать ввод только формул.

=ЕФОРМУЛА(ссылка на первую ячейку диапазона)

РАЗРЕШАЕМ ВВОДИТЬ ТОЛЬКО УНИКАЛЬНЫЕ ЗНАЧЕНИЯ

Уникальные значения – это значения, которые в диапазоне встречаются всего лишь один раз. Подсчитать количество можно с помощью СЧЁТЕСЛИ / COUNTIF:

=СЧЁТЕСЛИ(диапазон; критерий)

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

=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)

И сравнивать это с единицей: если значение будет встречаться более одного раза, его вводить уже нельзя.

=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)<=1

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

=СЧЁТЕСЛИ(A$2:A$30; A2)<=1

РАЗРЕШАЕМ ВВОДИТЬ ВСЕ СЛОВА ТОЛЬКО С ЗАГЛАВНОЙ БУКВЫ

Формула будет выглядеть так:

=СОВПАД(первая ячейка диапазона; ПРОПНАЧ(первая ячейка диапазона))

Функция СОВПАД / EXACT проверяет совпадение с учетом регистра. А ПРОПНАЧ / PROPER меняет регистр текста – делает первые буквы каждого слова заглавными.

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

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

Сортировка данных

Сортировка данных – упорядочивание по значениям в одном или нескольких столбцах. Без этой операции никуда: а как иначе посмотреть топ продаж по какому-либо параметру (городам, товарам и так далее)? Это помогает упорядочить таблицу, анализировать данные становится проще.

Быстрее всего отсортировать данные по возрастанию или убыванию можно на вкладке «Данные».



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



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



Кроме того, сортировать можно с помощью фильтра (о нем ниже).

А если простой сортировки по одному столбцу недостаточно, открывайте окно «Сортировка» (одноименная кнопка на ленте).



Здесь есть возможность сортировать по нескольким столбцам. Допустим, мы хотим упорядочить таблицу по клиентам, а в рамках каждого клиента – по сумме, чтобы самые крупные сделки были сверху. Тогда мы добавляем два соответствующих уровня сортировки в этом окне – сначала компанию, а затем, нажав кнопку «Добавить уровень» (Add Level), – сумму.



Результат:



В «Параметрах» в сортировке есть две опции: можно сортировать не строки, а столбцы и учитывать регистр текста при сортировке.



Рассмотрим сортировку с помощью пользовательских списков. Не всегда нужно сортировать данные по алфавиту.

Возможно, вы уже обращали внимание, что месяцы или дни недели сортируются не по алфавиту, а в правильном (хронологическом) порядке. Это происходит потому, что в Excel встроены соответствующие списки для сортировки – с короткими и длинными названиями месяцев и дней недели. Мы не можем их удалить или изменить, но можем добавлять новые!

Допустим, в вашей компании есть правила сортировки списка филиалов – не по алфавиту. В таком случае стоит создать пользовательский список.

Списки добавляются и удаляются в параметрах Excel: Дополнительно → Общие (раздел в самом конце вкладки «Дополнительно») → Изменить списки (Options → Advanced → General → Edit Custom Lists).



В окне со списками есть списки дней недели и месяцев – их редактировать и удалять нельзя. Но можно добавлять свои.



После добавления сортировка в диапазонах, таблицах и сводных таблицах может осуществляться по такому порядку. Кроме того, список можно создать непосредственно в процессе сортировки. В любом случае нужно выбрать «Настраиваемый список…» (Custom List…) в окне сортировки.



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





Еще один плюс пользовательских списков: они работают при протягивании.



В Google Таблицах пользовательских списков нет.

Фильтрация данных

Фильтрация данных – еще одна базовая операция, без которой никуда в Excel или Google Таблицах. Фильтрация – отбор и отображение строк по заданным критериям (текстовым, числовым или даже по цвету ячейки/шрифта).

Фильтр можно установить и снять сочетанием клавиш Ctrl + Shift + L или на ленте инструментов на вкладке «Данные» (Data → Filter).



Кроме того, если вы превращаете диапазон в таблицу – сочетания клавиш Ctrl + L или Ctrl + T, «Вставка → Таблицы» (Insert → Table) или «Главная → Форматировать как таблицу» (Home → Format as Table) на ленте, фильтр создается автоматически (подробнее об этом инструменте мы поговорим в соответствующей главе ниже).

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



ВЫБОР ОПРЕДЕЛЕННЫХ ЗНАЧЕНИЙ

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



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


Отображаются строки с двумя продуктами из трех – курсами и электронными библиотеками


Чтобы снять фильтрацию в определенном столбце, нажмите «Снять фильтрацию с…» (Clear Filter From) или «Выделить все» (Select All), что сделает все элементы выделенными, а это значит, что фильтрации по столбцу не будет.



Чтобы снять фильтрацию во всех столбцах быстро, проще всего снова нажать Ctrl + Shift + L.

ТЕКСТОВЫЕ ФИЛЬТРЫ

Допустим, нам нужны все сделки с индивидуальными предпринимателями или ООО. Конечно, в нашем случае можно выбрать их и по одному, но что, если клиентов (как это и будет в реальной жизни) намного больше?

Тогда лучше воспользоваться текстовыми фильтрами: в открывающемся списке будет несколько вариантов.



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



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



Поэтому, если вам нужны строго два варианта, лучше выбрать «ИЛИ» (Or) и указать их без символов подстановки.



ЧИСЛОВЫЕ ФИЛЬТРЫ

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



Например, можно отобразить строки со сделками крупнее 12 000.



Обратите внимание на числовой фильтр «Первые 10» (Top 10). По умолчанию это 10 наибольших значений.



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





Кроме того, можно наложить условие не по количеству элементов, а по процентам. Например, посмотреть на 20 % самых крупных сделок.



ФИЛЬТР ПО ЦВЕТУ

Фильтровать можно по цвету (заливки и шрифта), и неважно, был этот цвет изменен вами вручную или он появляется в результате работы условного форматирования.

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



Здесь используется формула для форматирования ячеек =ДЕНЬНЕД(D2;2)>5 – мы проверяем, что день недели больше 5-го (то есть 6 или 7). Второй аргумент функции ДЕНЬНЕД определяет тип нумерации; 2 – начало недели с понедельника, то есть ему соответствует единица.

Мы можем отфильтровать (и отсортировать тоже) эти строки по цвету.




В Google Таблицах фильтр устанавливается на панели инструментов или через меню «Данные» (Alt + D → F).



Чтобы настраивать фильтры, которые не будут видны другим пользователям при совместной работе (с книгами Excel, хранящимися в облаке), используйте «Представления» (Sheet Views) – о них подробнее в соответствующей главе ниже.

ОБНОВЛЕНИЕ РЕЗУЛЬТАТОВ ФИЛЬТРАЦИИ

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

Для обновления фильтра нажмите Ctrl + Alt + L. Или кнопку «Повторить» (Reapply) на ленте рядом с кнопкой фильтра.



Если у вас Excel 2021 / Microsoft 365 или вы используете Google Таблицы, можно использовать функции СОРТ / SORT и ФИЛЬТР / FILTER. Так как это функции, то результат (отсортированные и/или отфильтрованные данные) будет обновляться автоматически при изменении исходных данных.

ФИЛЬТРЫ В GOOGLE ТАБЛИЦАХ

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

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



Доступ ко всем созданным в файле фильтрам (режимам фильтрации, Filter views) есть у любого пользователя, даже с доступом на просмотр (но менять и удалять их он не сможет).

Пользователь с доступом на просмотр/комментирование может создавать только временные фильтры или просматривать чужие режимы фильтрации. Временные фильтры не сохраняются после закрытия вкладки и не видны другим пользователям.

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

Хорошее правило – периодически удалять все фильтры без названий, чтобы не засорять файл. Если у фильтра нет названия, значит, его создали «на один раз». Правильная же ситуация – когда у каждого отдела/сотрудника/ситуации есть свой фильтр, например: «Иванов», «Маркетинг» или «Проекты в работе с приоритетом 2».

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

Вычисления в Excel: функции и формулы

Что такое формула и функция в Excel

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

Автоматический пересчет формул можно отключить – например, если их в книге очень много и вам не хочется после любого действия ждать, пока большое количество формул обновится. Это можно сделать в параметрах Excel: Параметры → Формулы → Вычисления в книге → Вручную (Options → Formulas → Workbook Calculation → Manual).



А также на ленте во вкладке «Формулы».



Там же справа есть кнопки для пересчета всех листов (F9, Fn + F9) или текущего листа (Shift + F9, Fn + ⇑ + F9).


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

Для этого достаточно включить флажок «Формат устаревших значений» (Format Stale Values) на вкладке «Формулы».

Работать это будет при ручном пересчете формул.

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

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



Кроме того, можно включить режим отображения формул: вкладка «Формулы» – «Показать формулы» (Show Formulas), тогда вы будете видеть их прямо в ячейках.



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



Ввод формулы начинается со знака «равно» (=). Если вы хотите ввести текст, который начинается с этого знака, введите перед ним апостроф (') – он автоматически превращает значение в ячейке в текст, и оно не будет вычисляться как формула.

В формулах используются:

• знаки математических операций: плюс (+), минус (—), деление (/), умножение (*), возведение в степень (^);

• знак «амперсанд» (&) – он используется для «склеивания» (объединения, конкатенации) текстовых значений;

• круглые скобки для определения порядка операций;

• числа и текст, введенные вручную прямо в формуле (текст указывается в формулах в кавычках);

• ссылки на ячейки и диапазоны (они указываются без кавычек);

• пробел: это оператор пересечения (например, A1:E5 B2:D6 – это ссылка на пересечение двух диапазонов (на их общие ячейки));

• решетка (#): это ссылка на массив, который возвращается формулой из указанной ячейки (такой тип ссылок работает только в Excel 2021 / Microsoft 365); например, A1# – это ссылка на массив значений, который возвращает формула, находящаяся в ячейке A1 (мы подробно обсудим в главе «Динамические массивы», зачем нужны и как работают такие ссылки);

• знаки сравнения: «равно» (=) (знак «равно» в начале формулы присутствует всегда, а еще один может использоваться, если мы хотим сравнить два значения), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=), «не равно» (<>);

• функции рабочего листа Excel (например, СУММ / SUM, СРЗНАЧ / AVERAGE, ВПР / VLOOKUP и другие, всего функций в Excel более 500 – количество разнится в зависимости от версии); ниже мы поговорим о том, как вставлять функции в формулы.

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

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

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

• Ввести ее адрес с клавиатуры (не забывайте, что номера столбцов – это латинские буквы).

• Выделить соседнюю с формулой ячейку с помощью клавиш со стрелками (это удобно, если вам нужно сослаться на ячейку, которая находится недалеко от ячейки с формулой). Когда вы вводите формулу, есть два режима редактирования: в одном нажатие стрелок на клавиатуре перемещает курсор в самой формуле, во втором – выделяет ячейки. Переключаются эти режимы клавишей F2 (^ + U).

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



То же самое происходит не только при первом вводе формулы, но и если войти в режим редактирования (нажать F2, или щелкнуть двойным щелчком по ячейке с формулой, или щелкнуть на строку формул).

АБСОЛЮТНЫЕ И ОТНОСИТЕЛЬНЫЕ ССЫЛКИ

Обычная ссылка на ячейку, которая формируется по умолчанию и выглядит как A1, – это относительная ссылка.

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

Если мы ссылаемся из ячейки C2 на A2 – это ссылка на ячейку на два столбца левее от формулы, а не на ячейку с адресом A2.



Потому что стоит скопировать и вставить или протянуть (мышкой за правый нижний угол ячейки) такую формулу вниз в следующую строку – и ссылка на A2 превратится в A3. А если скопировать формулу вправо – то в B2. Это по-прежнему будет ссылка на ячейку на два столбца левее от формулы, потому она и называется относительной.



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

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



А вот если нам необходимо ссылаться на ячейку с определенным адресом, куда бы мы ни протягивали формулу (например, если для разных строк нам нужно брать одну и ту же ставку налога из ячейки D1), то нужно использовать абсолютные ссылки. Для этого достаточно нажать F4 (⌘ + T) после того, как вы щелкнули на ячейку в формуле (или после того, как установили курсор на ссылку в формуле). Появятся доллары у номеров строки и столбца – это означает, что ссылка стала абсолютной, а не относительной. То есть ссылкой на ячейку с адресом D1, а не на ячейку справа и сверху от формулы.



Эту формулу следует читать так (с точки зрения ссылок): произведение ячейки слева от формулы (B3) и ячейки D1 ($D$1).

И после протягивания формулы вниз по всей таблице она по-прежнему будет содержать ссылки на ячейку слева от себя (в каждом случае это будут разные ячейки) и на ячейку D1.



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

Ссылка вида A$1 – это ссылка на первую строку, а вот ссылка на столбец остается относительной, то есть будет меняться при копировании формулы с такой ссылкой. Аналогично $A1 – это ссылка на столбец A, а номер строки будет меняться при перемещении формулы.

В следующем примере нам нужно перемножить возможные варианты объема производства (из строки 2) и оптовых цен (из столбца B) – чтобы на пересечении получить сценарии по выручке для каждой пары параметров.



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



Абсолютные ссылки тоже не подойдут: с ними мы в любых ячейках будем умножать одни и те же ячейки C2 и B3 друг на друга. Это пример ситуации, когда нужны смешанные ссылки, ведь нам нужно перемножать разные (относительная часть ссылки) числа из столбца B (абсолютная часть ссылки) с разными (относительная) числами из строки 2 (абсолютная).



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


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

1. Ссылаемся на первую ячейку, «эфчетырим» ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, «закрепить»).

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

=СУММ($B$2:B2)

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

ССЫЛКИ НА СТОЛБЦЫ И СТРОКИ ЦЕЛИКОМ

В Excel (и в Google Таблицах) можно ссылаться не только на отдельные ячейки и диапазоны, но и на столбцы и строки целиком.

Вот как выглядят такие ссылки:

2:2 – относительная ссылка на всю вторую строку;

$3:$3 – абсолютная ссылка на всю третью строку;

A: E – относительная ссылка на пять столбцов, от A до E, целиком;

$B:$B – абсолютная ссылка на весь столбец B.

Плюс в том, что все строки будут учитываться в формуле. Минус в том, что попадут лишние, то есть мы априори ссылаемся на миллион с лишним строк.

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



ИМЕНОВАННЫЕ ДИАПАЗОНЫ

Файл с примерами: Имена диапазонов. xlsx

В Excel и Google Таблицах диапазонам можно присваивать имена. После того как диапазону присвоено имя, на него можно ссылаться в формулах, и такие формулы читаются легче (=Налог*Выручка, а не =B2*$E$1). Кроме того, имена помогают в некоторых особых ситуациях.

В Excel можно задать имя следующими способами.

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



Если в диапазоне есть заголовки и вы хотите их использовать в качестве имен, применяйте команду «Создать из выделенного» на вкладке «Формулы» (Formulas → Create from Selection). Можно также использовать сочетание клавиш Ctrl + Shift + F3.



Еще можно пользоваться диспетчером имен – это окно вызывается сочетанием Ctrl + F3, там можно и создавать имена, и просматривать/изменять/удалять существующие.



В Google Таблицах имена настраиваются в меню «Данные» → «Настроить именованные диапазоны» (Data → Named ranges). Можно задавать и в поле «Имя» слева от строки формул, как в Excel.



Есть некоторые ограничения:

• имя может быть длиной до 255 символов;

• имена могут начинаться с буквы, нижнего подчеркивания, обратной косой черты;

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

После того как мы задали имя, мы можем использовать его в формулах.

Имена отображаются в подсказке с функциями. Так, если у нас есть имя "Ставка_роялти", то оно появится в списке при вводе первых букв. Достаточно будет щелкнуть на него или выделить и нажать Tab.



Если мы присвоили имя одной ячейке, можно ссылаться на нее (это будет словно абсолютная ссылка), не переживая, что ссылка «поедет» (как это было бы с относительной ссылкой).



Если же имя присвоено целому диапазону, то мы можем:

• ссылаться на значение из этого диапазона в той же строке/столбце (своего рода относительная ссылка) в версиях Excel до 2019 включительно или ссылаться сразу на весь диапазон в Excel 2021 / Microsoft 365;



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



Имена могут действовать на уровне всей книги (то есть вы можете ссылаться на данные по имени с любого листа) – и это более частый вариант. Либо на уровне одного рабочего листа. Область действия задается в одноименном поле в диалоговом окне «Вставка имени». Книга – это вся книга Excel, а далее идут названия листов.



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



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



А еще можно просто ввести в этом поле адрес любой ячейки и нажать Enter, чтобы к ней перейти. Кстати, это пригодится, чтобы посмотреть, какие данные есть в скрытой ячейке (и даже отредактировать их).

Также перейти к любой ячейке можно с помощью окна «Переход» (вызывается клавишей F5).



Обратите внимание, что имена не применяются автоматически в существующих формулах, если вы ранее ссылались на диапазон или ячейку. В таких формулах останутся обычные ссылки. Если вы хотите, чтобы они заменились на имя, нужно применить имена – это команда на ленте: Формулы → Задать имя → Применить имена… (Formulas → Define Name → Apply Names…).


Команда будет доступна, если есть имена, которые можно применить


Итак, в чем польза имен:

• более легкое восприятие формулы;

• выбор имени из выпадающего списка при вводе формул;

• быстрый переход к именованному диапазону через поле «Имя»;

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

Кроме того, они будут отображаться при уменьшении масштаба до того уровня, когда cмогут поместиться поверх соответствующих диапазонов.



ССЫЛКА НА ПЕРЕСЕЧЕНИЕ ДИАПАЗОНОВ

Файл с примером: Пересечение диапазонов. xlsx

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

Например, если присвоить всем диапазонам в этой таблице имена (это можно сделать с помощью команды «Создать из выделенного» на вкладке ленты «Формулы»)…



…то можно будет ссылаться на показатель и квартал как на пересечение двух диапазонов:

=Материалы март



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

В Google Таблицах оператор пересечения диапазонов не работает.

ССЫЛКИ НА ДРУГИЕ ЛИСТЫ

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

'Название листа'!ссылка на диапазон

Название листа отделяется от ячейки или диапазона восклицательным знаком. Название листа берется в апострофы:

'Лемур'!A1:B300 – это ссылка на диапазон A1:B300 на листе «Выручка».

'Продажи Лемур'!D7 – это ссылка на ячейку D7 на листе «Продажи Лемур».

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

То есть будут работать оба варианта:

'Лемур'!A1:B300

Лемур! A1:B300

А такой вариант ссылки работать не будет:

Продажи Лемур! D7

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

На именованный диапазон (если область его действия – вся книга) можно ссылаться с других листов без имени.

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

ССЫЛКА НА НЕСКОЛЬКО ЛИСТОВ В ФОРМУЛЕ

Файл с примером: Несколько листов. xlsx

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

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

Допустим, нам нужна сумма чисел из ячеек B2 на листах от «Москва» до «Казань». Формула получится такой:

=СУММ(Москва: Казань! B2)

Обратите внимание, что, если после ввода формулы порядок листов поменяется (вы переместите какие-то листы), формула не изменится: она по-прежнему будет суммировать данные с группы листов, первый (левый ярлык) из которых называется «Москва», а последний (самый правый ярлык) – «Казань».

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

P. S. Если в названии хотя бы одного из крайних листов есть пробелы/дефисы, то нужно будет взять оба названия в апострофы (как и в случае со ссылкой на один лист с этими символами):

Bepul matn qismi tugad.

Ренат Шагабутдинов
Matn
4,6
37 baho
15
taqriz
7
iqtiboslar
102 207,93 s`om
Yosh cheklamasi:
16+
Litresda chiqarilgan sana:
20 iyul 2023
Yozilgan sana:
2023
Hajm:
757 Sahifa 846 illyustratsiayalar
ISBN:
9785002503377
Mualliflik huquqi egasi:
Манн, Иванов и Фербер (МИФ)
Yuklab olish formati:
seriyasiga kiradi "МИФ Бизнес"
Seriyadagi barcha kitoblar
Seriyadagi barcha kitoblarni tomosha qiling

Ushbu kitob bilan o'qiladi

O'xshash kitoblar

Muallifning boshqa kitoblari