Помощь по формулам и таблицам Excel

Привет, Капибояре, есть кто разбирается в таблицах?

Желательно одновременно в гугл щит и мс эксель например

Дело такое, веду отчетность в гугловских таблицах, на одном листе у меня база данных:

На другом поисковик:

Выдача поиска идет через формулу =FILTER('Данные'!A:K;'Данные'!B:B=B1)

Суть вопроса:

При экспорте в xlsx, поиск перестает работать.

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

Локально работаю в onlyoffice, но в онлайн экселе всё тоже самое. Ни гугл, ни яндекс, ни искуственный интелект ответа мне не дал, ну или я не так спрашивал( 

Что я делаю не так?

Капибара, привет!

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

Для примера возьмём данную таблицу:

Предположим, вам нужно сумму ячеек в столбце «Стоимость» вынести в другую программу. При выделении диапазона сумма отображается в нижнем правом углу Excel…

…которую нельзя скопировать.

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

1.   Создаём сам макрос и сохраняем в формате .xlsm

Открываем новый файл Excel, зажимаем Alt+F11, верхняя панель – Insert, выбираем Module. Вставляем код, закрываем окно макросов.

Файл сохраняем как «Книга с поддержкой макросов».

Теперь нужно назначить горячую клавишу. Зажимаем Alt+F8, имя макроса – Summation, в «Параметры» установите желаемую связку.

Внимание! Указанная буква будет работать только в своей раскладке клавиатуры (поэтому можно поставить цифру). У меня это «й», поставил для удобства, и ни на какие другие комбинации не ссылается.

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

Готово! Выделяете диапазон, далее Ctrl+й, потом вставляете в любое место из буфера.

НО! Макрос работает только пока открыта книга со вставленным кодом. Далее будет вариант занесения в глобальные шаблоны Excel.

2.   Сохраняем тот же файл, но уже как надстройку в формате .xlam

Затем открываем любую книгу Excel под рукой, наверху Файл, внизу Параметры, Надстройки, Перейти.

Открывается окно, по Обзору ищем только что сохранённую надстройку. Готово! Напротив новой строчки ставим галку – и в путь, можно приступать к работе. Файл надстройки удалять/переносить нельзя: Excel ссылается на его путь.

Работоспособность проверена на всех современных версиях.

Ребят, хочу поделиться с вами таким решением для Excel.

Это график, который отражает данные при их выборе, а также пересчитывает категорию Прочее.

Очень полезно, как я полагаю.

Ссыль

Всем бобра ))

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

1. Введение в интерфейс Excel1.1 Основные элементы интерфейса

• Лента: Верхняя часть окна, где находятся вкладки с инструментами («Главная», «Вставка», «Формулы» и т. д.).
• Ячейки: Основные элементы таблицы, расположенные в строках и столбцах (например, A1, B2).
• Строки и столбцы: Горизонтальные строки и вертикальные столбцы обозначаются номерами и буквами соответственно.
• Рабочие листы: Вкладки в нижней части окна, где хранятся разные таблицы одного файла.
• Строка формул: Область для ввода или редактирования формул и данных в выбранной ячейке.

1.2 Настройка интерфейса

• Включите или отключите ленту, используя сочетание клавиш Ctrl + F1.
• Настройте быстрый доступ к часто используемым командам через «Панель быстрого доступа».

2. Работа с данными2.1 Ввод и редактирование данных

• Введите текст, числа или формулы, выделив нужную ячейку.
• Для редактирования нажмите F2 или дважды щёлкните по ячейке.

2.2 Форматирование данных

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

2.3 Упорядочивание и фильтрация

• Выделите данные и используйте команду «Сортировка» для упорядочивания по возрастанию или убыванию.
• Добавьте фильтры через команду «Фильтр» (вкладка «Данные»).

3. Формулы и функции3.1 Основы работы с формулами

• Все формулы начинаются с символа = (например, =A1+B1).
• Используйте ссылки на ячейки вместо ввода значений вручную.

3.2 Основные функции

• СУММ(A1:A10): Суммирует значения в диапазоне ячеек.
• СРЗНАЧ(A1:A10): Вычисляет среднее значение.
• МАКС(A1:A10) и МИН(A1:A10): Находит наибольшее и наименьшее значение.
• ЕСЛИ(условие; значение_если_истина; значение_если_ложь): Логическая функция.

