NOPASSWD doesn't have a major impact on security. Its most obvious effect is to provide protection when the user left his workstation unattended: an attacker with physical access to his workstation can then extract data, perform actions and plant malware with the user's permissions, but not elevate his access to root. This protection is of limited use because the attacker can plant a keylogger-type program that records the user's password the next time he enters it at a sudo prompt or in a screensaver.
Nonetheless, requiring the password does raise the bar for the attacker. In many cases, protection against unsophisticated attackers is useful, particularly in unattended-workstation scenarios where the attack is often one of opportunity and the attacker may not know how to find and configure discreet malware at short notice. Furthermore it is harder to hide malware when you don't have root permissions — you can't hide from root if you don't have root.
There are some realistic scenarios where the lack of a password does protect even against sophisticated attackers. For example, a stolen laptop: the user's laptop is stolen, complete with private SSH keys; either the thief manages to guess the password for the key file (perhaps by brute force), or he gains access to them from a memory dump of a key agent. If the theft is detected, this is a signal to investigate recent activity on that user's account, and this means that a planted malware should be detected. If the attacker only had user-level access, anything he did will leave traces in logs; if the attacker obtained the user's password and ran sudo, all logs are now compromised.
I don't know whether the downsides of
NOPASSWD balance the upsides for your use case. You need to balance that against all the other factors of your situation. For example, it seems that you allow but don't enforce having different passwords. Can you instead use a centralized account database? How much containment do you need between your systems? Are you considering alternatives to Ansible that would support differing sudo passwords? Have you considered other authentication mechanisms?