Выбор уникальных значений из списка
В примере ниже нам нужно было получить список данных с уникальными значениями (фамилии без повторений).
Алгоритм работы:
Шаг 1: Выделяем первый столбец таблицы A1:A19.
Шаг 2: Выбираем Данные → Сортировка и фильтр → Дополнительно.
Шаг 3: В появившемся окне кликаем на Скопировать результат в другое место, а в поле Поместить результат в диапазон указываем
Шаг 4: Отмечаем галочкой Только уникальные записи и нажимаем на ОК.
В примере ниже нам нужно было получить список данных с уникальными значениями (фамилии без повторений).
Алгоритм работы:
Шаг 1: Выделяем первый столбец таблицы A1:A19.
Шаг 2: Выбираем Данные → Сортировка и фильтр → Дополнительно.
Шаг 3: В появившемся окне кликаем на Скопировать результат в другое место, а в поле Поместить результат в диапазон указываем
$F$1.Шаг 4: Отмечаем галочкой Только уникальные записи и нажимаем на ОК.
Как скрыть ошибки и нули из таблицы?
Чтобы скрыть ошибки, нули или любые другие значения — выделите ячейки и перейдите на вкладку Главная → Условное форматирование.
Для ошибок — выберите пункт Создать правило → Ячейки, которые содержат → Ошибки. С помощью кнопки Формат и вкладки Шрифт установите белый (невидимый) цвет шрифта.
Для нулей и других значений — на вкладке с условным форматированием выберите Правила редактирования ячеек → Равно, введите нужное значение (в нашем случае 0) и так же выберите белый шрифт.
Преимущество этого способа в том, что вы в любой момент сможете вернуть всё на свои места с помощью кнопки Удалить правила на вкладке с условным форматированием.
Чтобы скрыть ошибки, нули или любые другие значения — выделите ячейки и перейдите на вкладку Главная → Условное форматирование.
Для ошибок — выберите пункт Создать правило → Ячейки, которые содержат → Ошибки. С помощью кнопки Формат и вкладки Шрифт установите белый (невидимый) цвет шрифта.
Для нулей и других значений — на вкладке с условным форматированием выберите Правила редактирования ячеек → Равно, введите нужное значение (в нашем случае 0) и так же выберите белый шрифт.
Преимущество этого способа в том, что вы в любой момент сможете вернуть всё на свои места с помощью кнопки Удалить правила на вкладке с условным форматированием.
Преобразуем число в денежный формат
Отметим, что данные в столбце Результат будут отформатированы таким образом:
• если это сумма страны Россия – значит результат в рублях;
• если сумма любой страны из зоны Евросоюза – значит в евро-валюте.
Начнем работу:
1) Выделяем диапазон ячеек D2:D5 и выбираем Главная → Стили → Условное форматирование → Управление правилами → Создать правило.
2) Далее в Создание правила форматирования выбираем опцию Использовать формулу для определения форматированных ячеек. В поле ввода введите формулу:
4) Таким же образом создаем второе правило, но вводим формулу
Отметим, что данные в столбце Результат будут отформатированы таким образом:
• если это сумма страны Россия – значит результат в рублях;
• если сумма любой страны из зоны Евросоюза – значит в евро-валюте.
Начнем работу:
1) Выделяем диапазон ячеек D2:D5 и выбираем Главная → Стили → Условное форматирование → Управление правилами → Создать правило.
2) Далее в Создание правила форматирования выбираем опцию Использовать формулу для определения форматированных ячеек. В поле ввода введите формулу:
=F2="Россия"
3) Нажимаем на кнопку Формат и в появившемся окне Формат ячеек на вкладке Число указываем денежный формат «Рубли».4) Таким же образом создаем второе правило, но вводим формулу
=F2<>"Россия" и создаем денежный формат «Евро». Нажимаем ОК.
Сортировка по цвету ячеек
Здесь, на примере, нам нужно отсортировать ячейки в такой последовательности: зеленый, желтый, красный, а затем без цвета. Вот, как мы будем это делать:
Шаг 1: Выбираем Данные → Сортировка и фильтр → Сортировка.
Шаг 2: В секции Столбец выбираем Наименование, в Сортировка выбираем Цвет ячейки, в Порядок — Нет цвета и нажимаем на зеленый прямоугольник.
Шаг 3: Далее Копировать уровень и выбираем желтый, затем красный. Кликаем на ОК, готово.
Здесь, на примере, нам нужно отсортировать ячейки в такой последовательности: зеленый, желтый, красный, а затем без цвета. Вот, как мы будем это делать:
Шаг 1: Выбираем Данные → Сортировка и фильтр → Сортировка.
Шаг 2: В секции Столбец выбираем Наименование, в Сортировка выбираем Цвет ячейки, в Порядок — Нет цвета и нажимаем на зеленый прямоугольник.
Шаг 3: Далее Копировать уровень и выбираем желтый, затем красный. Кликаем на ОК, готово.
Способы использовать Microsoft Office бесплатно (часть 1):
Простые хитрости, позволяющие не платить за Word, Excel, PowerPoint и другие офисные программы.
1. Используйте MS Office Online
В браузере на любом ПК использовать привычные сервисы Microsoft можно без каких-либо подписок и оплаты. Для этого существует полноценный комплект онлайн-версий Word, Excel, PowerPoint и других программ.
Они полностью поддерживают привычное форматирование документов и включают в себя основные инструменты, правда, за некоторыми исключениями. К примеру, Word Online лишён панели WordArt, уравнений и диаграмм, а Excel Online не может работать с пользовательскими макросами.
И тем не менее, веб-версии вполне подойдут для открытия, просмотра и редактирования файлов Microsoft Office.
2. Установите мобильные приложения MS Office (Android, iOS)
Помимо онлайн-сервисов совершено бесплатно распространяются мобильные версии программ MS Office. На смартфонах с большими дисплеями с их помощью вполне удобно просматривать и редактировать документы, а в случае с планшетами — даже набирать тексты и работать с объёмными таблицами.
Бесплатное распространение актуально для всех устройств на базе Android и iOS, за исключением iPad Pro.
Простые хитрости, позволяющие не платить за Word, Excel, PowerPoint и другие офисные программы.
1. Используйте MS Office Online
В браузере на любом ПК использовать привычные сервисы Microsoft можно без каких-либо подписок и оплаты. Для этого существует полноценный комплект онлайн-версий Word, Excel, PowerPoint и других программ.
Они полностью поддерживают привычное форматирование документов и включают в себя основные инструменты, правда, за некоторыми исключениями. К примеру, Word Online лишён панели WordArt, уравнений и диаграмм, а Excel Online не может работать с пользовательскими макросами.
И тем не менее, веб-версии вполне подойдут для открытия, просмотра и редактирования файлов Microsoft Office.
2. Установите мобильные приложения MS Office (Android, iOS)
Помимо онлайн-сервисов совершено бесплатно распространяются мобильные версии программ MS Office. На смартфонах с большими дисплеями с их помощью вполне удобно просматривать и редактировать документы, а в случае с планшетами — даже набирать тексты и работать с объёмными таблицами.
Бесплатное распространение актуально для всех устройств на базе Android и iOS, за исключением iPad Pro.
Способы использовать Microsoft Office бесплатно (часть 2):
3. Воспользуйтесь Office 365
Облачный Office 365 для дома, включающий в себя премиум-версии приложений Office для шести человек, распространяется по подписке. Год использования обходится в 4 399 рублей, однако перед покупкой можно опробовать сервис бесплатно в течение целого месяца.
В рамках тестового периода будут предоставлены не только самые свежие версии всех программ, но также 1 ТБ места в облачном хранилище OneDrive и 60 бесплатных минут для звонков в Skype. Единственным условием является необходимость указать реквизиты карты, с которой по окончанию месяца будет списываться оплата.
4. Попробуйте MS Office 365 ProPlus
По истечению пробного периода с Office 365 можно запросить 30 дней бесплатного использования профессионального Office 365 ProPlus. Он позволяет создавать до 25 учетных записей пользователей и может быть интегрирован с локальными решениями для электронной почты и совместной работы. От вас потребуется заполнить специальную форму и дождаться ответа.
3. Воспользуйтесь Office 365
Облачный Office 365 для дома, включающий в себя премиум-версии приложений Office для шести человек, распространяется по подписке. Год использования обходится в 4 399 рублей, однако перед покупкой можно опробовать сервис бесплатно в течение целого месяца.
В рамках тестового периода будут предоставлены не только самые свежие версии всех программ, но также 1 ТБ места в облачном хранилище OneDrive и 60 бесплатных минут для звонков в Skype. Единственным условием является необходимость указать реквизиты карты, с которой по окончанию месяца будет списываться оплата.
4. Попробуйте MS Office 365 ProPlus
По истечению пробного периода с Office 365 можно запросить 30 дней бесплатного использования профессионального Office 365 ProPlus. Он позволяет создавать до 25 учетных записей пользователей и может быть интегрирован с локальными решениями для электронной почты и совместной работы. От вас потребуется заполнить специальную форму и дождаться ответа.
Способы использовать Microsoft Office бесплатно (часть 3):
5. Получите MS Office при покупке ПК
Получить официальные программы Office для дома или же подписку на год использования Office 365 вполне можно при покупке ноутбука или планшета на Windows, такие акции часто встречаются.
Соответственно, если вы как раз собирались приобрести новый ноутбук, стоит заранее ознакомиться с доступными предложениями и узнать, где Office уже включён в комплект.
6. Используйте Office 365 для учащихся и преподавателей
Для учащихся и преподавателей предоставляется специальный пакет Office 365, разработанный для образовательных учреждений. Использовать его можно совершенно бесплатно, если учебное заведение зарегистрировано на сайте Microsoft.
В рамках бесплатного тарифа основные программы доступны в веб-версии. Привычный десктопный Word, Excel и PowerPoint можно получить только платно. Но вполне возможно, что ваше учебное заведение предоставляет специальные условия приобретения Office 365 по сниженной стоимости.
5. Получите MS Office при покупке ПК
Получить официальные программы Office для дома или же подписку на год использования Office 365 вполне можно при покупке ноутбука или планшета на Windows, такие акции часто встречаются.
Соответственно, если вы как раз собирались приобрести новый ноутбук, стоит заранее ознакомиться с доступными предложениями и узнать, где Office уже включён в комплект.
6. Используйте Office 365 для учащихся и преподавателей
Для учащихся и преподавателей предоставляется специальный пакет Office 365, разработанный для образовательных учреждений. Использовать его можно совершенно бесплатно, если учебное заведение зарегистрировано на сайте Microsoft.
В рамках бесплатного тарифа основные программы доступны в веб-версии. Привычный десктопный Word, Excel и PowerPoint можно получить только платно. Но вполне возможно, что ваше учебное заведение предоставляет специальные условия приобретения Office 365 по сниженной стоимости.
Работа функции СУММПРОИЗВ
Первый массив обязательный. Значения должны быть числовыми, так как текстовые значения формула посчитает как 0. Массивы 2, 3 и так далее являются дополнительными необязательными аргументами и используются в зависимости от постановки задачи.
Данная функция по ячейкам или по элементам перемножает несколько массивов и суммирует получившиеся произведения. Достаточно популярный пример использования данной функции — получить общую стоимость, перемножив цену и количество товаров или услуг.
В примере ниже, в столбце "В" у нас имеются цены, а в столбце "С" – количество товаров. И, следовательно, используя функцию, узнаем стоимость реализации товаров.
Первый массив обязательный. Значения должны быть числовыми, так как текстовые значения формула посчитает как 0. Массивы 2, 3 и так далее являются дополнительными необязательными аргументами и используются в зависимости от постановки задачи.
Данная функция по ячейкам или по элементам перемножает несколько массивов и суммирует получившиеся произведения. Достаточно популярный пример использования данной функции — получить общую стоимость, перемножив цену и количество товаров или услуг.
В примере ниже, в столбце "В" у нас имеются цены, а в столбце "С" – количество товаров. И, следовательно, используя функцию, узнаем стоимость реализации товаров.
Проверка читаемости документа
Вы можете узнать, есть ли в вашем документе незначительные ошибки, которые портят его читаемость. Для этого перейдите на вкладку Рецензирование и выберите пункт Проверить читаемость.
В появившемся меню справа вы сможете посмотреть все ошибки, которые для лучшей читаемости желательно заменить. На примере на картинке это Объединённые ячейки и Имена листов по умолчанию.
Нажав на стрелку рядом с каждым пунктом, вы увидите рекомендуемые действия, чтобы исправить эти ошибки.
Вы можете узнать, есть ли в вашем документе незначительные ошибки, которые портят его читаемость. Для этого перейдите на вкладку Рецензирование и выберите пункт Проверить читаемость.
В появившемся меню справа вы сможете посмотреть все ошибки, которые для лучшей читаемости желательно заменить. На примере на картинке это Объединённые ячейки и Имена листов по умолчанию.
Нажав на стрелку рядом с каждым пунктом, вы увидите рекомендуемые действия, чтобы исправить эти ошибки.
Внесение данных на несколько листов одновременно
В Excel можно вводить данные на несколько листов сразу.
Для этого сперва выделяем листы, с которыми будем работать — нажимаем
Далее вводим данные на первом листе. Все, что вносим в таблицу, будет копироваться и на остальные выделенные листы.
После того как все необходимые данные внесены, листы надо разгруппировать.
Для этого нажимаем правой кнопкой мыши по названию листа и в раскрывающемся списке выбираем Разгруппировать листы.
Такой прием действует для ввода и данных, и формул, и форматирования.
В Excel можно вводить данные на несколько листов сразу.
Для этого сперва выделяем листы, с которыми будем работать — нажимаем
CTRL и мышкой выделяем нужные листы. Таким образом, они будут сгруппированы.Далее вводим данные на первом листе. Все, что вносим в таблицу, будет копироваться и на остальные выделенные листы.
После того как все необходимые данные внесены, листы надо разгруппировать.
Для этого нажимаем правой кнопкой мыши по названию листа и в раскрывающемся списке выбираем Разгруппировать листы.
Такой прием действует для ввода и данных, и формул, и форматирования.
Как разделить текст по столбцам?
Предположим, у вас есть несколько ячеек с именем и отчеством, но вы хотите сделать так, чтобы имя и фамилия отображались в разных столбцах.
В таком случае выделите нужные ячейки и перейдите на вкладку Данные → Текст по столбцам.
Нажмите кнопку Далее, затем выберите символ-разделитель (пробел, дефис, запятая и т.д.) и в последнем пункте нажмите Готово.
В результате имена останутся в первой ячейке, а отчества — перейдут в соседнюю.
Предположим, у вас есть несколько ячеек с именем и отчеством, но вы хотите сделать так, чтобы имя и фамилия отображались в разных столбцах.
В таком случае выделите нужные ячейки и перейдите на вкладку Данные → Текст по столбцам.
Нажмите кнопку Далее, затем выберите символ-разделитель (пробел, дефис, запятая и т.д.) и в последнем пункте нажмите Готово.
В результате имена останутся в первой ячейке, а отчества — перейдут в соседнюю.
Использование камеры вместо копирования
В Excel есть опция Камера, которая позволяет скопировать и «сфотографировать» ячейки, но вставить их уже в другом месте или листе в виде изображения.
Это изображение полностью динамическое: если изменить данные исходной ячейки, данные во вставленном изображении тоже будут изменены.
Этой функции нет в стандартном интерфейсе Excel, но её можно добавить, нажав на стрелочку рядом с панелью быстрого доступа.
Далее выберите Другие команды → Все команды, найдите в списке пункт Камера и нажмите Добавить, чтобы вынести её на панель быстрого доступа.
В Excel есть опция Камера, которая позволяет скопировать и «сфотографировать» ячейки, но вставить их уже в другом месте или листе в виде изображения.
Это изображение полностью динамическое: если изменить данные исходной ячейки, данные во вставленном изображении тоже будут изменены.
Этой функции нет в стандартном интерфейсе Excel, но её можно добавить, нажав на стрелочку рядом с панелью быстрого доступа.
Далее выберите Другие команды → Все команды, найдите в списке пункт Камера и нажмите Добавить, чтобы вынести её на панель быстрого доступа.
Простой кредитный калькулятор
В новых версиях Excel встроены готовые шаблоны, в том числе и Кредитный калькулятор.
Чтобы воспользоваться таким шаблоном, перейдите на вкладку Файл → Создать и в разделе Поиск шаблонов в сети найдите документ с названием Кредитный калькулятор.
После этого в открывшемся документе необходимо указать сумму кредита, процентную ставку и период кредитования. Шаблон автоматически рассчитает общую сумму процентов, количество платежей, а также сумму ежемесячных взносов.
В новых версиях Excel встроены готовые шаблоны, в том числе и Кредитный калькулятор.
Чтобы воспользоваться таким шаблоном, перейдите на вкладку Файл → Создать и в разделе Поиск шаблонов в сети найдите документ с названием Кредитный калькулятор.
После этого в открывшемся документе необходимо указать сумму кредита, процентную ставку и период кредитования. Шаблон автоматически рассчитает общую сумму процентов, количество платежей, а также сумму ежемесячных взносов.
Преобразование числового формата в текстовый
Excel округляет все числа, которые следуют за 15 цифрами до нуля, поэтому при работе например с кредитными картами или другими кодами нужно преобразовать числа в текстовый вариант.
Для этого:
— Шаг 1
Выберите ячейку или диапазон ячеек, который требуется преобразовать в текстовый формат.
— Шаг 2
Нажмите комбинацию клавиш
Excel округляет все числа, которые следуют за 15 цифрами до нуля, поэтому при работе например с кредитными картами или другими кодами нужно преобразовать числа в текстовый вариант.
Для этого:
— Шаг 1
Выберите ячейку или диапазон ячеек, который требуется преобразовать в текстовый формат.
— Шаг 2
Нажмите комбинацию клавиш
CTRL + 1, и в появившемся окне в разделе Числовые форматы выберите Текстовый формат.
Изменение направления перехода при нажатии ENTER
При заполнении ячеек в таблице и последующем нажатии клавиши
Это не всегда бывает удобно, особенно когда требуется заполнить большой массив данных по горизонтали.
В этом случае можно зайти в меню параметры, нажав кнопку Office и выбрав Параметры Excel.
Далее в открывшемся окне на вкладе Дополнительно можно будет задать направление перехода при нажатии
При заполнении ячеек в таблице и последующем нажатии клавиши
ENTER по умолчанию выделяется нижестоящая ячейка. Это не всегда бывает удобно, особенно когда требуется заполнить большой массив данных по горизонтали.
В этом случае можно зайти в меню параметры, нажав кнопку Office и выбрав Параметры Excel.
Далее в открывшемся окне на вкладе Дополнительно можно будет задать направление перехода при нажатии
ENTER: вниз, вправо, вверх, влево.
Функция ИНДЕКС
ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.
Функция имеет следующие аргументы:
• Массив (диапазон ячеек или константа массива).
• Номер строки (выбирает строку в массиве, из которой требуется возвратить значение).
• Номер столбца (выбирает столбец в массиве, из которого требуется возвратить значение).
ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.
Функция имеет следующие аргументы:
• Массив (диапазон ячеек или константа массива).
• Номер строки (выбирает строку в массиве, из которой требуется возвратить значение).
• Номер столбца (выбирает столбец в массиве, из которого требуется возвратить значение).
Как закрепить шапку таблицы?
Предположим, у вашей таблицы есть заголовки, и вы хотите чтобы они отображались даже при прокручивании листа вниз.
Для этого нужно полностью выделить ту строку, которая находится под шапкой. Так, например, если шапка находится в третей строке — выделите четвертую.
После этого перейдите на вкладку Вид → Закрепить области. Также, если шапка находится в первой строке, вы можете не выделять строки и на этом этапе просто выбрать пункт Закрепить верхнюю строку.
Предположим, у вашей таблицы есть заголовки, и вы хотите чтобы они отображались даже при прокручивании листа вниз.
Для этого нужно полностью выделить ту строку, которая находится под шапкой. Так, например, если шапка находится в третей строке — выделите четвертую.
После этого перейдите на вкладку Вид → Закрепить области. Также, если шапка находится в первой строке, вы можете не выделять строки и на этом этапе просто выбрать пункт Закрепить верхнюю строку.
Использование последнего значения
Бывают ситуации, когда нужно видеть только последнее значение в том или ином столбце.
Предположим, это подсчёт пробега автомобиля. В поле с итогами вам нужно не суммировать данные, а просто посмотреть последние показатели пробега.
В таком случае воспользуйтесь функциями
A6:D6 в этой формуле обозначает ссылку на один и тот же диапазон ячеек, из которого требуется вынести последнее значение.
Бывают ситуации, когда нужно видеть только последнее значение в том или ином столбце.
Предположим, это подсчёт пробега автомобиля. В поле с итогами вам нужно не суммировать данные, а просто посмотреть последние показатели пробега.
В таком случае воспользуйтесь функциями
=ИНДЕКС() и =СЧЁТ(), вложенными друг в друга. У вас должна получиться формула =ИНДЕКС(A6:D6;СЧЁТ(A6:D6)). A6:D6 в этой формуле обозначает ссылку на один и тот же диапазон ячеек, из которого требуется вынести последнее значение.
Как выделить цветом ячейки с просроченными датами?
На картинке, в примере ниже, введены даты, и выделены те, которым более чем 90 дней от текущей даты.
Как же это сделать:
• Выделяем диапазон ячеек (в нашем случае это А3:А8), затем Главная → Стили → Условное форматирование → Управление правилами → Создать правило.
• В появившемся окне выбираем Использовать формулу для определения форматируемых ячеек.
• В поле для формулы вводим
В итоге, все даты, актуальность которых превышает 90 дней, были выделены красным.
На картинке, в примере ниже, введены даты, и выделены те, которым более чем 90 дней от текущей даты.
Как же это сделать:
• Выделяем диапазон ячеек (в нашем случае это А3:А8), затем Главная → Стили → Условное форматирование → Управление правилами → Создать правило.
• В появившемся окне выбираем Использовать формулу для определения форматируемых ячеек.
• В поле для формулы вводим
=$B$1-A3>90
• Выбираем цвет, границы, шрифт оформления формата и нажимаем на ОК.В итоге, все даты, актуальность которых превышает 90 дней, были выделены красным.
Как превратить данные в подробную диаграмму?
В Excel есть встроенная надстройка Социальный граф, которая позволяет в пару кликов создавать диаграммы на основе выбранных данных.
Чтобы её использовать, перейдите на вкладку Вставка и нажмите кнопку, обозначенную на рисунке ниже.
В появившейся диаграмме можно редактировать тип, тему и фигуры диаграммы, а также присваивать ей данные из рабочего листа в виде ссылок на ячейку. Данные будут представлены в виде определённого количества выбранных фигур, каждая из которых соответствует значению 10.
В Excel есть встроенная надстройка Социальный граф, которая позволяет в пару кликов создавать диаграммы на основе выбранных данных.
Чтобы её использовать, перейдите на вкладку Вставка и нажмите кнопку, обозначенную на рисунке ниже.
В появившейся диаграмме можно редактировать тип, тему и фигуры диаграммы, а также присваивать ей данные из рабочего листа в виде ссылок на ячейку. Данные будут представлены в виде определённого количества выбранных фигур, каждая из которых соответствует значению 10.