3.3 Использование абсолютных и относительных ссылок

• Относительные ссылки (A1): Меняются при копировании формулы.
• Абсолютные ссылки ($A$1): Остаются неизменными при копировании.
• Сочетание клавиш F4 переключает тип ссылки.

4. Визуализация данных4.1 Создание диаграмм

• Выделите данные и перейдите на вкладку «Вставка».
• Выберите тип диаграммы («Гистограмма», «Круговая», «Линейная» и т. д.).
• Настройте внешний вид диаграммы с помощью вкладок «Конструктор» и «Формат».

4.2 Условное форматирование

• Найдите команду «Условное форматирование» на вкладке «Главная».
• Задайте правила для выделения ячеек («Больше», «Меньше», «Между» и т. д.).

5. Продвинутые функции Excel5.1 Работа с массивами

• Используйте функции массивов, такие как ИТОГИ и СУММПРОИЗВ.

5.2 Сводные таблицы

• Создайте сводную таблицу через вкладку «Вставка».
• Перетащите поля в области «Строки», «Столбцы» и «Значения» для настройки отчёта.

5.3 Макросы

• Включите вкладку «Разработчик» через настройки ленты.
• Запишите или напишите макрос на языке VBA для автоматизации задач.

6. Советы и приёмы6.1 Горячие клавиши

• Ctrl + Z: Отменить действие.
• Ctrl + Y: Повторить действие.
• Ctrl + S: Сохранить файл.
• Ctrl + Shift + L: Добавить или удалить фильтр.

6.2 Защита данных

• Заблокируйте ячейки через команду «Защитить лист» (вкладка «Рецензирование»).
• Установите пароль для защиты файла.

6.3 Совместная работа

• Используйте облачные хранилища, такие как OneDrive, для совместного редактирования.
• Включите отслеживание изменений через вкладку «Рецензирование».

7. Решение типичных проблем7.1 Ошибки в формулах

• #ДЕЛ/0!: Деление на ноль. Проверьте делитель.
• #ИМЯ?: Неверное имя функции. Проверьте орфографию.
• #ЗНАЧ!: Неправильный тип данных. Убедитесь, что данные совместимы с функцией.

7.2 Медленная работа файла

• Удалите ненужные формулы или пересчёт формул вручную через Формулы > Параметры вычислений.
• Сократите количество форматирований и данных.

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

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

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

Когда Lotus 1-2-3 впервые вышла, программа предполагала, что 1900 год был високосным, хотя на самом деле он таковым не был. Это упрощало обработку високосных годов в программе и не причиняло вреда практически никаким вычислениям дат в Lotus 1-2-3.

Lotus 1-2-3 была, по сути, предшественницей Excel – невероятно популярная программа для работы с электронными таблицами от компании Lotus Software, которая помогла персональным компьютерам IBM доминировать на рынке деловых решений в 1980-х годах. До сих пор нет единого мнения, был ли этот баг сознательным решением программистов Lotus для экономии ценной оперативной памяти за счёт упрощения вычисления дат, или же это была обычная ошибка.

Однако Lotus 1-2-3 была настолько популярна, что Microsoft при создании собственного аналога была вынуждена расставить приоритеты в пользу совместимости. Ни один бизнес не перешёл бы на Excel, если бы все существующие таблицы оказались несовместимы с новой программой. Когда вышли Microsoft Multiplan, а затем и Microsoft Excel, обе программы приняли то же допущение, используя аналогичную систему последовательной нумерации дат, что позволяло пользователям легко импортировать рабочие листы из одного приложения в другое. Стоит отметить, что все остальные високосные годы обрабатываются корректно – проблема касается исключительно 1900 года.

Офисные продукты Microsoft обогнали решения Lotus в начале 1990-х и с тех пор доминируют на рынке, хотя продукты Lotus окончательно перестали продаваться только в 2013 году. Но "наследственный" баг с високосным годом остался. Microsoft признаёт, что "технически исправить это поведение возможно, однако недостатки такого решения перевешивают преимущества". Компания перечисляет проблемы, которые возникнут в случае исправления:

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

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

Как отмечает сама Microsoft, сохранение текущего поведения приводит лишь к одной проблеме:

