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

Задача решается по-разному. Можно "разобрать" текст на части функциями ЛЕВСИМВ, ПСТР и ПРАВСИМВ, а потом склеить через дефисы и амперсанды. А можно поступить чуть хитрее и использовать функцию ЗАМЕНИТЬ, опустив третий аргумент.

#УР2 #Примеры_формул

В дополнение к предыдущему уроку. Если вам не нужно именно менять значение в ячейке (то есть физически вставлять в него дефисы), а нужно просто отобразить артикул в ячейке с разбивкой на группы по 3 цифры, то можно применить обычный числовой формат. Код совсем простой: "000-000-000".

#УР2 #Примеры_формул

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

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

#УР2 #Сводные_таблицы

Для математического округления чисел в Excel есть множество встроенных формул (ОКРУГЛ, ОКРВНИЗ, ОКРВВЕРХ и т.д.). Но бывают задачи, где требуется нестандартное округление. Разберем одну из таких.

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

Решается простой функцией ЕСЛИ и функциями для извлечения целой части числа (ЦЕЛОЕ) и его дробной части (ОСТАТ). А те, кто любит более изящные формулы, могут использовать вариант без функции ЕСЛИ.

#УР2 #Примеры_формул

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

Идея в том, чтобы определить последний день предыдущего месяца, а затем от этого дня отсчитать дату через один рабочий день. В итоге попадем на первый рабочий в следующем месяце. В основе вычисления - функция РАБДЕНЬ.

#УР2 #Примеры_формул

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

#УР2 #Сводные_таблицы

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

Выделяем первую ячейку, жмем F5, вводим адрес второй ячейки и зажав SHIFT кликаем ОК. В итоге быстро и удобно выделяется нужный диапазон.

#Горячие_клавиши

В Параметрах Excel можно найти много интересных опций и настроек. Одна из них - автоматическая вставка десятичной запятой. Такая настройка полезна, если нужно вручную вводить много данных, содержащих десятичную часть. Ставим галочку и указываем, сколько разрядов нужно отделять запятой. После активации вводим в ячейку числа с десятичными знаками, а Excel сам вставляет запятую. Но будьте аккуратны. Например, чтобы ввести 0,8 при включенной опции вставки запятой для 2 десятичных знаков, надо вводить в ячейку 080 или 80. Если ввести 08, то получим 0,08, а не нужное значение.

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

#Справка

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

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

#УР2 #Сводные_таблицы

Одна из задач, решаемых в Excel - заполнение каких-то форм на листе различными данными. Если у вас есть такая форма, то наверняка часто приходится очищать поля перед очередным заполнением.

Чтобы сделать такую операцию более удобной, можно выделить все заполняемые ячейки (зажав CTRL), а затем назначить диапазону имя (не забываем нажимать ENTER после ввода в поле Имя). В итоге, можно парой кликов выделить нужный диапазон и нажать DELETE для очистки. А можно и вовсе создать кнопку и привязать на нее коротенький макрос для очистки.

#УР1 #Обработка_таблиц

На макросы в Excel можно назначать горячие клавиши. Это очень удобно и практично. Единственный нюанс - программа различает и раскладки, и регистры. Например, если нам нужно назначить на макрос сочетание CTRL+m, то оно будет работать только при английской раскладке.

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

#УР4 #Макросы

Еще один способ сократить размер файла - просто удалить исходные данные, на которых построены сводные таблицы. Сами сводные при этом не пропадут и не сломаются. Их даже можно будет перенастраивать. А вот обновить сводную уже не получится - источник данных будет недоступен. Разумеется, такой способ можно применять лишь тогда, когда вам ТОЧНО не нужны исходные данные и не потребуется обновлять сводные таблицы. Плюс способа - существенное уменьшение размера файла.

#УР2 #Сводные_таблицы

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

#УР2 #Сводные_таблицы

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

Отключить эту опцию можно в параметрах на вкладке "Формулы".

#Справка

Для поиска текста в ячейке используются функции НАЙТИ и ПОИСК. Их минус в том, что если надо будет искать какое-то слово, то будут найдены не только вхождения этого слова как отдельного, но и сложные составные слова (например, молоко найдется в слове молокозавод).

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

#УР2 #Примеры_формул

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

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

#УР2 #Сводные_таблицы

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

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

#УР2 #Сводные_таблицы

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

#УР2 #Примеры_формул

Откройте в себе внимательного аналитика и научитесь превращать обычные таблицы в мощный инструмент для анализа и автоматизации на бесплатном курсе-симуляторе «Excel: простые шаги для оптимизации работы с данными».

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

🎁 После бесплатного обучения вы получите в подарок карьерную консультацию и скидку до 50% на курс-профессию "Аналитик данных", чтобы улучшить свои навыки под руководством экспертов.

Присоединяйтесь бесплатно в любое время и убедитесь, что Excel — это не просто таблички.

Реклама. ООО "Нетология". Erid 2VSb5yaFMDz

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

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

#УР1 #Работа_с_листами_книги