@excelmania

Минимальное значение без учета нулей

2018-06-19T05:41:13.428Z

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

Разберемся, как получить минимальное значение без учета нулей

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

Формула будет выглядеть следующим образом

Как видно - это формула массива. Применить ее не сложно: вводим формулу =МИН(ЕСЛИ(A1:A14<>0;A1:A14;"")), нажимаем на строку формул и жмем комбинацию клавиш Ctrl+Shift+Enter. Формула должна попасть в фигурные скобки как на рисунке выше

Результат не заставит себя долго ждать

19.06.2018 @ExcelMania

Превратить текст в дату

2018-06-18T06:02:56.090Z

Если у вас есть даты, которые сохранены как текст, да еще и в разном формате, то эта статья для вас!

Excel предусматривает специальную функцию, которая превращает текст в дату

Функция называется ДАТАЗНАЧ

Функция автоматически распознает текст и превращает его в дату не зависимо от формата текста

Единственное - нужно, чтобы формат целевой ячейки, в которую вы прописываете формулу был установлен как ДАТА

Наслаждайтесь Excel!

18.06.2018 @ExcelMania

Определяем квартал из даты

2018-06-15T06:09:04.764Z

Сегодня научимся определять, какой дате какой квартал соответствует. Понятно, что 2-3 даты можно определить вручную, а если массив состоит из тысячи дат?

Рассмотрим на примере небольшой выборки из дат и определим, в каком квартале эти даты находятся

Исходный пример

2. В ячейке B2 прописываем формулу =ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)&" квартал "&ГОД(A2)

3. Протягиваем формулу вниз и наслаждаемся результатом

Технически формула работает так

Формула ЦЕЛОЕ возвращает целое число от целевого параметра. В данном случае у нас это месяц, содержащийся внутри формулы ЦЕЛОЕ
Прибавляем к полученному номеру месяца 2 и делим на 3 (арифметика - без нее никуда!)
Далее идет сцепка данных для визуального отображения результата. Это непосредственно слово "квартал" и сам год текущего периода

15.06.2018 @ExcelMania

Телефонные номера в единый формат

2018-06-14T06:12:17.331Z

Сегодня о красоте. И о собственных пользовательских форматах Excel.

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

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

Рассмотрим на примере телефонных номеров

Это номера из выписки по телефонному счёту. Наша задача привести их к виду +7 (***) ***-**-**. То есть к формату, более удобному для чтения и понимания

В ячейке B1 нажимаем правой кнопкой мыши и выбираем Формат ячеек (или нажимаем комбинацию клавиш Ctrl+1). Мы будем пользоваться комбинацией клавиш, что и вам советуем

2. Выбираем пункт "все форматы"

3. Дальше все просто. В поле "Тип" указываем +7" "(#)" "000-00-00 и нажимаем ОК

4. Далее проставляем равенство для соседней ячейки и видим результат

Таким образом, можно настроить любой формат для своих данных

Позже мы опишем универсальный механизм

14.06.2018 @ExcelMania

Автоматическое суммирование ячеек выше текущей

2018-06-13T06:35:32.094Z

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

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

Исходные данные - набор цифровых значений, которые необходимо просуммировать

В целевой ячейке (в нашем случае это B9) нажимаем комбинацию клавиш Alt+=, после чего Excel сам проставит формулу СУММ и автоматически укажет диапазон

Далее нажимаем Enter и наслаждаемся результатом

13.06.2018 @ExcelMania

Клавиша F9

2018-06-07T06:03:36.731Z

Продолжаем серию статей о значении клавиш F в Excel

Клавиша F9 позволяет вычислить промежуточный результат многосоставной или односоставной формулы

Исходные данные

Становимся в строку формул и нажимаем F9

Для возврата формулы обратно нажимаем Esc

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

07.06.2018 @ExcelMania

Супер-быстрый выпадающий список Excel

2018-06-05T08:47:58.802Z

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

Единственный недостаток - в списке будут только те элементы, которые уже есть

Как всегда, рассмотрим на примере исходных данных

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

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

Это оперативное решение поможет сэкономить время при ведении таблиц с ограниченными или повторяющимися данными и не прибегать к настройке выпадающих списков

05.06.2018 @ExcelMania

Сумма ячеек по условию

2018-06-04T11:50:53.298Z

Быстро и просто суммируем столбец с ячейками по определенному условию. Скажем, чтобы суммировались все числа больше 500 000

В данной статье просто и понятно об еще одной возможности формулы СУММЕСЛИ

Исходные данные

В целевой ячейке прописываем формулу =СУММЕСЛИ(B4:B33;"<500000") и получаем результат

Сегодня очень о простом, но почему-то определенный % наших учеников не смог разобраться в вариативности формулы СУММЕСЛИ после того как мы опубликовали статью о ней

04.06.2018 @ExcelMania

Удаление пробелов между цифрами

2018-06-01T06:48:10.012Z

Буквально вчера наш клиент столкнулся с трудностью, решить которую самостоятельно не смог. Разумеется, наша команда пришла на помощь в решении этой задачи

Так получилось, что на его участке нерадивый оператор каким-то неистовым способом заполнил реестр так, что все цифры оказались разделены пробелами и не поддавались дальнейшим вычислениям. Цифр в массиве было больше 20 000. Кусок массива выглядит так

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

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

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

=ПОДСТАВИТЬ(B6;" ";"")

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

Почему именно такой вариант решения, а не, скажем, обычная замена Ctrl+H? Как выяснилось, применительно к этой задаче, это может не являться решением (в файле с примером от нашего клиента данный способ не сработал). Этот же работает всегда 100%

01.06.2018 @ExcelMania

Замена первой буквы в строке на заглавную

2018-05-31T06:25:12.759Z

Порой у наших юных и не совсем юных экселистов возникают трудности в выполнении простых на первый взгляд задач

Сегодня на повестке дня задача, как в строчках Excel сделать первые буквы заглавными

Рассмотрим небольшой кусок таблицы, где недобросовестный оператор предоставил данные не структурно (где-то написано правильно, где-то нет)

Наша задача причесать данные и сделать все фамилии с большой буквы

Для этого прописываем формулу в соседней ячейке

=СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A3));ПСТР(A3;2;ДЛСТР(A3)-1))

и протягиваем вниз

Формулу прочитать можно так

СЦЕПИТЬ() — функция соединяющая две части текста в ячейке

ПРОПИСН() — функция преобразующая текст в заглавные буквы

ЛЕВСИМВ() — функция возвращающая указанное количество символов слева

ПСТР()— выводит текст начиная от указанного по счету символа

ДЛСТР() — возвращает количество символов в ячейке

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

31.05.2018 @ExcelMania