Функция WEEKDAY возвращает некорректные значения для дат до 1 марта 1900 года. Но так как большинство пользователей не работают с датами до 1 марта 1900 года, эта проблема встречается крайне редко.

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

Линк

Устал от чтения новостей, давайте поговорим о чём-то хорошем. Вводные в моём случае при изучении английского:

- нулевой словарный запас;

- плохой почерк - свои записи впоследствии разбираю, но с трудом.

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

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

https://www.oxfordlearnersdictionaries.com/

Для проверки запоминания очень хорошо подходит функция СЛУЧМЕЖДУ(). Для её использования (на примере скрина выше):

- в ячейку H1 - "1", H2 - "=H1+10", продолжаем вниз;

- в ячейку I1 - "=СЛУЧМЕЖДУ(H1;H2-1)", продолжаем вниз;

- в ячейку J1 - "=ВПР(I1;$A$1:$D$1000;2;ЛОЖЬ), продолжаем вниз (выведет русское слово, вспомнить перевод и произношение).

В этом случае набор слов разбивается на десятки, в столбце J выводится случайное слово из столбца В в диапазоне от 1 до 10, от 11 до 20 и т.п. Границы диапазонов, в которых выбирается случайное слово, естественно, каждый подбирает под себя.

Обновить страницу для получения новых комбинаций - по-умолчанию F9.

Может быть, кому-то пригодится; мне это показалось гораздо удобнее, чем исписывать каракулями десятки листов. Пример выложил на Яндекс Диск (надеюсь, это не запрещено): https://disk.yandex.ru/i/UHAdHOpGCoQ87w

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

P.S. Ещё полезная ссылка: сайт, где можно "без регистрации и смс" скачать книги на английском, адаптированные под разные уровни языка: https://english-e-reader.net/ (я был крайне удивлён отсутствием рекламы и объёмом контента).

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

За прошедшую неделю Уважаемые Пикабушники помогли очень многим.Они сделали намного больше, чем оф организации(.Есть на чем спать и чем укрыться. Есть посуда и постельное бельё. Есть даже новенький телевизор. И за всё это огромное спасибо вам, уважаемые Пикабушники. Продолжение поста «Помощь». Всё ещё очень нужна помощь с одеждой и особенно с обувью. Очень нужны мужская одежда большого размера 56-60 на рост 170-175. Вся. Начиная с белья. И нужна обувь закрытая, оба имеют по паре тряпичных тапочек(. Женская 37-38; мужская 44. На широкую ногу. Вдруг у кого то найдётся.
Королёв М.О
тел/ватсап:+7 995 897 46 39
@AlexX9054 телеграмм
К сожалению, посты не выходят в горячее. И люди, кому адресована благодарность их не видят.
Так же хочу напомнить, что в результате изменения политики Пикабу в части выхода постов в "Горячее" в общую ленту перестали попадать посты интересных авторов. Пожалуйста - читайте и подписывайтесь на @AlexRadio, , , @SallyKS, @GoodCurrentNews, , @Olivkovaya.Nimfa ,@myaus09, ,- у них замечательный авторский контент. К сожалению, , заблокировали, но на его страничке есть его координаты в других соцсетях.
Пожалуйста, помогите поднять пост. Пусть его увидят те, кто сможет помочь. Заранее, большое спасибо.

Добрый день Пикабу, добрый день все любители животных, и конечно же, уважаемые подписчики.
Сегодня я очень хочу поблагодарить @AlexRadio, который обратил на нас внимание своих читателей. Я безмерно благодарна всем, кто откликнулся. Благодаря вашей помощи и поддержке молоденькому коту Лису, в понедельник будет проведена довольно дорогая операция по удалению опухоли, на которую я не могла собрать средства почти три месяца. Спасибо вам огромное! Так же теперь есть возможность обследовать еще одного котенка у ортопеда. Я рассказывала ранее в посте, про котенка Шнурка, которого выкинули с третьего этажа. И всё это благодаря вам! Вашей отзывчивости и неравнодушию.
Конечно, в доме где живут более 20 котеек потребностей очень много, и без вашей поддержки мне не справиться. Я очень признательна всем, каждому из вас!
Я продолжаю искать дама своим подобрашкам. Надеюсь, с вашей помощью это будет быстрее.
А вот и котофеи, которые благодаря вам получат необходимое лечение.
Лис

Шнурок

Москва и М. О
тел/ватсап:+7 995 897 46 39
@AlexX9054 телеграмм
На аккаунте подключен донат, для тех, кто посчитает возможным нас поддержать.
Огромное спасибо всем кто помогает и поддерживает нас.

Добрый день, Пикабу. Все наши читатели, и конечно подписчики.
Похоже, лето у нас не задалось( впрочем, как и прошлое.
В последнее время нас просто преследуют неприятности. Сначало заболел Алекс(врожденное изменение печени. Лечится) Следом, отличился Бесик. Повредил бок. Гной, промывания, обработка. Все пришлось делать под седацией(. Сейчас все хорошо заживает.

На этот раз, Санчес. Всё началось с того, что в субботу поднялась температура, 40,6. Я его всего осмотрела, ощупала. Залезла буквально везде(, но причины не обнаружила. А после укола но-шпы, температура спасла, и он стал опять активным, ласкучим и шибутным). В общем, самый обычный Саня.
А сегодня я заметила, что он пытается есть, а корм выпадает. Надо отметить, что со всеми последними тратами, я не купила корм для котят, и они едят размоченный взрослый корм(.
При осмотре, под подбородком, обнаружила опухоль. Плотный шарик, с небольшую обивку. Внутри ни чего не видно, небольшое покраснение под языком, не язвы. При этом он активен и пытается кушать.
Нам сново надо ехать в клинику. А ехать не на что. Мы ещё за последний визит с Босиком остались немного должны. Вот такая у нас сейчас ситуация(

На фото конечно, не очень хорошо видно, но на ощупь шишка большая и плотная.
В прошлом году, похожая ситуация у нас была с Лисом. Было осложнение после кальцивироза, опухоль слюной железы. Операция тогда нам обошлась в катастрофические деньги.
Мне очень нужна ваша помощь. Самой мне не справиться.
Москва и М. О
Тел/ватсап:+7 995 897 46 39
@AlexX9054 телеграмм

https://pay.cloudtips.ru/p/5b82d7de на аккаунте подключен донат для Банды. Мы так же будем безмерно рады за помощь кормом и наполнителем. Будем рады информационной помощи. Если кто то разместит о нас информацию в своих соц сетях, у котеек будет больше шансов найти своих людей.
ОГРОМНОЕ СПАСИБО ВСЕМ, КТО ПОМОГАЕТ И ПОДДЕРЖИВАЕТ. ЭТО ОЧЕНЬ ВАЖНО ДЛЯ НАС.

Пожалуйста, помогите поднять пост. С новыми правилами, это теперь очень не просто(

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

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

Комментарии для минусов прилагаются. 

Сегодня я обращаюсь к пикабушникам за помощью.

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

Шутка, конечно.

Теперь серьезно.

Почти 2 года я периодически выкладывал истории из сборников, которые были в процессе написания - "Байки из жизни", потом - "Новые байки" и, наконец - "Снова байки". Истории пользовались разным успехом, т.е. были удачные, были не особо. В случаях, когда истории были удачные, я вижу свою заслугу - смог интересно рассказать, когда были неудачные - несомненна вина того, кто поведал мне историю - не смог вложить душу в повествование.

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

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

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

Некоторые истории обросли продолжениями, другие - подробностями.

Вот я и подумал... написано 3 сборника. В каждом 60+ историй. Всего - чуть менее 200 баек. А если отобрать лучшие, дополнить, попутно исправив опечатки и сделав новые, и собрать их в отдельный сборник - "50 лучших баек"? Может, не 50, может 60. Или 70, хотя на счет этой цифры сомневаюсь.

Но как отобрать лучшие? Ежу понятно, что мое мнение будет предвзятым.

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

Так я подхожу к сути дела.

Ищу 10 добровольцев. Тех, кто любит читать. Я отправлю добровольцам все 3 сборника (в электронном виде), добровольцы прочтут их и оценят каждую байку по 5-балльной системе, где 1 - полный отстой, 5 - суперская байка. А я, исходя их анализа этих данных, скомпоную сборник самых рейтинговых баек.

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

Итак, добровольцы есть?

UPD:

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

Кто отозвался - всем спасибо, отправил все 3 сборника. Кто откликнулся позже - извиняюсь.

Кто не получил - делал рассылку сразу по нескольким адресам, посмотрите папку "СПАМ"